🤔 How to create a MySQL connection and run queries with Python3

Benjamin Mock - Jan 29 '20 -

- Dev Community

Want to get better at Web Development 🚀🚀🚀? Subscribe to my weekly newsletter at https://codesnacks.net/subscribe/

We will use pymysql to connect to MySQL in Python3. First, you have to install the module using pip or pip3:

pip install -U pymysql
pip3 install -U pymysql

Then you can use this code to connect to your database and do a simple query:

import pymysql

conn = pymysql.connect(host='127.0.0.1', unix_socket='/tmp/mysql.sock',
                       user='root', passwd="your_pw", db='your_db')
cur = conn.cursor()
cur.execute("SELECT * FROM your_table")
for r in cur:
    print(r)  # print whole row
    print(r[0])  # print first column of result

print(cur.description)  # gives a description of the table including all columns

cur.close()
conn.close()

If you don't want to access columns by index, but by name, you need to pass cursorclass=pymysql.cursors.DictCursor to the connection.

import pymysql

# add cursorclass to access columns by name
conn = pymysql.connect(host='127.0.0.1', unix_socket='/tmp/mysql.sock', user='root',
                       passwd="your_pw", db='your_db', cursorclass=pymysql.cursors.DictCursor)
cur = conn.cursor()
cur.execute("SELECT * from your_table")
for r in cur:
    print(r["id"]) # now you can access columns by name (assuming you have an id column)
print(cur.description)
cur.close()
conn.close()