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 简单实例

#!/usr/bin/python
import MySQLdb

# Open a database connection
conn = MySQLdb.connect('192.168.0.30','dbuser','123','mydb')

# Create a cursor objec using cursor()
cursor = conn.cursor()

# SQL statement
sql = 'SHOW variables like "%char%"';

# Execute SQL statement using execute()
cursor.execute(sql)

# Get data 
data = cursor.fetchall()

print data

# Close database connection
cursor.close()

  

2.2 Insert 插入数据

#!/usr/bin/python
import MySQLdb

'''Insert'''
# Open a database connection
conn = MySQLdb.connect('192.168.0.30','dbuser','123','mydb')

# Create a cursor objec using cursor()
cursor = conn.cursor()

# SQL statement
sql = 'INSERT INTO mytable(id,name) VALUES(2001,"Heburn"),(2002,"Jerry");'

try:
    # Execute SQL statement using execute()
    result = cursor.execute(sql)
    # Commit
    conn.commit()
    print 'Insert',result,'records'
except:
    # Rollback in case there is any error
    conn.rollback()

# Close database connection
cursor.close()

  

2.3 Update 更新数据

#!/usr/bin/python
import MySQLdb

'''Update'''
# Open a database connection
conn = MySQLdb.connect('192.168.0.30','dbuser','123','mydb')

# Create a cursor objec using cursor()
cursor = conn.cursor()

# SQL statement
sql = 'UPDATE mytable SET name="Lincoln" WHERE id = 2001;'

try:
    # Execute SQL statement using execute()
    result = cursor.execute(sql)
    # Commit
    conn.commit()
    print 'Update',result,'records'
except:
    # Rollback in case there is any error
    conn.rollback()

# Close database connection
cursor.close()

  

2.4 删除数据

#!/usr/bin/python
import MySQLdb

'''Delete'''
# Open a database connection
conn = MySQLdb.connect('192.168.0.30','dbuser','123','mydb')

# Create a cursor objec using cursor()
cursor = conn.cursor()

# SQL statement
sql = 'Delete from mytable WHERE id = 2001;'

try:
    # Execute SQL statement using execute()
    result = cursor.execute(sql)
    # Commit
    conn.commit()
    print 'Delete',result,'records'
except:
    # Rollback in case there is any error
    conn.rollback()

# Close database connection
cursor.close()

  

2.5 查询数据

#!/usr/bin/python
import MySQLdb

'''Select'''
# Open a database connection
conn = MySQLdb.connect('192.168.0.30','dbuser','123','mydb')

# Create a cursor objec using cursor()
cursor = conn.cursor()

# SQL statement
sql = 'SELECT id, name FROM mytable WHERE id = 2002;'

try:
    # Execute SQL statement using execute()
    cursor.execute(sql)

    # Get all records
    results = cursor.fetchall()
    for row in results:
        id = row[0]
        name = row[1]
        print 'id = %d, name = %s' % (id,name)

except:
    print "Error: can't queray any data."

# Close database connection
cursor.close()

  

2.6 创建表

#!/usr/bin/python
import MySQLdb

'''Create table'''
# Open a database connection
conn = MySQLdb.connect('192.168.0.30','dbuser','123','mydb')

# Create a cursor objec using cursor()
cursor = conn.cursor()

# SQL statement
sql = '''
CREATE TABLE mytable (
    id int,
    name char(20)
) ENGINE = InnoDB DEFAULT CHARSET=utf8;
'''

try:
    # Execute SQL statement using execute()
    cursor.execute(sql)
except:
    print "Error: can't Create table mytable."

# Close database connection
cursor.close()

  

---

 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()

2.3 Insert 插入数据

# Insert
conn = pymysql.connect('192.168.0.30','dbuser','123','mydb')
cursor = conn.cursor()
sql = 'insert into mytable(id,name) values(1001, "Andrew");'
try:
    cursor.execute(sql)
    conn.commit()
except:
    conn.rollback()

conn.close()

2.4 Update 更新数据

# Update
conn = pymysql.connect('192.168.0.30','dbuser','123','mydb')
cursor = conn.cursor()
sql = 'update mytable set name = "Heburn" where id = 1001;'
try:
    cursor.execute(sql)
    conn.commit()
except:
    conn.rollback()

conn.close()

2.5 Delete 删除数据

# Delete
conn = pymysql.connect('192.168.0.30','dbuser','123','mydb')
cursor = conn.cursor()
sql = 'delete from mytable where id = 1001;'
try:
    cursor.execute(sql)
    conn.commit()
except:
    conn.rollback()

conn.close()

2.6 Select 查询数据

fetchone() 获取查询结果集中的一行内容

fetchall() 获取查询结果集中的所有行内容

# Database Query
conn = pymysql.connect('192.168.0.30','dbuser','123','mydb')
cursor = conn.cursor()
sql = 'select * from mytable where id = 1001;'
try:
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        id = row[0]
        name = row[1]
        print("id = %d, name = %s" % (id,name))
except:
    print('Error: unable to fetch data.')

conn.close()