python 实现 pymysql 数据库操作方法_Python

一、安装

				?

									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