Python
can be used to access MySQL
databases with the help of an additional module. There are a few available Python
modules to allow MySQL
database access, and each come with their pros and cons.
This method also works with MariaDB
databases as MariaDB
is for the most part is just a drop-in replacement for MySQL
.
This example will be based on mysql-connector
module, though other modules would function the same with only minor differences.
pip
for your system. 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
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.
mysql.connector
module. In [1]: import mysql.connector
MySQL
or MariaDB
database. In [2]: conn = mysql.connector.connect(host="localhost", user="user", password="password", database="mysql")
In [3]: cursor = conn.cursor()
SQL
statement. In [4]: stmt = "SELECT * FROM user"
SQL
statement. In [5]: cursor.execute(stmt)
In [6]: records = cursor.fetchall()
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')
In [9]: conn.close()
In [10]: cursor.close() Out[10]: True
Python
script. #!/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()
Comment anonymously. Login not required.