You can access MySQL and MariaDB databases from Python using a helper module. Some of the more popular Python modules to access MySQL and MariaDB databases are mysql-connector and PyMySQL.
These modules are more similar than they are different, and for this example, we'll be using the mysql-connector module.
Steps to access MySQL database from Python:
- Install pip for your system.
- Install mysql-connector module using pip.
$ pip3 install mysql-connector Collecting mysql-connector Downloading mysql-connector-2.2.9.tar.gz (11.9 MB) |████████████████████████████████| 11.9 MB 11.6 MB/s Building wheels for collected packages: mysql-connector Building wheel for mysql-connector (setup.py) ... done Created wheel for mysql-connector: filename=mysql_connector-2.2.9-cp38-cp38-linux_x86_64.whl size=247948 sha256=4baaca9e6150eb5bac152a3ec95dee71ff2235f83fdd41b83bf5c89346adef29 Stored in directory: /home/user/.cache/pip/wheels/57/e4/98/5feafb5c393dd2540e44b064a6f95832990d543e5b4f53ea8f Successfully built mysql-connector Installing collected packages: mysql-connector Successfully installed mysql-connector-2.2.9
- Run your preferred Python shell.
$ ipython3 Python 3.8.2 (default, Apr 27 2020, 15:53:34) Type 'copyright', 'credits' or 'license' for more information IPython 7.13.0 -- An enhanced Interactive Python. Type '?' for help.
- Import mysql.connector module.
In [1]: import mysql.connector
- Establish connection to your MySQL or MariaDB database.
In [2]: conn = mysql.connector.connect(host="localhost", user="user", password="password", database="mysql")
- Create a cursor for your connection.
In [3]: cursor = conn.cursor()
- Create an SQL statement.
In [4]: stmt = "SELECT * FROM user"
- Execute your SQL statement.
In [5]: cursor.execute(stmt)
- Get records from executed statement.
In [6]: records = cursor.fetchall()
- Process records accordingly.
In [7]: print("Total users: ", cursor.rowcount) Total users: 2 In [8]: for record in records: ...: print("User: ", record[1]) ...: User: bytearray(b'root') User: bytearray(b'user')
- Close connection to database.
In [9]: conn.close()
- Close cursor.
In [10]: cursor.close() Out[10]: True
- Combine steps into a Python script.
- access-mysql-database.py
#!/usr/bin/env python3 #Import required module import mysql.connector #Establish connection to database conn = mysql.connector.connect(host="localhost", user="user", password="password", database="mysql") #Create cursor cursor = conn.cursor() #Create an SQL statement stmt = "SELECT * FROM user" #Execute SQL statement cursor.execute(stmt) #Get records from executed statement records = cursor.fetchall() #Process print("Total users: ", cursor.rowcount) for record in records: print("User: ", record[1]) #Close connection to database conn.close() #Close cursor cursor.close()
![](https://www.simplified.guide/_media/page/author/shakir/mohd-shakir-zakaria.jpg?w=200&tok=d15487)
Author: Mohd
Shakir Zakaria
Mohd Shakir Zakaria is an experienced cloud architect with a strong development and open-source advocacy background. He boasts multiple certifications in AWS, Red Hat, VMware, ITIL, and Linux, underscoring his expertise in cloud architecture and system administration.
![](data:image/x-icon;base64,iVBORw0KGgoAAAANSUhEUgAAABgAAAAYCAYAAADgdz34AAABOElEQVRIia2VYXHDMAyFPwiBEAiBYAYLg5lBC8EQyqBmsDFoGKQMFgYNg+1H5jtFtmMn6bvzj9jWe7IUSVBGD9yBh1p34KPCPosr8AJ+C+sFXPYQd8BYQazX+G9bJK/xeus1WRFJ7oAGsMAkCCbgKb5ncbcXIm1KQBoasd9kvGoTRDJcK1jWT+1THhTQKg4rD+c3CUien3BgFPlwgDzAKS4DcGPjaTvRKa4bLB5HqicQReOdAk2NwPWEgKkReJwQ0PkcALzaPBqmhrjNeIiLLJT7HpFcg7RBWcbfsQ7XhXyr+GSZC7nm14TLXnjeERdMKvH6n9fLa29CmYckO5YkfUtPFKYM+Uyio1pxoXZCDRkBmzPw4tIIfLH926YEfMkrlzCqFaguUsN6AJUEnhxsMT1LknNwFGbHH5UF4DVNwliLAAAAAElFTkSuQmCC)
Mohd Shakir Zakaria is an experienced cloud architect with a strong development and open-source advocacy background. He boasts multiple certifications in AWS, Red Hat, VMware, ITIL, and Linux, underscoring his expertise in cloud architecture and system administration.
Discuss the article:
Comment anonymously. Login not required.