本篇对于Python操作MySQL主要使用两种方式：
原生模块 pymsql
ORM框架 SQLAchemy
参考资料：http://www.runoob.com/python/python-mysql.html
一、pymsql
pymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同。
下载安装
pip3 install pymysql -i https://pypi.douban.com/simple
使用操作
1、执行SQL
写sql语句的时候，如果在操作前sql语句字符串格式化，占位符%s要引号' '引起来，如果执行操作，以元组或者列表的形式，在提交的时候传递参数，则占位符%s就不能引号引起来。
import pymysql
user= input("username:")
pwd= input("password:")
# 创建连接
conn= pymysql.connect(host="localhost",user='root',password='',database="db666")
# 出现编码问题conn = pymysql.connect(host="localhost",user='root',password='',database="db666",charset="utf8")
# 创建游标
cursor=conn.cursor()
sql= "select * from userinfo where username='%s' and password='%s'" %(user,pwd,)
#select * from userinfo where username='uu' or 1=1 -- ' and password='%s' #sql注入
# 执行SQL，并返回收影响行数cursor.execute(sql)
# 获取第一行数据
result=cursor.fetchone()
cursor.close()
conn.close()ifresult:
print('登录成功')else:
print('登录失败')
注意：存在中文的时候，连接需要添加charset='utf8'，否则中文显示乱码。
获取查询数据
结论：excute执行SQL语句的时候，必须使用参数化的方式，否则必然产生SQL注入漏洞。
import pymysql
user = input("username:")
pwd = input("password:")
conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor =conn.cursor()
sql = "select * from userinfo where username=%s and password=%s"cursor.execute(sql,(user,pwd)) #推荐这种方法，防止sql注入
# cursor.execute(sql,[user,pwd])
# cursor.execute(sql,{'u':user,'p':pwd})
#查询一行
result =cursor.fetchone()
#查询全部result = cursor.fetchall()
#查询四行
result = cursor.fetchmany(4)cursor.close()
conn.close()
ifresult:
print('登录成功')
else:
print('登录失败')
注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：
cursor.scroll(1,mode='relative') # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动
插入多行数据
conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor=conn.cursor()
sql= "insert into userinfo(username,password) values(%s,%s)"# 插入多行数据
r= cursor.executemany(sql,[('egon','sb'),('laoyao','BS')])
#******# 提交，不然无法保存新建或者修改的数据
conn.commit()cursor.close()
conn.close()
报错信息：
Error: unable to insertdb!insert intoclass(title) values(%s)
not all arguments converted duringstringformatting
sql= "insert into class(title) values(%s)"li= [('1期',), ('2期',)] # 只插入一列时这么写可以解决，sql语句也不能%s也不能‘’引起来
cursor.executemany(sql,li)
#sql = "insert into userinfo(name,email,gender_id) values(%s,%s,%s)"
#db.insertmanydb(sql, [('egon', 'sb',1), ('laoyao', 'BS',1)])
fetch数据类型
关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：
# 查
conn= pymysql.connect(host="localhost",user='root',password='',database="db666")
# 游标设置为字典类型cursor= conn.cursor(cursor=pymysql.cursors.DictCursor)
sql= "select * from userinfo"cursor.execute(sql)
cursor.scroll(1,mode='relative') # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动
# 查询一行
result=cursor.fetchone()
print(result)
# 查询全部
result=cursor.fetchall()
print(result)
# 查询4行
result= cursor.fetchmany(4)
print(result)
cursor.close()
conn.close()
插入单条数据，获取新创建数据自增ID
# 新插入数据的自增ID: cursor.lastrowid
import pymysql
conn= pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor=conn.cursor()
sql= "insert into userinfo(username,password) values('asdfasdf','123123')"cursor.execute(sql)
conn.commit()
# 新插入数据的自增ID，插入多条时也是拿到最后一条的ID
print(cursor.lastrowid)
cursor.close()
conn.close()
sql语句插入中内容同时包含单引号和双引号的解决办法
在python中调用MySQLdb模块插入数据信息，假设待输入信息data为：
Hello'World"!
其中同时包含了单引号和双引号
一般插入语句为
sql = "insert into tb (my_str) values('%s')" %(data)
cursor.execute(sql)
其中values('%s')中的%s外面也要有引号，这个引号与data中的引号匹配导致了内容错误
解决办法一: MySQLdb.escape_string()
在MySQLdb模块中自带针对mysql的转义函数escape_string()，直接调用即可
sql = "insert into tb (my_str) values('%s')" %(MySQLdb.escape_string(data))
cursor.execute(sql)
解决办法二：转义字符
将data变为下面的形式,再插入数据库就正确了
Hello\'World\"!
具体在python中的转义函数如下：
def transferContent(self, content):if content isNone:returnNoneelse:string = ""
for c incontent:if c == '"':string += '\\\"'elif c== "'":string += "\\\'"elif c== "\\":string += "\\\\"
else:string +=creturn string
要加三个\，这是因为\\会在函数中转义为\，\'会转义成'，两者合起来才能在字符串中留下 \'，然后sql读取后才能识别这是转义
注意，\本身也需要转义，否则如果原本字符串中为\',只转义'就会变成\\\\',结果是\\\\相互抵消，只剩下'
在python中，下面两种写法是一样的
a="'"a="\'"
二、SQLAchemy
SQLAlchemy是Python编程语言下的一款ORM(关系对象映射)框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。
Object Relational Mapper (ORM) 关系对象映射(就是自己写的类)
Schema/Type 、SQL Expression Lanuage将类转换成SQL语句
DBAPI就是(pymysql、mysqlDB、SQL、oracle等中间件接口)，通过这些东西连接不同的数据库
Engine(引擎启动)，然后去connection pooling连接池看建立几个连接，然后去Dialect拿配置(如mysql+pymysql)，通过中间件接口连接对应数据库
安装：
pip3 install SQLAlchemy
面向对象：
classFoo:
def __init__(self,name):
self.name=name
def show(self):
print(self.name)
def __call__(self):
pass
def __getitem__(self,key):
pass
def __setitem__(self,key,value):
pass
def __delitem__(self,key):
pass
obj1= Foo('eric')
obj1() 执行__call__方法
obj1['k'] 执行__getitem__方法
obj1['k'] = 123执行__setitem__方法
del obj[k] 执行__delitem__方法
obj.__dict__对象的全部属性
特殊方法：
1. 提取共性
2. 分类
3. 模板“约束”
面向对象： 数据和逻辑(属性和行为)组合在一起
函数编程：数据和逻辑分离
4. 当一类函数公用同样参数时候，可以转变成类进行 - 分类
类：对应表
对象：对应行
SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如
MySQL-Python
mysql+mysqldb://:@[:]/
pymysql
mysql+pymysql://:@/[?]
MySQL-Connector
mysql+mysqlconnector://:@[:]/
cx_Oracle
oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html
一、内部处理
使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。
#!/usr/bin/env python
#-*- coding:utf-8 -*-
fromsqlalchemy import create_engine
engine= create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)
# 执行SQL
# cur=engine.execute(
#"INSERT INTO hosts (host, color_id) VALUES ('1.1.1.22', 3)"# )
# 新插入行自增ID
# cur.lastrowid
# 执行SQL
# cur=engine.execute(
#"INSERT INTO hosts (host, color_id) VALUES(%s, %s)",[('1.1.1.22', 3),('1.1.1.221', 3),]
# )
# 执行SQL
# cur=engine.execute(
#"INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)",
# host='1.1.1.99', color_id=3# )
# 执行SQL
# cur= engine.execute('select * from hosts')
# 获取第一行数据
# cur.fetchone()
# 获取第n行数据
# cur.fetchmany(3)
# 获取所有数据
# cur.fetchall()
View Code
二、ORM功能使用
对象-关系映射(OBJECT/RELATIONALMAPPING，简称ORM)，是随着面向对象的软件开发方法发展而产生的。用来把对象模型表示的对象映射到基于S Q L 的关系模型数据库结构中去。这样，我们在具体的操作实体对象的时候，就不需要再去和复杂的 SQ L 语句打交道，只需简单的操作实体对象的属性和方法。O R M 技术是在对象和关系之间提供了一条桥梁，前台的对象型数据和数据库中的关系型的数据通过这个桥梁来相互转化。
使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。
1、创建表
#!/usr/bin/env python
#-*- coding:utf-8 -*-
fromsqlalchemy.ext.declarative import declarative_basefromsqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Indexfromsqlalchemy.orm import sessionmaker, relationshipfromsqlalchemy import create_engine
engine= create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)
Base=declarative_base()# 创建单表classUserType(Base):
__tablename__= 'usertype'id= Column(Integer, primary_key=True, autoincrement=True)
title= Column(String(32), nullable=True, index=True)classUsers(Base):
__tablename__= 'users'id= Column(Integer, primary_key=True, autoincrement=True)
name= Column(String(32), nullable=True, index=True)
email= Column(String(16), unique=True)
user_type_id= Column(Integer,ForeignKey("usertype.id"))
user_type= relationship("UserType",backref="xxoo")
#前面的给Users使用 后面的给UserType使用
# relationship：和外键绑定，在一起使用，根据外键连表
# 正向操作 有外键.relationship是正向操作
# 反向操作
Base.metadata.create_all(engine) #找到继承Base的类，写到数据库，创建表
Base.metadata.drop_all(engine)
# def init_db():
# Base.metadata.create_all(engine)
#
# def drop_db():
# Base.metadata.drop_all(engine)
#
# init_db()
2、操作表
fromsqlalchemy.ext.declarative import declarative_basefromsqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Indexfromsqlalchemy.orm import sessionmaker, relationshipfromsqlalchemy import create_engine
Base=declarative_base()
# 创建单表classUserType(Base):
__tablename__= 'usertype'id= Column(Integer, primary_key=True, autoincrement=True)
title= Column(String(32), nullable=True, index=True)classUsers(Base):
__tablename__= 'users'id= Column(Integer, primary_key=True, autoincrement=True)
name= Column(String(32), nullable=True, index=True)
email= Column(String(16), unique=True)
user_type_id= Column(Integer,ForeignKey("usertype.id"))
user_type= relationship("UserType",backref="xxoo")
#前面的给Users使用 后面的给UserType使用
# relationship：和外键绑定，在一起使用，根据外键连表
# 正向操作 有外键.relationship是正向操作
# 反向操作
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/db4?charset=utf8", max_overflow=5)
# max_overflow=5 连接数据库的最大连接数
Session = sessionmaker(bind=engine)
session = Session() #在链接池拿一个链接
# 类----》表
# 对象---》行
# 添加单个
obj = UserType(title = '普通用户') session.add(obj)
#添加多个数据
objs = [UserType(title = '超级用户'), UserType(title = '白金用户'), UserType(title = '黑金用户'), ] session.add_all(objs)
# 查询
print(session.query(UserType))
#sql语句
user_type_list=session.query(UserType).all() print(type(user_type_list))
#是一个列表
print((user_type_list)[0])
#是类一个对象，对象代表行
for row in user_type_list:
print(row.id,row.title)
#相当于where条件语句,query(UserType.id,UserType.name)确定取哪一列
user_type_list=session.query(UserType.id,UserType.title).filter(UserType.id>2)
for row in user_type_list:
print(row.id,row.title)
#删除，先查再删除
session.query(UserType.id,UserType.title).filter(UserType.id>2).delete()
# 修改，先查再修改
session.query(UserType.id,UserType.title).filter(UserType.id>0).update({"title","黑金"}) #整列都改变
session.query(UserType.id,UserType.title).filter(UserType.id>0).update({UserType.title:UserType.title+"x"},synchronize_session=False) #改变单独的每行，字符串拼接
session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate") #改变单独的每行，计算
# 1.select * from b where id in (select id from tb2)
# 2.select * from (select * from tb) as B
q1 = session.query(UserType).filter(UserType.id>0).subquery() #子查询要加subquery()
result = session.query(q1).all()
print(result)
# select id ,(select id from wherer id = x) from xxx;
result = session.query(UserType.id,session.query(Users).subquery()).all()
# 3.select id ,(slect * from Users) from UserType
result = session.query(UserType.id,session.query(Users).filter(Users.id==UserType.id).as_scalar())
#as_scalar()，将表变成一个元素放置，避免笛卡尔机
print(result)
# 问题一：获取用户信息以及与其关联的用户类型名称 一对一
# 连表查询
use_list = session.query(Users,UserType).join(UserType, isouter=True).all() #结果是类元组
use_list = session.query(Users.name,UserType.title).join(UserType, isouter=True).all() #结果是行结果元组
for row in use_list: print(row[0],row[1],row.name,row.title)
use_list = session.query(Users)
for row in use_list:
print(row.name,row.id,row.user_type.title) #relation拿到相关联的表的某行正向操作
# 问题二：获取用户类型名称对应的用户 一对多
use_list = session.query(UserType) for row in use_list:
print(row.id,row.title,session.query(Users).filter(Users.user_type_id == row.id).all())
use_list = session.query(UserType)
for row in use_list:
print(row.id,row.title,row.xxoo) #relation拿到相关联的表的某行，反向操作
session.commit()
session.close()
obj = Users(name="alex0", extra='sb')
session.add(obj)
session.add_all([
Users(name="alex1", extra='sb'),
Users(name="alex2", extra='sb'),
])
session.commit()
增
session.query(Users).filter(Users.id > 2).delete()
session.commit()
删
session.query(Users).filter(Users.id > 2).update({"name" : "099"})
session.query(Users).filter(Users.id> 2).update({Users.name: Users.name + "099"}, synchronize_session=False)
session.query(Users).filter(Users.id> 2).update({"num": Users.num + 1}, synchronize_session="evaluate")
session.commit()
改
ret =session.query(Users).all()
ret=session.query(Users.name, Users.extra).all()
ret= session.query(Users).filter_by(name='alex').all()
ret= session.query(Users).filter_by(name='alex').first()
ret= session.query(Users).filter(text("id<:value and name=':name")).params(value=224,'>
ret= session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()
查
# 条件
ret= session.query(Users).filter_by(name='alex').all() #filter_by传入的是参数，filter_by会转换成filter语句
ret= session.query(Users).filter(Users.id > 1, Users.name == 'eric').all() #and
ret= session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()
ret= session.query(Users).filter(Users.id.in_([1,3,4])).all() #in
ret= session.query(Users).filter(~Users.id.in_([1,3,4])).all() #not in
ret= session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()fromsqlalchemy import and_, or_
ret= session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all() #and
ret= session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all() #or
ret=session.query(Users).filter(
or_(
Users.id< 2,
and_(Users.name== 'eric', Users.id > 3),
Users.extra!= "")).all()
# 通配符
ret= session.query(Users).filter(Users.name.like('e%')).all()
ret= session.query(Users).filter(~Users.name.like('e%')).all()
# 限制，相当于
liniteret= session.query(Users)[1:2]
# 排序
ret=session.query(Users).order_by(Users.name.desc()).all()
ret=session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()
# 分组fromsqlalchemy.sql import func #导入聚会函数
ret=session.query(Users).group_by(Users.extra).all()
ret=session.query(
func.max(Users.id),
func.sum(Users.id),
func.min(Users.id)).group_by(Users.name).all()
ret=session.query(
func.max(Users.id),
func.sum(Users.id),
func.min(Users.id)).group_by(Users.name).having(func.min(Users.id)>2).all()
# 连表
ret= session.query(Users, Favor).filter(Users.id ==Favor.nid).all() 查询两张表，笛卡尔机
ret=session.query(Person).join(Favor).all() #相当于inner join
ret= session.query(Person).join(Favor, isouter=True).all() #相当于left join,没有right join
# 组合
q1= session.query(Users.name).filter(Users.id > 2)
q2= session.query(Favor.caption).filter(Favor.nid < 2)
ret=q1.union(q2).all()
q1= session.query(Users.name).filter(Users.id > 2)
q2= session.query(Favor.caption).filter(Favor.nid < 2)
ret= q1.union_all(q2).all()
扫一扫
3696
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
