Python2 中使用模块 MySQLdb 模块处理数据库的操作，在Python3中使用 PyMySQL
Python2 - 数据库的操作
1. MySQLdb 安装
yum -y install MySQL-python
2. MySQL 数据库操作
2.1 准备以下MySQL数据库环境，便于后面的实验
名称
值
host
192.168.0.30
port
3306
user
dbuser
passowrd
123
database
mydb
table
mytable
2.2 简单实例
1 #!/usr/bin/python
2 importMySQLdb3
4 #Open a database connection
5 conn = MySQLdb.connect('192.168.0.30','dbuser','123','mydb')6
7 #Create a cursor objec using cursor()
8 cursor =conn.cursor()9
10 #SQL statement
11 sql = 'SHOW variables like "%char%"';12
13 #Execute SQL statement using execute()
14 cursor.execute(sql)15
16 #Get data
17 data =cursor.fetchall()18
19 printdata20
21 #Close database connection
22 cursor.close()
View Code
2.2 Insert 插入数据
1 #!/usr/bin/python
2 importMySQLdb3
4 '''Insert'''
5 #Open a database connection
6 conn = MySQLdb.connect('192.168.0.30','dbuser','123','mydb')7
8 #Create a cursor objec using cursor()
9 cursor =conn.cursor()10
11 #SQL statement
12 sql = 'INSERT INTO mytable(id,name) VALUES(2001,"Heburn"),(2002,"Jerry");'
13
14 try:15 #Execute SQL statement using execute()
16 result =cursor.execute(sql)17 #Commit
18 mit()19 print 'Insert',result,'records'
20 except:21 #Rollback in case there is any error
22 conn.rollback()23
24 #Close database connection
25 cursor.close()
View Code
2.3 Update 更新数据
1 #!/usr/bin/python
2 importMySQLdb3
4 '''Update'''
5 #Open a database connection
6 conn = MySQLdb.connect('192.168.0.30','dbuser','123','mydb')7
8 #Create a cursor objec using cursor()
9 cursor =conn.cursor()10
11 #SQL statement
12 sql = 'UPDATE mytable SET name="Lincoln" WHERE id = 2001;'
13
14 try:15 #Execute SQL statement using execute()
16 result =cursor.execute(sql)17 #Commit
18 mit()19 print 'Update',result,'records'
20 except:21 #Rollback in case there is any error
22 conn.rollback()23
24 #Close database connection
25 cursor.close()
View Code
2.4 删除数据
1 #!/usr/bin/python
2 importMySQLdb3
4 '''Delete'''
5 #Open a database connection
6 conn = MySQLdb.connect('192.168.0.30','dbuser','123','mydb')7
8 #Create a cursor objec using cursor()
9 cursor =conn.cursor()10
11 #SQL statement
12 sql = 'Delete from mytable WHERE id = 2001;'
13
14 try:15 #Execute SQL statement using execute()
16 result =cursor.execute(sql)17 #Commit
18 mit()19 print 'Delete',result,'records'
20 except:21 #Rollback in case there is any error
22 conn.rollback()23
24 #Close database connection
25 cursor.close()
View Code
2.5 查询数据
1 #!/usr/bin/python
2 importMySQLdb3
4 '''Select'''
5 #Open a database connection
6 conn = MySQLdb.connect('192.168.0.30','dbuser','123','mydb')7
8 #Create a cursor objec using cursor()
9 cursor =conn.cursor()10
11 #SQL statement
12 sql = 'SELECT id, name FROM mytable WHERE id = 2002;'
13
14 try:15 #Execute SQL statement using execute()
16 cursor.execute(sql)17
18 #Get all records
19 results =cursor.fetchall()20 for row inresults:21 id =row[0]22 name = row[1]23 print 'id = %d, name = %s' %(id,name)24
25 except:26 print "Error: can't queray any data."
27
28 #Close database connection
29 cursor.close()
View Code
2.6 创建表
1 #!/usr/bin/python
2 importMySQLdb3
4 '''Create table'''
5 #Open a database connection
6 conn = MySQLdb.connect('192.168.0.30','dbuser','123','mydb')7
8 #Create a cursor objec using cursor()
9 cursor =conn.cursor()10
11 #SQL statement
12 sql = '''
13 CREATE TABLE mytable (14 id int,15 name char(20)16 ) ENGINE = InnoDB DEFAULT CHARSET=utf8;17 '''
18
19 try:20 #Execute SQL statement using execute()
21 cursor.execute(sql)22 except:23 print "Error: can't Create table mytable."
24
25 #Close database connection
26 cursor.close()
View Code
Python3 - 数据库的操作
1. PyMySQL 安装
2. MySQL 数据库操作
2.1 准备以下MySQL数据库环境，便于后面的实验
名称
值
host
192.168.0.30
port
3306
user
dbuser
passowrd
123
database
mydb
table
mytable
2.2 简单实例
importpymysql#Open the database connection
conn =pymysql.connect(
host= '192.168.0.30',
port= 3306,
user= 'dbuser',
password= '123',
db= 'mydb',
charset= 'utf8')#Create a cursor object using cursor()
cursor =conn.cursor()#SQL statement
sql = 'SELECT VERSION()'
#Execute SQL query using execute()
cursor.execute(sql)#Get a piece single of data
data =cursor.fetchone()print(data)#Close database connection
conn.close()
View Code
2.3 Insert 插入数据
1 #Insert
2 conn = pymysql.connect('192.168.0.30','dbuser','123','mydb')3 cursor =conn.cursor()4 sql = 'insert into mytable(id,name) values(1001, "Andrew");'
5 try:6 cursor.execute(sql)7 mit()8 except:9 conn.rollback()10
11 conn.close()
View Code
2.4 Update 更新数据
1 #Update
2 conn = pymysql.connect('192.168.0.30','dbuser','123','mydb')3 cursor =conn.cursor()4 sql = 'update mytable set name = "Heburn" where id = 1001;'
5 try:6 cursor.execute(sql)7 mit()8 except:9 conn.rollback()10
11 conn.close()
View Code
2.5 Delete 删除数据
1 #Delete
2 conn = pymysql.connect('192.168.0.30','dbuser','123','mydb')3 cursor =conn.cursor()4 sql = 'delete from mytable where id = 1001;'
5 try:6 cursor.execute(sql)7 mit()8 except:9 conn.rollback()10
11 conn.close()
View Code
2.6 Select 查询数据
fetchone() 获取查询结果集中的一行内容
fetchall() 获取查询结果集中的所有行内容
1 #Database Query
2 conn = pymysql.connect('192.168.0.30','dbuser','123','mydb')3 cursor =conn.cursor()4 sql = 'select * from mytable where id = 1001;'
5 try:6 cursor.execute(sql)7 results =cursor.fetchall()8 for row inresults:9 id =row[0]10 name = row[1]11 print("id = %d, name = %s" %(id,name))12 except:13 print('Error: unable to fetch data.')14
15 conn.close()
View Code
