Databases & SQL · Class 12 · Python & MySQL Together

Pythonconnectivity⏱️ 9 min read

Connecting Python to MySQLOptional for Informatics Practices

You can write SQL and you can write Python. This chapter joins them: a Python program that stores its data in MySQL instead of in a file. It is the last unit of the Computer Science course, and the one most Class 12 projects are built on.

Who this page is for

Python-SQL connectivity is in the Computer Science (083) Class 12 syllabus, Unit 3. It is not in the Informatics Practices (065) syllabus — IP students move data between MySQL and Pandas instead, and can skip this chapter.

1Why connect them at all

You have written Python programs that keep their data in a text, binary or CSV file. Those work, but you had to do everything by hand: find a record, keep the file consistent, handle two people using it at once.

MySQL already does all of that. What it cannot do is ask the user questions, print a menu or produce a report. So each side does what it is good at:

🐍 Python does

The menus, the input, the calculations, the printed report — everything a person interacts with.

🐬 MySQL does

Storing the data safely, finding it fast, and enforcing the keys and constraints.

2The connector module

Python cannot talk to MySQL on its own. It needs a connector — a module that knows how to carry a query to the server and bring the answer back. Install it once, from the command prompt and not from inside Python:

command prompt

pip install mysql-connector-python

Then import it at the top of the program:

connect.py

import mysql.connector

The name has a dot in it

You install mysql-connector-python with a hyphen, and import mysql.connector with a dot. They look different because one is the package's name on the internet and the other is its name in Python. Both spellings matter.

3Opening the connection

connect() takes four things: which machine the server is on, who you are, your password, and which database you want.

connect.py

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    port=3307,
    user="student",
    passwd="kv@1234",
    database="lambdalab")

print("Connected?", con.is_connected())
con.close()
print("After close:", con.is_connected())

Output

Connected? True
After close: False

host

"localhost" means the server is on this same computer. That is the normal case in a school lab.

user

The MySQL username. In most textbooks and labs this is "root".

passwd

The password for that user, set when MySQL was installed. Also spelled password=.

database

Which database to open — the same thing use lambdalab; does at the prompt.

About that port= line

A standard MySQL install listens on port 3306, and the connector assumes it — so you normally leave port out entirely. The practice server these outputs were run against uses 3307, so the line is shown here to keep the program and its output honestly matched. On your machine, delete it.

4Checking it actually opened

is_connected() returns True or False. It is worth printing while you are learning, because a program that fails at the very first step is otherwise confusing to debug.

Notice the second line of that output: after con.close() the same call returns False. Closing is not just tidiness — the connection genuinely stops working, and any query after it will fail.

5When the details are wrong

Get the password wrong and connect() raises an exception. This is exactly what the Exception Handling chapter was for:

wrongpass.py

import mysql.connector

try:
    con = mysql.connector.connect(
        host="localhost", port=3307,
        user="student", passwd="wrong", database="lambdalab")
except mysql.connector.Error as err:
    print("Error:", err)

Output

Error: 1045 (28000): Access denied for user 'student'@'localhost' (using password: YES)

1045 is the error number to recognise: the username or password is wrong. (using password: YES) tells you a password was sent — if it said NO, you had forgotten the passwd argument altogether.

Three errors worth knowing by number

1045 — wrong user or password. 1049 — unknown database, so check the database= spelling. 2003 — cannot reach the server at all, which usually means MySQL is not running.

6The shape of every program in this chapter

connect()

Open the connection.

cursor()

Make the object that carries queries.

execute()

Run SQL, and fetch any answer.

close()

Let the connection go.

Steps 2 and 3 are the next lesson. Every program you write for the rest of this chapter has this same skeleton.

7Recap

pip install mysql-connector-python

Once, at the command prompt.

import mysql.connector

Hyphen to install, dot to import.

connect(host, user, passwd, database)

Returns a connection object.

is_connected() / close()

Check it opened; let it go when done.

Quick Check

Which statement imports the connector?

Quick Check

What does con.is_connected() return after con.close()?

Quick Check

A program stops with 'Access denied for user … (using password: YES)'. What is wrong?

← Previous

🪢 Natural Join

Cursor, execute() & fetching 📥