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()
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.
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.