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 import MySQLdb
 3 
 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 print data
20 
21 # Close database connection
22 cursor.close()

View Code

2.2 Insert 插入数据

 1 #!/usr/bin/python
 2 import MySQLdb
 3 
 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 import MySQLdb
 3 
 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 import MySQLdb
 3 
 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 import MySQLdb
 3 
 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 in results:
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 import MySQLdb
 3 
 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 简单实例

import pymysql

# 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 in results:
 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

Andraw|朱标