Connecting Python with MySQL: connect(), cursor(), execute() and close() (Class 12 Computer Science)

Understand Connecting Python to MySQL: connect(), cursor() and execute()

import mysql.connector as sqltor
try:
    con = sqltor.connect(host="localhost", user="root", passwd="admin123", database="school")
    if con.is_connected():
        print("Connected")
    con.close()
except sqltor.Error:
    print("Could not connect")

Connecting Python to MySQL: connect(), cursor() and execute(): key facts

Six steps

• •import mysql.connector
• •connect(host, user, passwd, database)
• •con.cursor()
• •cur.execute(query)
• •fetch or commit
• •con.close()

Every board program follows this order.

connect() arguments

host	server, localhost
user	account name
passwd	password
database	database to use

The password is never printed.

Connection and cursor

con.is_connected()	True if open
con.cursor()	creates a cursor
cur.execute(sql)	runs a query
con.close()	ends the connection

A wrong login raises mysql.connector.Error.

Alias

• •import mysql.connector as sqltor
• •then sqltor.connect(...)
• •sqltor.Error

Any alias is fine; use it consistently.

How to answer Connecting Python to MySQL: connect(), cursor() and execute() questions

1. 1Import mysql.connector (with an alias) at the top.
2. 2Call connect with host, user, passwd and database; keep the result in a variable such as con.
3. 3Create the cursor with cur = con.cursor() (do not forget the brackets).
4. 4Put the SQL statement in a string and pass it to cur.execute().
5. 5Add the fetch loop for SELECT, or con.commit() for INSERT, UPDATE and DELETE.
6. 6Close the connection at the end; wrap steps in try-except if a failed connection must be handled.

Connecting Python to MySQL: connect(), cursor() and execute() in real life

The idea, met in an everyday situation. Answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Tricks to remember Connecting Python to MySQL: connect(), cursor() and execute()

Short memory aids. Say each one aloud once, then use it on a question the same day.

• I-C-C-E-F-C

  Import, Connect, Cursor, Execute, Fetch or commit, Close.

  Use it when: The sequence of steps.

• Connection is the line, cursor is the speaker

  You need both to make a call.

  Use it when: Connection versus cursor.

• Brackets on cursor()

  cursor is a method; call it with ().

  Use it when: Common syntax error.

• Close the door

  Always end with con.close().

  Use it when: Last step.

Connecting Python to MySQL: connect(), cursor() and execute(): solved examples

Ordered from board level to HOTS. Try each one before you open the solution.

import mysql.connector as sqltor
conn = sqltor.connect(host="localhost", user="admin", passwd="admin123", database="school")
cur = ____(1)____
cur.____(2)____("SELECT Name, City FROM STUDENT ORDER BY RollNo")
for row in cur:
    print(row)
conn.____(3)____

import mysql.connector
con = mysql.connector.connect(host="localhost", user="root", passwd="admin123", database="school")
cur = con.cursor
cur.execute("SELECT Name FROM STUDENT")
print(cur.fetchone())

Where students lose marks in Connecting Python to MySQL: connect(), cursor() and execute()

The slips examiners see most often, and what to write instead.

• ✗Mistake: Writing con.cursor without brackets

  cursor is a method; without brackets cur holds the method itself.

  Wrong working

  cur = con.cursor

  Right working

  cur = con.cursor()

  ✓Do this: Check every method call for ().

  Usually costs: 1 mark

• ✗Mistake: Executing before creating the cursor

  The query is run through the cursor, not the connection.

  Wrong working

  con.execute("SELECT * FROM STUDENT")

  Right working

  cur = con.cursor()
  cur.execute("SELECT * FROM STUDENT")

  ✓Do this: Create the cursor, then execute.

  Usually costs: 1 mark

• ✗Mistake: Forgetting to close the connection

  Resources stay open and the board expects the sixth step.

  Wrong working

  (no close statement)

  Right working

  con.close()

  ✓Do this: End the program with close().

  Usually costs: 0.5 mark

Practice questions on Connecting Python to MySQL: connect(), cursor() and execute()

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

1. Q1 · 1 mark · MCQBoard level

   The method of the connection object that creates a cursor is:
   (a) connect() (b) cursor() (c) execute() (d) commit()

   Hint

   Think of the object that carries SQL.

   Answer

   (b) cursor()

2. Q2 · 1 mark · Very short answerBoard level

   Which method closes the connection to the database?

   Hint

   The last step.

   Answer

   con.close()

3. Q3 · 2 marks · Short answerHarder board level

   Write the statements to import mysql.connector with the alias m and connect to the database school on localhost as user root with the password pw123.

   Hint

   Four arguments.

   Answer

   import mysql.connector as m
   con = m.connect(host="localhost", user="root", passwd="pw123", database="school")

4. Q4 · 1 mark · Assertion-reasonHarder board level

   Assertion (A): A program that gives a wrong password to connect() simply gets False from is_connected().
   Reason (R): connect() raises mysql.connector.Error when the login fails.
   (a) Both A and R are true and R is the correct explanation of A. (b) Both A and R are true but R is not the correct explanation of A. (c) A is true but R is false. (d) A is false but R is true.

   Hint

   Is a connection object ever created?

   Answer

   (d) A is false and R is true: no connection object is returned, so is_connected() is never reached.

5. Q5 · 3 marks · Short answerHOTS / competency

   Suppose a program must print Welcome if the connection is made, but tell the user to check the password when the login fails. Write the code using try-except, and explain why an if-else alone cannot handle the failure.

   Hint

   The error happens inside connect().

   Answer

   import mysql.connector as m
   try:
       con = m.connect(host="localhost", user="root", passwd="pw", database="school")
       print("Welcome")
       con.close()
   except m.Error:
       print("Check the password")

   If the login fails, connect() raises an exception and no connection object exists to test with if-else, so the except block must catch it.

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1. 1.Which module connects Python to MySQL?Show answer

   mysql.connector.

2. 2.Which function opens a connection?Show answer

   connect().

3. 3.Which method gives a cursor?Show answer

   con.cursor().

4. 4.Which method runs an SQL statement?Show answer

   cur.execute().

5. 5.What does connect() raise on a failed login?Show answer

   mysql.connector.Error.

6. 6.Which method ends the connection?Show answer

   close().

Connecting Python to MySQL: connect(), cursor() and execute(): frequently asked questions