s4 mysql的py操作 笔记
MySQL三
____oldboy fullstack4
Python开发【第十九篇】：Python操作MySQL
https://www.cnblogs.com/wupeiqi/articles/5713330.html
- pymysql模块：
pip3 install pymysql -i https://pypi.douban.com/simple
Python模块：对数据库进行操作（SQL语句）
python2: mysqldb,pymysql
操作步骤：
- 连接、关闭（游标）
- execute() # SQL注入
- 增删改： conn.commit()
- fetchone fetchall
- 受影响行数
- 获取插入数据自增ID
示例一：
import pymysql
user = input('username:')
pwd = input('password:')
conn = pymysql.connect(host='localhost',user='root',password='',database='db66')
cursor =conn.cursor() # 游标
# sql ='select * from userinfo where username = "%s" and password ="%s"' %(user,pwd,) # sql注入，不推荐！！！！！！
# sql ='select * from userinfo where username = %s and password =%s'
sql ='select * from userinfo where username = %(u)s and password =%(p)s'
# cursor.execute(sql) # sql注入，不推荐！！！！！！
# cursor.execute(sql,user,pwd)
# cursor.execute(sql,[user,pwd])
cursor.execute(sql,{'u':user,'p':pwd})
result = cursor.fetchone()
cursor.close()
conn.close()
if result:
print('sucess!')
else:
print('wrong!!!')
# alex'--
# uuuu' or 1=1 --
# sql 注入
示例二：
import pymysql
# 增、删、改
# conn = pymysql.connect(host='localhost',user='root',password='',database='db66')
# cursor =conn.cursor()
# sql ="INSERT INTO userinfo(username,password) VALUES ('root','123123')"
# r = cursor.execute(sql) # r:受影响行数
# conn.commit() # 增、删、改 必须！！！
# cursor.close()
# conn.close()
# # user = 'eric'
# # pwd = '123123'
# conn = pymysql.connect(host='localhost',user='root',password='',database='db66')
# cursor =conn.cursor()
# # sql ="INSERT INTO userinfo(username,password) VALUES (%s,%s)"
# # cursor.execute(sql,(user,pwd,))
# sql ="INSERT INTO userinfo(username,password) VALUES (%s,%s)"
# r = cursor.executemany(sql,[('egon','sb'),('laoyao','BS')])
# # r:受影响行数,executemany只适用于insert
# conn.commit() # 增、删、改 必须！！！
# cursor.close()
# conn.close()
# 查
# conn = pymysql.connect(host='localhost',user='root',password='',database='db66')
# cursor =conn.cursor(cursor=pymysql.cursors.DictCursor)
# sql ='select * from userinfo'
# cursor.execute(sql)
# # cursor.ecroll(1,mode='relative') #相对当前位置移动
# # cursor.ecroll(2,mode='absolute') #相对绝对位置移动
# # result = cursor.fetchone()
# # result = cursor.fetchmany(4) #一般不用
# result = cursor.fetchall() # 通过sql语句限制数量
# print(result) # 默认得到元组，里面小元组；通过cursor =conn.cursor（参数修改）
# cursor.close()
# conn.close()
# 新插入数据的自增ID
conn = pymysql.connect(host='localhost',user='root',password='',database='db66')
cursor =conn.cursor()
sql ="INSERT INTO userinfo(username,password) VALUES ('eeeee','123123')"
cursor.execute(sql)
conn.commit()
print(cursor.lastrowid) # 新增数据ID
cursor.close()
conn.close()
- 面向对象回顾：
1. 函数编程：数据和逻辑分离
a = 123
b = 456
c = 789
def exc3(proc_name):
callproc(xxx)
return xxx
def exc4(proc_name):
callproc(xxx)
return xxx
2. 面向对象：数据和逻辑（属性和行为）组合在一起
class SqlHelper:
def __init__(self):
self.host = ''
self.port =''
self.db =''
self.charset=''
def exc1(self,SQL):
conn(self.host,)
execute("inser")
return xx
def exc2(self,proc_name):
callproc(xxx)
return xxx
一类事物共同具有：属性和行为
class Person:
def __init__(self,name):
self.name = name
def speak(self):
pass
1. 提取共性
2. 进行分类
3. 模板“约束”
4. 当一类函数公用同样参数时，可以转变成类进行分类
3. 分类示例：
类 = 表，对象=行
class Userinfo:
def __init__(self,id,name):
"""
‘约束’每个对象中只有两个字段，即：每个行数据都有id和name列
"""
self.id = id
self.name= name
def add(self,name):
pass
...
# row1 = UserInfo(1,'alex') # 第一行
# row2 = UserInfo(2,'alex') # 第二行
# UserInfo.add('eric)
4. 特殊方法：
class Foo:
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 # python特有
obj1['k'] ==> getitem
obj1['k'] = 123 ==> setitem
del obj[k] ==> delitem
obj.__dict__
- ORM框架（关系、对象映射）
- SQLAlchemy
# pip3 install SQLAlchemy -i https://pypi.douban.com/simple
# 默认没有 DB first
- 作用：
- 提供简单的规则
- 自动转换成SQL语句
- 分类：
- DB first: 手动创建数据库以及表，ORM框架自动生成类
- code first： 手动创建类、和数据库，ORM框架生成表
- 功能:
- 创建数据库表
- 连接数据库
# SQLAlchemy不接数据库，而是通过DBAPI：pymyql,mysqldb..
# 使用前安装DBAPI
- 类转换SQL语句
- 操作数据行
增
删
改
查
- 便利的功能Relationship
- 代码：
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index,CHAR,VARCHAR
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
# engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/db62?charset=utf8", max_overflow=5)
# max-voerflow:最大连接池
Base =declarative_base()
# 创建表单
class UserType(Base):
__tablename__ ='usertype'
id = Column(Integer,primary_key=True,autoincrement=True)
title = Column(String(32),nullable=True,index=True)
"""
1 白金
2 黑金
obj.xx ==> [obj,obj...]
"""
class Users(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')
# __table_args__ = (
# UniqueConstraint('id','name',name='uix_id_name'),
# Index('ixx','name','email',),
# )
"""
1 方少伟 1
2 成套 1
3 小白 2
# 正向
ut = relationship（backref='xx'）
obj.ut ==> 1 白金
"""
def init_db():
Base.metadata.create_all(engine)
def drop_db():
Base.metadata.drop_all(engine)
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/db62?charset=utf8", max_overflow=5)
Session =sessionmaker(bind=engine)
session = Session()
# 类 -> 表，对象 -> 行
# -------- 增加 --------
# obj1 = UserType(title='普通用户')
# session.add(obj1)
# 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)) # <class 'list'>
# print(type(user_type_list[0])) # <class '__main__.UserType'>
# print(user_type_list[0]) # <__main__.UserType object at 0x000000000413D668>
# for row in user_type_list:
# print(row.id,row.title)
# select xxx UserType where ..
# user_type_list =session.query(UserType).filter(UserType.id > 2)
# user_type_list =session.query(UserType.id,UserType.title).filter(UserType.id > 2)
# for row in user_type_list:
# print(row.id,row.title)
# 分组、排序、连表、通配符、子查询、limit、union、where、原生SQL
# 条件
# ret = session.query(Users).filter_by(name='alex').all() # filter_by传字典，filter传表达式
# ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
# 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()
# ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
# ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()
# from sqlalchemy import and_, or_
# ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
# ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
# 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()
# ret = session.query(Users).filter(~Users.name.like('e%')).all()
# 限制
ret = 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()
# # 分组
# from sqlalchemy.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)
# ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()
#
# ret = session.query(Person).join(Favor).all()
# 默认找到ForeignKey关联，相当于 inner join
# ret = session.query(Person).join(Favor, isouter=True).all()
# left 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()
# 子查询
# 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()
# result = session.query(q1).all()
# print(result)
# 3
# select
# id ,
# (select * from users where users.user_type_id=usertype.id)
# from usertype;
# session.query(UserType,session.query(Users).filter(Users.id == 1).subquery()) # 笛卡尔积
# session.query(UserType,Users) # 笛卡尔积
# result = session.query(UserType.id,session.query(Users).as_scalar())
# result = session.query(UserType.id,session.query(Users).filter(Users.user_type_id==UserType.id).as_scalar())
# print(result)
# 问题1. 获取用户信息以及与其关联的用户类型名称(FK,Relationship=>正向操作)
# user_list = session.query(Users,UserType).join(UserType,isouter=True)
# print(user_list)
# for row in user_list:
# print(row[0].id,row[0].name,row[0].email,row[0].user_type_id,row[1].title)
# user_list = session.query(Users.name,UserType.title).join(UserType,isouter=True).all()
# for row in user_list:
# print(row[0],row[1],row.name,row.title)
# user_list = session.query(Users)
# for row in user_list:
# print(row.name,row.id,row.user_type.title)
# 问题2. 获取用户类型
# type_list = session.query(UserType)
# for row in type_list:
# print(row.id,row.title,session.query(Users).filter(Users.user_type_id == row.id).all())
# type_list = session.query(UserType)
# for row in type_list:
# print(row.id,row.title,row.xxoo)
# -------- 删除 --------
# session.query(UserType.id,UserType.title).filter(UserType.id > 2).delete()
# -------- 修改 --------
# usession.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(UserType.id,UserType.title).filter(UserType.id > 0).update(
# {'num':Users.num +1},
# synchronize_session='evaluate') # 数字
session.commit()
session.close()
