Python programmers connect to databases, write new data, or update existing data regularly throughout the day. Python has rich libraries to connect to databases (both SQL and NoSQL).
There are a lot of ways to connect to the MySQL database itself. In this article, we will cover the best ways to connect to MySQL in Python.
Libraries to connect to MySQL in Python
Below Python libraries support functions to connect to MySQL db in Python:
- Python MySQL Connector
- PyMySQL
- SQLAlchemy
- MySQLdb
- oursql
- aiomysql
Recommended Libraries
We recommend the below libraries to connect to MySQL based on regular updates, community, and documentation.
- SQLAlchemy
- Python MySQL Connector
- PyMySQL
What’s best for you?
➤ If you just need a simple way to connect to MySQL database, then use PyMySQL or Python MySQL Connector library.
➤ If you want a feature-rich library with lot of in-built options to connect to database, elegant querying and error handling, then opt for SQLAlchemy library. The other best thing about SQLAlchemy is, it’s ability to supports multiple database like Postgres, MSSQL Server etc.
1. Using Python MySQL Connector
Install Python MySQL Connector
We can install the MySQL Connector with the below command using pip3
. Alternatively, You can visit our complete guide on Python MySQL Connector installation on different platforms.
pip3 install mysql-connector-python
Connection using Python MySQL Connector
Using Python MySQL connector library, we can establish the database connection in two ways.
- Using
mysql.connector.connect()
function - Using
mysql.connector.MySQLConnection()
class
Both the methods are pretty much straight-forward and there are no performance related differences. However, mysql.connector.connect() method require fewer lines of code compared to other method.
In the below examples, we are going to use connector.connect()
function or mysql.connector.MySQLConnection()
to create a MySQL database connection.
Both of these provides a lot of options on how do we connect and query the DB. Here are a few important parameters this function accepts.
host
: Host name or IP address of the MySQL database serverport
: Port number on which MySQL service is running. The default port for MySQL is 3306.user
: User name of the database connectionpassword
: Password for the user
The other allowed parameters includes auto_commit
, raw
, connection_timeout
etc.
1. Connection without connection pooling
This is a plain way of connecting to MySQL DB from Python code. It can be used in personal projects but not a good idea to connect to MySQL directly without a connection pool in a production level code base.
Code Thread:
from mysql import connector
try:
connection = connector.connect(host='host', port='port', database='database', user='user', password='password')
print('Connected to MySQL:', connection.is_connected())
except Exception as e:
print('Error connecting to MySQL Database:', e)
Output:
Connected to MySQL: True
2. Connection with connection pooling
When you pass any one of the pool_name
or pool_size
to connect()
function, the connection pool will be created automatically.
Code Thread 1:
from mysql import connector
try:
# keeping credentials in the code is a potential risk. instead you should store them in a configuration file, environment variables.
connection = connector.connect(host='host', port='port', database='db', user='user', password='password', pool_name='my_pool', pool_size=8)
print('Connected to MySQL:', connection.is_connected())
except Exception as e:
print('Error connecting to MySQL Database:', e)
Output:
Connected to MySQL: True
Code Thread2 – pool connection:
Here is the example code for MySQL connection using MySQLConnectionPool
class instead of connect()
method.
from mysql.connector import pooling
try:
pool = pooling.MySQLConnectionPool(host='host', port='post', database='db', user='user',
password='pwd', pool_name= 'my_pool', pool_size= 8)
connection = pool.get_connection()
print('db server version:', connection.get_server_info())
except Exception as e:
print('Error creating a pool:', e)
Output:
db server info: 8.0.26
Possible errors to watch out for:
One of the common error you will face while connecting to the MySQL database is the below one.
Error connecting to MySQL Database: 2003 (HY000): Can't connect to MySQL server on 'X.X.X.X:X'
The following things need to be checked out when you encounter the above error.
- Firewall enabled: Check if the firewall is enabled on the MySQL port.
- Accepting only local connections: Check the file
mysqld.cnf
on the server in the path/etc/mysql/mysqld.conf.d
. update thebind-address
field to0.0.0.0
- IP is not granted permission to access MySQL DB: Grant permission to your IP to the MySQL database with the below command.
GRANT ALL ON database.* TO user@ip IDENTIFIED BY 'password'
If you have done all the above three things, don’t forget to restart the MySQL server with the below command.
sudo systemctl restart mysql
2. Using PyMySQL
Install Python PyMySQL
Use the below commands to install PyMySQL to install in case you haven’t installed it.
sudo pip3 install PyMySQL
python3 -m pip install PyMySQL
Using PyMySQL in Python
Code Thread:
import pymysql
try:
connection = pymysql.connect(host='host', user='wordpress', password='passwrd', database='db')
with connection.cursor() as cursor:
sql = "SELECT `id`, `name` FROM `employee` WHERE `email`=%s"
cursor.execute(sql, ('mbap@codethreads.dev',))
result = cursor.fetchone()
print(result)
except Exception as e:
print('Error connecting to MySQL:', e)
Output:
(1,mbappe)
3. Use SQLAlchemy
The SQLAlchemy offers database agnostic functions. The same methods would work for different database vendors like MySQL, Postgres, Oracle etc.
It offers ORM style of SQL Queris there by code is much type safe and readable.
Let’s say we have an Employee table with ID, name, email and employment_type fields. We can create ORM mapping for the table and connect to MySQL DB from Python to query this. Below is the code for this.
Code Thread:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, String, Integer, Boolean, DateTime, Numeric
from sqlalchemy.ext.declarative import declarative_base
base = declarative_base()
class Employee(base):
__tablename__ = 'employee'
__table_args__ = {'extend_existing': True}
ID = Column(Integer, primary_key=True, autoincrement=True, nullable=True)
name = Column(String)
email = Column(String)
employment_type = Column(String)
password='pwd'
user = 'user'
ip = 'ip'
db = 'db'
db_string = "mysql+pymysql://" + user + ":" + password + "@" + ip + ":3306/" + db
db = create_engine(db_string, pool_size=10, max_overflow=0)
Session = sessionmaker(bind=db)
with Session() as session:
result = session.query(Employee.name).filter(Employee.email == 'mabp@codethreads.dev', Employee.employment_type == 'full-time').all()
print(result)
Output:
[('Mbappe',)]
SQLAlchemy is one of the most used and preferred way to connect to MySQL in Python.
Best Practices to store database credentials
It is not a good idea to keep database credentials in the code. If you keep them in the code, they may go to GitHub and vulnerable for leakage. Instead, You can use one of the following options to store credentials securely.
- Use config file
- Use Environment variables
1. Read Credentials from config file
Credentials in config.py
file:
Host = "host"
Port = "post"
Username = "username"
Password = "password"
Database = "Database"
Reading config.py file:
Reading config.py file and making a MySQL Connection in Python using SQLAlchemy. You can read the config.py file the same way in other ways of connecting to MySQL as well.
Code Thread:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, String, Integer, Boolean, DateTime, Numeric
from sqlalchemy.ext.declarative import declarative_base
import config
base = declarative_base()
class Employee(base):
__tablename__ = 'employee'
__table_args__ = {'extend_existing': True}
ID = Column(Integer, primary_key=True, autoincrement=True, nullable=True)
name = Column(String)
email = Column(String)
employment_type = Column(String)
password = config.Password
user = config.Username
ip = config.Host
db = config.Database
db_string = "mysql+pymysql://" + user + ":" + password + "@" + ip + ":3306/" + db
db = create_engine(db_string, pool_size=10, max_overflow=0)
Session = sessionmaker(bind=db)
with Session() as session:
result = session.query(Employee.name).filter(Employee.email == 'mabp@codethreads.dev', Employee.employment_type == 'full-time').all()
print(result)
Output:
[('MBappe',)]
2. Read Credentials from Environment Variables
Another best way to store the credentials is using the Environment variables. These are accessible only on that particular server and can be set from either code or from command line.
Here is the sample code to set the environment variables from code –
import os
#setting the environment variables using os module
os.environ['USER_NAME'] = 'codethreads'
os.environ['PASSWORD'] = 'codethreads_password'
Once you set these, you can always access them from the code with the below code snippet –
import os
user_name = os.getenv('USER_NAME')
password = os.getenv('PASSWORD')
This will return the values stored in those environment variables.
Hope you have got the gist of connect to MySQL in Python. For more such insightful articles, follow codethreads.dev!!
Frequently Asked Questions
-
What is the best MySQL connector for Python?
These are several python libraries to connect to MySQL. Out of which using PyMySQL and SQLAlchemy are the best ways for different use cases. Detailed code with examples mentioned here.
-
Is Python compatible with SQL?
Yes, you can connect to SQL database from Python code in different ways. Make sure to use the latest versions of MySQL to have the support for latest features.