MENU


Python

Python connects to MySQL through the mysql-connector-python driver, which provides a DB-API-style connection object and a cursor object for running statements.

Install

Install the connector with pip:
python -m pip install mysql-connector-python

Installs the MySQL Connector/Python package for the active Python interpreter.

Connect()

Open a connection and obtain a cursor:
import mysql.connector
mydb = mysql.connector.connect(host=..., user=..., password=..., database=...)
mycursor = mydb.cursor()

mysql.connector.connect() returns a connection object; cursor() returns a cursor bound to that connection, used to run statements and fetch results.


ch07-mysql-connect.py:
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="mydatabase"
)
mycursor = mydb.cursor()

Execute()

execute() runs a single statement on the cursor – both DDL (see Table Definitions) and queries (see Data Retrieval). After a DDL or SHOW statement, iterating the cursor directly yields each result row; after a SELECT, fetchall() returns every row as a list of tuples. The snippet below continues from the connection opened above – it creates the database and a customers table, then queries the (still empty) table:


ch07-mysql-execute.py:
# ****** DDL via execute()
mycursor.execute("CREATE DATABASE mydatabase")
mycursor.execute("SHOW DATABASES")
for x in mycursor:
    print(x)

mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
mycursor.execute("SHOW TABLES")
for x in mycursor:
    print(x)

# ****** Query via execute() + fetchall()
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

Commit()

MySQL Connector/Python does not autocommit by default, so data-changing statements – DELETE, INSERT, UPDATE (see Data Manipulation) – must be followed by mydb.commit() to persist the change. executemany() runs the same parameterized statement once per row of a supplied list, which is the idiomatic way to bulk-insert data. After either execute() or executemany(), mycursor.rowcount gives the number of rows affected by the statement. Continuing the same cursor from above – the customers table is still empty at this point, so the DELETE below matches nothing:


ch07-mysql-commit.py:
# ****** DELETE, then commit()
sql = "DELETE FROM customers WHERE address = 'Mountain 21'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")

# ****** Bulk INSERT via executemany(), then commit()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
    ('Peter', 'Lowstreet 4'),
    ('Amy', 'Apple st 652'),
    ('Hannah', 'Mountain 21'),
    ('Michael', 'Valley 345'),
    ('Sandy', 'Ocean blvd 2'),
    ('Betty', 'Green Grass 1'),
    ('Richard', 'Sky st 331'),
    ('Susan', 'One way 98'),
    ('Vicky', 'Yellow Garden 2'),
    ('Ben', 'Park Lane 38'),
    ('William', 'Central st 954'),
    ('Chuck', 'Main Road 989'),
    ('Viola', 'Sideway 1633')
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "was inserted.")

# ****** UPDATE, then commit()
sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")

0 record(s) deleted 13 was inserted. 1 record(s) affected