Python mysql tutorial

MySQL Database with Python

---

MySQL Connector/Python is the official MySQL driver for Python connection to MySQL database, many beginners still have some difficulty to connect mysql database in python.

MySQLdb currently only supports up to python 2.7, use mysql.connector.

Here is a summary of the use of the MySQL Connector module with examples.

Python MySQL

MySQL server (or MariaDb) should be installed and running, it can be running on another computer (server).

You must have the module mysql.connector installed, use pip to install the module.

To test if your database is working, you can use the code below:

# -*- coding:utf-8 -*-
import mysql.connector

# Connect with database, change parameters
cnn = mysql.connector.connect(user='root',passwd='root',database='testdb')

# create cursor
cursor = cnn.cursor()

# execute a query
cursor.execute("SELECT VERSION()")

# fetch version
data = cursor.fetchone()
print("Database version : %s " % data)

# close connection
cnn.close()

Database connection

The code to connect the database is as follows. Change the parameters username, password and database and the host address if it's not localhost mysql server.

import mysql.connector
config={'host':'127.0.0.1',#default 127.0.0.1
        'user':'root',
        'password':'123456',
        'port':3306 ,#default is 3306
        'database':'testdb',
        'charset':'utf8'# is utf8 by default
        }
try:
  cnn=mysql.connector.connect(**config)
except mysql.connector.Error as e:
  print('connect fails!{}'.format(e))

The connection method is slightly different from the MySQLdb module, which uses the = sign, and here the : sign.

Creating a table

Tables can be created with the SQL query CREATE TABLE ....

Below we create a table named student based on a new database connection above. The code is as follows (you should already be connected with the database).

# create table query
sql_create_table='CREATE TABLE `student` 
(`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
PRIMARY KEY (`id`)
ENGINE=MyISAM DEFAULT CHARSET=utf8'

# run query
cursor=cnn.cursor()
try:
  cursor.execute(sql_create_table)
except mysql.connector.Error as e:
  print('create table orange fails!{}'.format(e))

Insert data

The SQL query `INSERT INTO table (col1, col2,..) VALUES (....)" inserts a new record into the database.

The syntax for inserting data is essentially the same as on MySQLdb.

The code is as follows

cursor=cnn.cursor()
try:
  'First: direct string insertion method'
  sql_insert1="insert into student (name, age) values ('orange', 20)"
  cursor.execute(sql_insert1)

  'Second: tuple connection insertion method'
  sql_insert2="insert into student (name, age) values (%s, %s)"
  #The %s here are placeholders, not formatted strings, so AGE uses %s
  data=('shiki',25)
  cursor.execute(sql_insert2,data)

  'Third: dictionary connection insertion method'
  sql_insert3="insert into student (name, age) values (%(name)s, %(age)s)"
  data={'name':'mumu','age':30}
  cursor.execute(sql_insert3,data)
  #If the database engine is Innodb, execute cnn.commit() for transaction commit after execution
except mysql.connector.Error as e:
  print('insert datas error!{}'.format(e))
finally:
  cursor.close()
  cnn.close()

Similarly, the MySQL Connector supports multiple insertions, also using cursor.executeemany, for example.

stmt='insert into student (name, age) values (%s,%s)'
data=[
     ('Linda', 31),
     ('Carlos', 32),
     ('Laura', 31)]
cursor.executeemany(stmt,data)

Query operation

You can run the SELECT query to fetch data from a database table. The code is as follows

cursor=cnn.cursor()
try:
  sql_query='select id,name from student where age > %s'
  cursor.execute(sql_query,(21,))
  for id,name in cursor. print ('%s\'s age is older than 25,and her/his id is %d'%(name,id))
    print ('%s\'s age is older than 25,and her/his id is %d'%(name,id))
except mysql.connector.Error as e:
  print('query error!{}'.format(e))
finally:
  cursor.close()
  cnn.close()

Delete operation

Deletion from the database can be done with the query DELETE FROM table WHERE .... The code is as follows

cursor=cnn.cursor()
try:
  sql_delete='delete from student where name = %(name)s and age < %(age)s'
  data={'name':'orange','age':24}
  cursor.execute(sql_delete,data)
except mysql.connector.Error as e:
  print('delete error!{}'.format(e))
finally:
  cursor.close()
  cnn.close()