How do I open MySQL connector in Python?

Connecting to a MySQL database with Python can be confusing. There are several different connection drivers, and not all of them play nicely with different operating systems. On top of that, enabling SSL can be a challenge.

This article covers four methods you can use to connect your Python application to MySQL. These examples will use PlanetScale, a serverless database platform for MySQL, but the actual database you connect to is arbitrary.

The following connection methods will be covered:

  • sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
    0
  • sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
    1
  • sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
    2
  • sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
    3

If you need a free database to test your connection, PlanetScale is an excellent option. PlanetScale is built on top of Vitess and provides you a familiar branching workflow to test and deploy schema changes.

Vitess was built in 2010 to solve scaling issues at YouTube. Since then, the open source project has continued to grow and now helps several companies like Slack and Square handle their massive data scaling needs.

Sign up for an account to get a free 5GB database.

Create a database

Once you're in the PlanetScale dashboard:

  1. Click "Create new database" on your organization overview dashboard.
  2. Give your database a name.
  3. Choose a region from the dropdown.
  4. Click Create database.

Your database will be created with a

sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
4 development branch. You can make schema changes here and promote it to production once you're ready.

Get the connection credentials

To generate the connection credentials:

  1. Click "Connect" on your database overview page.
  2. Select "Python" from the "Connect With" dropdown.
  3. Copy the credentials into a
    sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
    5 file in your Python application folder.

To ensure a secure connection, PlanetScale uses certificates from a Certificate Authority (CA) that is part of the system root available on almost all platforms. You must specify the path to the CA certificates you trust when connecting to the database. Note that this path depends on your platform. across various operating systems and distributions.

If you copied the credentials from the PlanetScale dashboard, this should already be done for you, as PlanetScale auto-detects your operating system.

If you’re using a Windows system, you need to download the root certificates, and then point to their location with the correct configuration options for the driver you’re using.

The

sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
0 package is one of the most popular Python packages for MySQL. It contains the
sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
7 module, an interface to MySQL that provides the Python database API.

Begin by installing

sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
0. You can do this in two ways:

  1. Use an installer from the official MySQL site that matches the OS and SQL version you’re running.
  2. Use
    sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
    9 to install it:

Terminal

pip install mysqlclient

On Linux, you might need to install the Python3 and MySQL development headers and libraries before installing

sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
0:

Terminal

sudo apt-get install python3-dev default-libmysqlclient-dev build-essential

Once you have

sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
0 installed, you can connect to the database using the following code:

Python

import os
import MySQLdb # import the MySQLdb module
from dotenv import load_dotenv
load_dotenv()

# Create the connection object
connection = MySQLdb.connect(
    host=os.getenv("HOST"),
    user=os.getenv("USERNAME"),
    passwd=os.getenv("PASSWORD"),
    db=os.getenv("DATABASE"),
    ssl_mode="VERIFY_IDENTITY",
    ssl={
        'ca': os.getenv("SSL_CERT")
    }
)

# Create cursor and use it to execute SQL command
cursor = connection.cursor()
cursor.execute("select @@version")
version = cursor.fetchone()

if version:
    print('Running version: ', version)
else:
    print('Not connected.')

Note, you must install

import os
import MySQLdb # import the MySQLdb module
from dotenv import load_dotenv
load_dotenv()

# Create the connection object
connection = MySQLdb.connect(
    host=os.getenv("HOST"),
    user=os.getenv("USERNAME"),
    passwd=os.getenv("PASSWORD"),
    db=os.getenv("DATABASE"),
    ssl_mode="VERIFY_IDENTITY",
    ssl={
        'ca': os.getenv("SSL_CERT")
    }
)

# Create cursor and use it to execute SQL command
cursor = connection.cursor()
cursor.execute("select @@version")
version = cursor.fetchone()

if version:
    print('Running version: ', version)
else:
    print('Not connected.')
2 and import the
import os
import MySQLdb # import the MySQLdb module
from dotenv import load_dotenv
load_dotenv()

# Create the connection object
connection = MySQLdb.connect(
    host=os.getenv("HOST"),
    user=os.getenv("USERNAME"),
    passwd=os.getenv("PASSWORD"),
    db=os.getenv("DATABASE"),
    ssl_mode="VERIFY_IDENTITY",
    ssl={
        'ca': os.getenv("SSL_CERT")
    }
)

# Create cursor and use it to execute SQL command
cursor = connection.cursor()
cursor.execute("select @@version")
version = cursor.fetchone()

if version:
    print('Running version: ', version)
else:
    print('Not connected.')
3 module to access the database credentials in the
sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
5 file.

Note for Windows users: The

sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
0 installation from a wheel file doesn’t support
import os
import MySQLdb # import the MySQLdb module
from dotenv import load_dotenv
load_dotenv()

# Create the connection object
connection = MySQLdb.connect(
    host=os.getenv("HOST"),
    user=os.getenv("USERNAME"),
    passwd=os.getenv("PASSWORD"),
    db=os.getenv("DATABASE"),
    ssl_mode="VERIFY_IDENTITY",
    ssl={
        'ca': os.getenv("SSL_CERT")
    }
)

# Create cursor and use it to execute SQL command
cursor = connection.cursor()
cursor.execute("select @@version")
version = cursor.fetchone()

if version:
    print('Running version: ', version)
else:
    print('Not connected.')
6. Since you want to ensure the database connection is secure, we recommend using another driver, such as mysql-connector-python.

This example uses the connection to fetch the SQL version of the database and then closes the connection.

The rest of the connection methods have nearly the same syntax as this example. This similar syntax stems from the Python database API specification (PEP 249) that encourages consistency among the Python modules used to access databases.

The MySQL Connector/Python module is the official Oracle-supported driver to connect MySQL through Python. The connector is entirely Python, while

sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
0 is written in C. It’s also self-contained, meaning it doesn’t require the MySQL client library or any Python modules outside the standard library.

Note that MySQL Connector/Python doesn’t support the old MySQL server authentication methods, which means that MySQL versions before 4.1 don’t work.

Start by installing the

sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
1 module. It’s recommended to install it using pip:

Terminal

pip install mysql-connector-python

Once installed, use the following code to connect to MySQL:

Python

import os
from dotenv import load_dotenv
from mysql.connector import Error
import mysql.connector

load_dotenv()

connection = mysql.connector.connect(
host=os.getenv("HOST"),
database=os.getenv("DATABASE"),
user=os.getenv("USERNAME"),
password=os.getenv("PASSWORD"),
ssl_ca=os.getenv("SSL_CERT")
)

try:
    if connection.is_connected():
        cursor = connection.cursor()
    cursor.execute("select @@version ")
    version = cursor.fetchone()
    if version:
        print('Running version: ', version)
    else:
        print('Not connected.')
except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    connection.close()

The PyMySQL package is another connector you can use to connect Python to MySQL. It’s a good option if you’re looking for speed, as it’s faster than

sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
1.

You can instal it using pip with:

Terminal

pip install PyMySQL

Then, use the following connection code:

Python

from dotenv import load_dotenv
import pymysql
import os
load_dotenv()
connection = pymysql.connect(
    host=os.getenv("HOST"),
    database=os.getenv("DATABASE"),
    user=os.getenv("USERNAME"),
    password=os.getenv("PASSWORD"),
    ssl_ca=os.getenv("SSL_CERT")
)
cursor = connection.cursor()
cursor.execute("select @@version ")
version = cursor.fetchone()
if version:
    print('Running version: ', version)
else:
    print('Not connected.')
connection.close()

The

sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
3 library is used for accessing a MySQL database from the asyncio framework. Apart from being asynchronous, the connection code is similar to PyMySQL. Note that using
sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
3 requires Python 3.7+ and PyMySQL.

To use

sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
3, install the asynchronous module first with:

Terminal

pip install asyncio

Then, install

sudo apt-get install python3-dev default-libmysqlclient-dev build-essential
3 using:

Terminal

pip install aiomysql

You can then connect to MySQL using the following code:

Python

import os
import asyncio
import aiomysql
import ssl

from dotenv import load_dotenv
load_dotenv()

ctx = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
ctx.load_verify_locations(cafile=os.getenv("SSL_CERT"))

loop = asyncio.get_event_loop()

async def connect_db():
   connection = await aiomysql.connect(
       host=os.getenv("HOST"),
       port=3306,
       user=os.getenv("USERNAME"),
       password=os.getenv("PASSWORD"),
       db=os.getenv("DATABASE"),
       loop=loop,
       ssl=ctx
   )
   cursor = await connection.cursor()
   await cursor.execute("select @@version")
   version = await cursor.fetchall()
   print('Running version: ', version)
   await cursor.close()
   connection.close()
loop.run_until_complete(connect_db())

Persisting data in a database is one of the common features of software applications. Like Python, most programming languages support connection and interaction with different databases.

This tutorial explored connecting Python to MySQL. We shared four different common connectors you can use and tested them on a PlanetScale database using SSL for security — a crucial step, as it prevents man-in-the-middle attacks. We reviewed how the connector worked for each connection method and showed you the code necessary to run it. Now that you’ve worked through this hands-on overview, you’re all set to connect a MySQL database to a Python app.

How do I run MySQL connector in Python?

Procedure to Follow in Python to Work with MySQL.
Connect to the database..
Create an object for your database..
Execute the SQL query..
Fetch records from the result..
Informing the Database if you make any changes in the table..

How do I open MySQL connector?

To connect to MySQL Server:.
Locate the MySQL Command-Line Client. ... .
Run the client. ... .
Enter your password. ... .
Get a list of databases. ... .
Create a database. ... .
Select the database you want to use. ... .
Create a table and insert data. ... .
Finish working with the MySQL Command-Line Client..

Is MySQL connector a library in Python?

It is written in pure Python and does not have any dependencies except for the Python Standard Library. ... There are various versions of MySQL Connector/Python available:.

Where does MySQL connector install Python?

Installation. Packages are available at the Connector/Python download site. For some packaging formats, there are different packages for different versions of Python; choose the one appropriate for the version of Python installed on your system.