Python with MySQL

Definition

In simple words

SQLite is a notebook on your desk. MySQL is a records office many people can use together, so you connect to it with a username and password.

Why it matters

A very large number of web apps run on MySQL, so you'll meet it in real jobs. The SQL is nearly the same as SQLite's, and only the connection differs.

How it works

pip install mysql-connector-python

import os
import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="app",
    password=os.environ["DB_PASSWORD"],   # never in the code
    database="school",
)
cursor = db.cursor()

cursor.execute("INSERT INTO students (name, mark) VALUES (%s, %s)", ("Riya", 91))
db.commit()                                # save the change

cursor.execute("SELECT name, mark FROM students WHERE mark > %s", (80,))
for name, mark in cursor.fetchall():
    print(name, mark)

cursor.close()
db.close()

Common mistakes

• Forgetting db.commit(), so inserts vanish.
• Putting the password in code.
• Using ? here. This driver uses %s.

Check yourself

Summary

• Connect, cursor, execute, commit.
• Use %s placeholders, and keep the password out of code.
• The SQL is nearly the same as SQLite's.