[syntax 14] Python MySQL connector driver

MySQL is the most popular relational database management system.

In this chapter, we will introduce you to use MySQL connector to connect and use mysql. MySQL connector is the official drive provided by mysql.

We can use the pip command to install MySQL connector:

python3 -m pip install mysql-connector

Use the following code to test whether MySQL connector is installed successfully:

import mysql.connector

Execute the above code. If no error is generated, the installation is successful.

Create database connection

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",       # Database host address
  user="root",    # Database user name
  passwd=""   # Database password
)
 
print(mydb)

<mysql.connector.connection.MySQLConnection object at 0x0000019D2E6457F0>

#Create database

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
)

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE test2_db")

mycursor.execute("SHOW DATABASES")

for x in mycursor:
    print(x)

('information_schema',)
('mysql',)
('performance_schema',)
('sys',)
('test1_db',)
('test2_db',)
('test_db',)

#Or we can directly connect to the database. If the database does not exist, the error message will be output

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db'
)

Create data table

# Create data table

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE test(name VARCHAR(255),url VARCHAR(255))")

#Show Datasheet

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

mycursor.execute("SHOW TABLES")

for x in mycursor:
    print(x)

('test',)

#Set primary key

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

mycursor.execute("ALTER TABLE test ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

insert data

#Insert a record
import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

sql = "INSERT INTO test (name,url) VALUES (%s,%s)"
val = ("ECUST","Xu Huan")
mycursor.execute(sql,val)

mydb.commit()

print(mycursor.rowcount,'Record inserted successfully')

1 record inserted successfully

#Insert multiple records
import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

sql = "INSERT INTO test (name,url) VALUES (%s,%s)"
val = [("ECUST","Xu Huan"),
      ("MIT","Wang Yizhuo"),
      ("JHU","Nawukere"),
      ]
mycursor.executemany(sql,val)#Insert multiple records using executemany()

mydb.commit()

print(mycursor.rowcount,'Record inserted successfully')

3 record inserted successfully

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

sql = "INSERT INTO test (name,url) VALUES (%s,%s)"
val = [("PKU","Smith"),
      ]
mycursor.executemany(sql,val)

mydb.commit()

print('A record has been inserted',mycursor.lastrowid)

A record has been inserted 7

Query data

#SELECT statement can be used to query data

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM test")

myresult = mycursor.fetchall() #fetchall() gets all records

for i in myresult:
    print(i)

('ECUST', 'Xu Huan', 1)
('ECUST', 'Xu Huan', 2)
('ECUST', 'Xu Huan', 3)
('MIT', 'Wang Yizhuo', 4)
('JHU', 'Nawukere', 5)
('PKU', 'Smith', 6)
('PKU', 'Smith', 7)

#You can also read the specified field data

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

mycursor.execute("SELECT name,id FROM test")

myresult = mycursor.fetchall()

for i in myresult:
    print(i)

('ECUST', 1)
('ECUST', 2)
('ECUST', 3)
('MIT', 4)
('JHU', 5)
('PKU', 6)
('PKU', 7)

#If you want to read only one piece of data, you can use the fetchone() method

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM test")

myresult = mycursor.fetchone() 

print(myresult)

('ECUST', 'Xu Huan', 1)

#There are conditional restrictions on the data read. You can use the where statement

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM test WHERE name = 'ECUST'")

myresult = mycursor.fetchall() 

for i in myresult:
    print(i)

('ECUST', 'Xu Huan', 1)
('ECUST', 'Xu Huan', 2)
('ECUST', 'Xu Huan', 3)

#Wildcard% can also be used

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM test WHERE name LIKE '%K%'")

myresult = mycursor.fetchall() 

for i in myresult:
    print(i)

('PKU', 'Smith', 6)
('PKU', 'Smith', 7)

#To prevent SQL injection attacks on database queries, you can use the% s placeholder to escape the query's criteria

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

sql = "SELECT * FROM test WHERE name = %s"
na = ('PKU',)

mycursor.execute(sql, na)

myresult = mycursor.fetchall() 

for i in myresult:
    print(i)

('PKU', 'Smith', 6)
('PKU', 'Smith', 7)

#sort

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM test ORDER BY url")

myresult = mycursor.fetchall() 

for i in myresult:
    print(i)

('JHU', 'Nawukere', 5)
('PKU', 'Smith', 6)
('PKU', 'Smith', 7)
('MIT', 'Wang Yizhuo', 4)
('ECUST', 'Xu Huan', 1)
('ECUST', 'Xu Huan', 2)
('ECUST', 'Xu Huan', 3)

#Sort data in descending order based on an attribute

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM test ORDER BY name DESC")

myresult = mycursor.fetchall() 

for i in myresult:
    print(i)

('PKU', 'Smith', 6)
('PKU', 'Smith', 7)
('MIT', 'Wang Yizhuo', 4)
('JHU', 'Nawukere', 5)
('ECUST', 'Xu Huan', 1)
('ECUST', 'Xu Huan', 2)
('ECUST', 'Xu Huan', 3)

#Read the specified number of data
#Limit read data start data
import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM test LIMIT 4 OFFSET 2")
#Using LIMIT statement to specify query data quantity
#Limit start position with OFFSET statement

myresult = mycursor.fetchall() 

for i in myresult:
    print(i)

('ECUST', 'Xu Huan', 3)
('MIT', 'Wang Yizhuo', 4)
('JHU', 'Nawukere', 5)
('PKU', 'Smith', 6)

Delete record

#Delete records using the DELETE FROM statement
import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

mycursor.execute("DELETE FROM test WHERE name LIKE '%K%'")

mydb.commit()
 
print(mycursor.rowcount, " Records deleted")

2 records deleted

#In order to prevent SQL injection attacks on database queries, we can use the% s placeholder to escape the conditions for deleting statements

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

sql = "DELETE FROM test WHERE name = %s"
na = ("ECUST",)

mycursor.execute(sql,na)

mydb.commit()
 
print(mycursor.rowcount, " Records deleted")

3 records deleted

Update table data

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()


sql = "UPDATE test SET name = 'princeton' WHERE name = 'JHU'"
 
mycursor.execute(sql)

mydb.commit()

print(mycursor.rowcount,'Record (s) modified')

2 records modified

#To prevent SQL injection attacks on database queries, we can use the% s placeholder to escape the conditions of the update statement
import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()


sql = "UPDATE test SET name = %s WHERE name = %s"
na = ('JHU','princeton')
 
mycursor.execute(sql,na)

mydb.commit()

print(mycursor.rowcount,'Record (s) modified')

2 records modified

Delete table

#DROP TABLE statement is used to delete the table, and IF EXISTS keyword is used to judge whether the table is respected
import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    database = 'test_db',
)

mycursor = mydb.cursor()

sql = 'DROP TABLE IF EXISTS test'

mycursor.execute(sql)

Posted by jabapyth on Tue, 24 Dec 2019 13:35:29 -0800

Programmer Group