python 包之 pymysql 数据库操作教程

一、安装

pip install pymysql

 

二、连接数据库

• 三种连接数据库的方式

import pymysql 

# 方式一
conn = pymysql.connect('localhost', 'root', 'root')

# 方式二
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='', charset='utf8')

# 方式三
config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'passwd': 'root',
    'charset': 'utf8'
}
conn = pymysql.connect(**config)

 

三、创建数据库

• 创建一个test数据库并进入

import pymysql

db = pymysql.connect("localhost", "root", "root", "test")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

cursor.execute('DROP DATABASE IF EXISTS test')
cursor.execute('CREATE DATABASE IF NOT EXISTS test')
conn.select_db('test')

 

四、创建数据表

• 创建一张user表

import pymysql

db = pymysql.connect("localhost", "root", "root", "test")
cursor = db.cursor()

cursor.execute('CREATE TABLE user(id int primary key,name varchar(30))')

 

五、插入一条数据

import pymysql

db = pymysql.connect("localhost", "root", "root", "test")
cursor = db.cursor()

try:
    # 执行SQL语句
    sql = 'INSERT INTO user values("%d","%s")' %(1,"autofelix")
		cursor.execute(sql)
    # 提交到数据库执行
    db.commit()
except:
    # 发生错误时回滚
    db.rollback()
finally:
    # 关闭游标连接
    cursor.close()
    # 关闭数据库连接
    conn.close()

 

六、插入多条数据

import pymysql

db = pymysql.connect("localhost", "root", "root", "test")
cursor = db.cursor()

try:
    # 执行SQL语句
    values = [(1, 'autofelix'), (2, '飞兔小哥')]
		cursor.executemany('INSERT INTO user values(%s,%s)', values)
    # 提交到数据库执行
    db.commit()
except:
    # 发生错误时回滚
    db.rollback()
finally:
    # 关闭游标连接
    cursor.close()
    # 关闭数据库连接
    conn.close()

 

七、数据统计

import pymysql

db = pymysql.connect("localhost", "root", "root", "test")
cursor = db.cursor()

count = cursor.execute('SELECT * FROM user')
# 统计数据总数
print('total records: %d' %count)
# 统计字段数
print('total records:', cursor.rowcount)

 

八、获取表名信息

import pymysql

db = pymysql.connect("localhost", "root", "root", "test")
cursor = db.cursor()

desc = cursor.description
print("%s %3s" % (desc[0][0], desc[1][0]))

 

九、获取单条数据

• 使用 fetchone 方法获取单条数据

import pymysql


db = pymysql.connect("localhost", "root", "root", "test")
cursor = db.cursor()

# 使用 execute()  方法执行 SQL 查询
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()

print("Database version : %s " % data)

# 关闭数据库连接
db.close()

 

十、查询多条数据

import pymysql

db = pymysql.connect("localhost", "root", "root", "test")
cursor = db.cursor()

cursor.execute('SELECT * FROM user')

results = cursor.fetchmany(5)
for r in results:
  print (r)

 

十一、查询所有数据

import pymysql

db = pymysql.connect("localhost", "root", "root", "test")
cursor = db.cursor()

cursor.execute('SELECT * FROM user')

results = cursor.fetchall()
for r in results:
  print (r)

 

十二、上下文管理

• 每次都连接关闭很麻烦，使用上下文管理，简化连接过程

import pymysql
import contextlib

# 定义上下文管理器，连接后自动关闭连接
@contextlib.contextmanager
def mysql(host='127.0.0.1', port=3306, user='root', passwd='', db='test',charset='utf8'):
  conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset=charset)
  cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
  try:
    yield cursor
  finally:
    conn.commit()
    cursor.close()
    conn.close()

# 执行sql
with mysql() as cursor:
  print(cursor)
  count = cursor.execute("select * from user")
  row_1 = cursor.fetchone()
  print row_count, row_1