mysql 根据子查询的结果查询朱标_Python - MySQL数据库操作

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 conn.commit()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 conn.commit()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 conn.commit()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 conn.commit()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 conn.commit()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 conn.commit()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