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.
$ 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
$ 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.
In : import mysql.connector
In : conn = mysql.connector.connect(host="localhost", user="user", password="password", database="mysql")
In : cursor = conn.cursor()
In : stmt = "SELECT * FROM user"
In : cursor.execute(stmt)
In : records = cursor.fetchall()
In : print("Total users: ", cursor.rowcount) Total users: 2 In : for record in records: ...: print("User: ", record) ...: User: bytearray(b'root') User: bytearray(b'user')
In : conn.close()
In : cursor.close() Out: True
#!/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) #Close connection to database conn.close() #Close cursor cursor.close()
Comment anonymously. Login not required.