模块
使用pymysql对mysql数据库进行操作
pip install PyMySQL
连接对象
import pymysql
try:
connection = pymysql.connect(
host = 'localhost',
user = 'root',
password = '123456',
db = 'db_users',
charset = 'utf8',
cursorclass = pymysql.cursors.DictCursor)
print(connection)
except Exception as e:
print(e)
>><pymysql.connections.Connection object at 0x00000132FF4C5FD0>
host ,user,password可以省略不写则如下,设置charset 防止插入中文时出错
db = pymysql.connect(host='localhost',user='root',password='123456',db='db_users',charset='utf8',cursorclass=pymysql.cursors.DictCursor)
成功后常用方法 | 方法 | 说明 | | —- | —- | | cursor() | 获取游标对象,操作数据库,执行DML | | commit() | 提交事务 | | rollback() | 回滚事务 | | close() | 关闭数据库连接 |
游标对象
连接mysql后可以使用游标对象实现python对数据库的操作,常用方法如下:
方法 | 说明 |
---|---|
execute(operation[,parameters]) | 执行数据库操作,SQL语句或者DQL |
executemany(operation,seq_of_params) | 用于批量操作,如批量更新 |
fetchone() | 获取查询结果集中的下一条记录 |
fetchmany(size) | 获取指定数量的记录 |
fetchall() | 获取结构集的所有记录 |
close() | 关闭当前游标 |
基本操作流程
例如:新增加一张表
import pymysql
connection = pymysql.connect(
host = 'localhost',
user = 'root',
password = '123456',
db = 'db_users',
charset = 'utf8',
cursorclass = pymysql.cursors.DictCursor)
sql = """
create table books(
id int not null auto_increment primary key,
name varchar(20),
category varchar(50),
price double(10,2) default '0',
publish_time date default null)engine=InnoDB default charset=utf8 collate utf8_general_ci;
"""
cursor = connection.cursor()
cursor.execute(sql)
cursor.close()
connection.close()
增删改查操作
- DML语句(增删改)
例如:向books中新增一个图书信息
import pymysql
connection = pymysql.connect(
host = 'localhost',
user = 'root',
password = '123456',
db = 'db_users',
charset = 'utf8',
cursorclass = pymysql.cursors.DictCursor)
sql = 'insert into books values(1,"零基础学python","Python","80.00","2018-04-11");'
cursor.execute(sql)
cursor.close()
connection.commit()
例:向books中一次插入多个数据
import pymysql
db = pymysql.connect('localhost','root','123456','db_users',charset='utf8')
cursor = db.cursor()
data = [("零基础学python(彩印版)","Python","100.00","2019-12-11"),
("Java入门大全","Java","78.88","2019-04-11"),
("零基础学PHP","PHP","56.00","2016-04-11"),
("PHP项目开发实战入门","PHP","180.00","2020-01-31")
]
try:
cursor.executemany("insert into books(name,category,price,publish_time) values(%s,%s,%s,%s);",data)
db.commit()
except:
db.rollback()
db.close()
- DQL(查询语句)
例:按照价格从低到高筛选出3条数据
import pymysql
db = pymysql.connect('localhost','root','123456','db_users',charset='utf8')
cursor = db.cursor()
sql = 'select * from books order by price asc;'
cursor.execute(sql)
data = cursor.fetchall()
for book in data:
print(f'图书:{book["name"]},价格:{book["price"]}') #格式化字符串
db.close()
ORM编程
对象关系映射是一种程序设计技术,用于实现面向对象编程语言里不同类型系统的数据之间的转换
数据库和对象的映像关系如下:
- 数据表—>类
- 数据行—>对象
- 字段—>对象的属性
例如以下是面向对象的方式执行sql语句:
sql = 'select * from books order by price;'
cursor.execute(sql)
data = cursor.fetchall()
改成ORM后则变成:
data = Book.query.all()
使用ORM后就不需要了解底层数据库的语言,直接与数据对象之间实现交互