Connecting Python to MySQL: Send SQL, Get Rows Back

Why connect Python to a database?

Python is good at logic and user screens; MySQL is good at storing and searching lots of data safely. Together, a Python program (the front end) can store and read data in MySQL (the back end).

Install the connector once: pip install mysql-connector-python. Then:

import mysql.connector as sql

Step by step: connect, cursor, execute

import mysql.connector as sql
con = sql.connect(host='localhost', user='root',
                  passwd='your_password', database='school')
if con.is_connected():
    print('Connected')
cur = con.cursor()
cur.execute('SELECT * FROM student')

• connect() returns a connection object. Arguments: host (the server, often 'localhost'), user, passwd/password, database.
• is_connected() returns True if the connection works.
• cursor() makes a cursor object from the connection. It runs queries and holds the result set.
• execute(query) sends one SQL statement as a string.

Reading results: fetchone, fetchall, fetchmany, rowcount

After a SELECT, the rows wait in the cursor's result set. Each row comes as a tuple.

• fetchone(): the next single row as a tuple, or None when no rows are left.
• fetchall(): all remaining rows as a list of tuples (empty list if none left).
• fetchmany(n): the next n rows as a list.
• rowcount: a property (no brackets). For SELECT it is the number of rows fetched so far; for INSERT/UPDATE/DELETE it is the number of rows affected.

cur.execute('SELECT RollNo, Name FROM student')
r = cur.fetchone()      # (1, 'Asha')
print(cur.rowcount)     # 1
rest = cur.fetchall()   # [(2,'Ravi'), (3,'Zoya')]
print(cur.rowcount)     # 3
for row in rest:
    print(row[0], row[1])

Changing data: INSERT, UPDATE, DELETE and commit()

cur.execute("INSERT INTO student VALUES (4, 'Om', 'Pune', 70)")
con.commit()
print(cur.rowcount, 'row inserted')

cur.execute('UPDATE student SET Marks = Marks + 2 WHERE Marks < 60')
con.commit()
print(cur.rowcount, 'rows updated')

commit() belongs to the connection (con.commit()), not the cursor. It makes the changes permanent. Without it, changes are thrown away when the program ends. (con.rollback() undoes changes not yet committed.) Finally: cur.close() and con.close().

Putting values into queries: %s and format()

Using %s placeholders (preferred)

roll = int(input('Roll: '))
cur.execute('SELECT * FROM student WHERE RollNo = %s', (roll,))
print(cur.fetchone())

cur.execute('INSERT INTO student VALUES (%s, %s, %s, %s)', (5, 'Kabir', 'Jaipur', 66))
con.commit()

Values go in a tuple as the second argument. A single value still needs a comma: (roll,). The connector adds quotes where needed and protects against harmful input (SQL injection). Always write %s, even for numbers.

Using format() or f-strings

q = "SELECT * FROM student WHERE Marks > {} AND City = '{}'".format(80, 'Delhi')
cur.execute(q)

Here the string is built first, so you must add quotes around text values yourself. It is fine for practice, but %s is safer for real input.

Try it: a mini menu program

Write a menu: 1 Add student, 2 Show all, 3 Search by roll, 4 Exit. Use execute with %s for add and search, commit() after add, fetchall() for show and fetchone() for search. Test: add a student and forget commit(); restart and see it is missing. Then add commit() and try again. Use the 3D free-play step to predict what fetchone and fetchall return.

Key formulas and definitions

• con = mysql.connector.connect(host, user, passwd, database) → cur = con.cursor()
• cur.execute(sql[, params]) · con.commit() after INSERT/UPDATE/DELETE
• fetchone() → tuple or None · fetchall() → list of tuples · fetchmany(n) · cur.rowcount
• execute('… %s …', (value,)) · or '… {} …'.format(value)

Worked examples

Common mistakes

• Forgetting con.commit() after INSERT, UPDATE or DELETE, so nothing is saved.
• Writing cur.commit(); commit() belongs to the connection object.
• Passing a single value as (80) instead of (80,); it must be a tuple.
• Calling rowcount() with brackets; it is a property: cur.rowcount.