Python使用MySQL
使用pymysql、sqlalchemy
1 pymysql的基本使用
使用pymysql模块,使Python可以连接控制MySQL数据库
1.1 连接数据库
import pymysql
conn = pymysql.connect(host='', user='', password='', database='', charset='utf8') # 依次输入主机地址,用户名,密码,连接数据库,显示中文
# 关闭连接
conn.close()
cursor.close()
1.2 表格数据查询
# 连接成功数据库
cursor = conn.cursor()
# 当括号内不填写参数时,返回结果以元组形式展现
# cursor=pymysql.cursors.DictCursor:返回以字典形式展现
sql_order = 'select * from table_name where data1=%s and data2=%s' # 填写相关MySQL语句进行查询
cursor.execute(sql_order, (data1, data2)) # 使用execute进行执行MySQL语句,并进行传参数
# 返回查询结果
result = cursor.fetchone() # 返回一条结果,再次执行会返回下一条
# result = cursor.fetchmany(n) # 返回n条结果
# result = cursor.fetchall() # 返回全部结果
# 当使用one和many时,可以使用以下方法改变游标位置
# cursor.scroll(n, mode='relative') n为改变位置,mode表示改变的方式:relative相对位置移动,absolute绝对位置移动
1.3 表格数据更新
# 连接成功数据库
cursor = conn.cursor()
sql = 'insert into table_name(data1,data2) values(%s,%s)'
cursor.execute(sql,('e', 'e1'))
# cursor.executemany(sql,[('e', 'e1'),('f','f1')])
# cursor.executemany用于在同时增加多个数据时使用
# cursor.lastrowid获取最后插入数据的id
# 更新数据
conn.commit()
1.4 更多使用
1.4.1 使用存储过程
# 连接成功数据库
cursor = conn.cursor()
cursor.callproc('p',(v1,v2))
result = cursor.fetchall
2 sqlalchemy
使用sqlalchemy模块,用Python语言直接控制数据库,不用自己写SQL代码。
sqlalchemy模块是通过类来生成SQL语句的,在后续使用中都是基于类来使用。
2.1 数据库的连接与生成
类的操作对象是表,在连接时是直接连接至某个库进行的操作,操作其他库需要重新连接。
2.1.1 连接与基本定义
# 导入
from sqlalchemy import Column, String, create_engine, Integer
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
# 定义对象:
class User(Base): # 继承Base的新类,一个类为一个表
# 表的名字:
__tablename__ = 'test1'
# 表的结构:
id = Column(Integer, primary_key=True) # Column(列的数据类型,限定条件)
name = Column(String(20))
# 初始化数据库连接:
def init_sql():
engine = create_engine('mysql+pymysql://root:password@localhost:3306/test?charset=utf8', max_overflow=5)
# 连接方式://连接的用户:密码@IP地址:连接端口(默认3306)/链接库?字符类型
# max_overflow:运行用户连接数据库的最大连接数,防止电脑无法承载过大访问
Base.metadata.create_all(engine)
- 数据类型:
参数 | 类型 | 使用 |
---|---|---|
String | 字符型 | String(n) |
Integer | 整型 | Integer |
Float | 浮点类型 | Float |
Double | 双精度浮点类型 | Double |
Boolean | 布尔类型 | Boolean |
DECIMAL | 定点类型 | DECIMAL(n,m) |
Enum | 枚举类型 | Enum(‘data1’, ‘data2’) |
DateTime | 存储时间 YYYY-MM-DD HH:MM:SS | DateTime |
Time | 存储时间 HH:MM:SS | Time |
Text | 存储长字符串 | Text |
LONGTEXT | 长文本类型 | LONGTEXT |
- 常用参数
参数 | 意义 | 使用 |
---|---|---|
default | 默认值 | default=n |
nullable | 是否可以为空 | nullable=True |
primary_key | 是否为主键 | primary_key=True |
unique | 是否唯一 | unique=True |
autoincrement | 是否自增 | autoincrement=True |
onupdate | 更新的时候执行的函数 | onupdate=datetime.now |
name | 该列列名 | name=‘col_name’ |
ForeignKey | 外键 | ForeignKey(‘other_table.col’) |
2.2 表格数据的更新
2.2.1 增加数据
增加数据就是生产类(表)的实际对象,一个对象就是一行数据
# 正常连接
# 导入
from sqlalchemy.orm import sessionmaker
# 创建并连接对象
Session = sessionmaker(bind=engine) # bind参数对应的是上面连接的目标
session_1 = Session() # 可以创建的连接对话为上面所写的最大连接数
# 添加数据
# 单行数据添加
add_data = User(id=1, name='name1') # 通过创建对象的方法填写数据
session_1.add(add_data) # 将填写完成的数据添加入对话中生产SQL语句
add_data = User(id=2, name='name2')
session_1.add(add_data)
# 多行数据添加
add_data = [User(id=4, name='name4'),
User(id=5, name='name5'),
User(id=6, name='name6'),
User(id=7, name='name7')]
session_1.add_all(add_data)
# 传输数据并关闭对话
session_1.commit() # 传输数据
session_1.close() # 完成操作关闭对话
2.2.2 修改数据
# 正常连接
# 导入
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session_1 = Session()
session_1.query(User).filter_by(id=1).update({'name': "user1"})
# 对应表格对象 修改行条件 修改信息
session_1.commit()
session_1.close()
2.2.3 删除数据
# 正常连接
# 导入
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session_1 = Session()
session_1.query(User).filter(User.name == "user1").delete()
# 对应表格对象 删除行条件
session_1.commit()
session_1.close()
2.3 数据查询
查询的筛选条件使用的是两个函数,filter与filter_by:
filter:
- 使用: 类名.属性名
- 使用范围:所有比较运算符,and_(),or_()
filter_by:- 使用:属性名
- 使用范围:=
2.3.1 直接查询
# 生产SQL语句
session_1.query(User) # 生产查询的sql语句SELECT user.id AS user_id, user.name AS user_name FROM user
# 获取所有对象,需要用:对象.属性获取具体数据
aaa = session_1.query(User).all()
id1 = aaa[0].id # 得到第一个id,可以用for获取所有数据
# 直接按列获取数据
session_1.query(User.id,User.name).all()
2.3.2 条件查询
# 字符数据
session_1.query(User.id,User.name).filter(User.name.like("name%")).all()
# 比较运算符
session_1.query(User.id,User.name).filter(User.id>3).all()
# 逻辑运算符
from sqlalchemy import and_, or_
session_1.query(User.id,User.name).filter(and_(User.id>3, User.id<5)).all()
# 函数
session_1.query(User.id,User.name).filter(User.id.between(1,5)).all() # 取一定范围内的值,闭区间
session_1.query(User.id,User.name).filter(User.id.in(1,5)).all() # 取固定值
session_1.query(User.id,User.name).filter(~User.id.in(1,5)).all() # 不取固定值
2.3.3 显示限制
# 切片显示
session_1.query(User.id,User.name)[1:2] # 左闭右开
# 显示顺序
session_1.query(User).order_by(User.id.desc()).all() # desc()大到小, asc小到大
# 可以同时放多个数据,先按第一个排线再按后面排序
from sqlalchemy.sql import func
session_1.query(func.max(User.id)).group_by(User.id).all() # max、min、sum、count
# 连表显示
session_1.query(User).join(User_2) # 默认关联主键,默认INNER JOIN
session_1.query(User).join(User_2, isouter=True) # LEFT JOIN
session_1.query(User, User_2).filter(User.id==User_2.id)
# 组合(上下连表)
session_1.query(User.id,User.name).union(session_1.query(User.id,User.name)).all # union去重,union_all不去重
# 子查询,实现在SQL语句中的(select * from table_name) as B
session_1.query(User.id,User.name).subquery() # 如此才可以作为条件放入查询
2.4 relationship
import sqlalchemy
from sqlalchemy import Column, String, create_engine, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker
class User_score(Base): # 继承Base的新类,一个类为一个表
# 表的名字:
__tablename__ = 'User_score'
# 表的结构:
id = Column(Integer, primary_key=True) # Column(列的数据类型,限定条件)
score = Column(Integer)
class User(Base): # 继承Base的新类,一个类为一个表
# 表的名字:
__tablename__ = 'User'
# 表的结构:
id = Column(Integer, primary_key=True) # Column(列的数据类型,限定条件)
name = Column(String(20))
score = Column(Integer, ForeignKey('User_score.id')) # 连接外键
score_re = relationship('User_score', backref="score_back") # 根据外键自动连表
Session = sessionmaker(bind=engine)
session_1 = Session()
add_data = [User(id=1, name='name1', score=2),
User(id=2, name='name2', score=1),
User(id=3, name='name3', score=3),
User(id=4, name='name4', score=1)]
session_1.add_all(add_data)
add_data = [User_score(id=1, score='a'),
User_score(id=2, score='b'),
User_score(id=3, score='c')]
session_1.add_all(add_data)
session_1.commit()
session_1.close()
# 使用
Session = sessionmaker(bind=engine)
session_1 = Session()
# 正向查询
user_data = session_1.query(User).all()
# 整张表有多行数据,需要一行行展示,所以使用for
for i in user_data:
print(i.score_re.id, i.score_re.score)
# 反向查询
user_data = session_1.query(User_score).all()
# 由于在被连接的附表中单个数值可能对应主表的多个数值,所以会以列表形式展出,因此要多一个for
for i in user_data:
for j in i.score_back:
print(j.id)
session_1.commit()
session_1.close()