Python操作Mysql
Python操作MySQL主要使用的两种方式
- 原生模块 pymysql
- ORM框架 SQLAlchemy
1. pymysql
-
pymysql是python中操作MySQL的模块
- python2.x中使用MySQLdb, python3.x中使用pymysql，两者使用几乎相同。
-
安装
pip3 install pymysql -i https://pypi.douban.com/simple -
使用
- 连接
- 创建游标
- 通过游标执行SQL语句
- 关闭游标
- 关闭连接
-
用户登入
import pymysql username = input("username:") password = input("password:") # 创建连接 conn = pymysql.connect(host='localhost', port=3306, user='root', password='123', db='scott', charset='utf8') # 获取游标 cursor = conn.cursor() # sql = "SELECT * FROM userinfo WHERE username='%s' AND password='%s'"%(username, password) # SELECT * FROM userinfo # WHERE username='ret' -- ' AND password='' SQL 注入 # SELECT * FROM userinfo # WHERE username='' or 1=1 -- ' AND password='' SQL 注入 # print(sql) # sql = "SELECT * FROM userinfo WHERE username=%s AND password=%s" sql = "SELECT * FROM userinfo WHERE username=%(u)s AND password=%(p)s" # 执行SQL语句 # cursor.execute(sql, [username, password]) cursor.execute(sql, {'u':username, 'p':password}) # 获取查询结果 result = cursor.fetchone() # 关闭游标 cursor.close() # 关闭连接 conn.close() if result: print("login success!\n") else: print("login fail!\n") -
增删改查
import pymysql ''' # 创建连接 conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="123", db="scott") # 获取游标 cursor = conn.cursor() sql = "INSERT INTO userinfo VALUES('root', '123');" # 执行SQL语句 effect_row = cursor.execute(sql) # 返回影响的行数 print("%d rows effect.\n"%(effect_row)) # 对于增删改等操作需要提交 conn.commit() # 关闭游标 cursor.close() # 关闭连接 conn.close() ''' ''' # user = input("username:") # passwd = input("passwd:") # 创建连接 conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="123", db="scott") # 获取游标 cursor = conn.cursor() sql = "INSERT INTO userinfo VALUES(%s, %s);" # 执行SQL语句 #effect_row = cursor.execute(sql, (user, passwd)) # 返回影响的行数 effect_row = cursor.executemany(sql, [('r1', '123'), ('r2', '123')]) print("%d rows effect.\n"%(effect_row)) # 对于增删改等操作需要提交 conn.commit() # 关闭游标 cursor.close() # 关闭连接 conn.close() ''' ''' # 查 # 创建连接 conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="123", db="scott") # 获取游标 cursor = conn.cursor() # 默认返回结果为元组形式 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 设置返回结果为字典形式 sql = "SELECT * FROM userinfo;" # 执行SQL语句 #effect_row = cursor.execute(sql, (user, passwd)) # 返回影响的行数 effect_row = cursor.execute(sql) print("%d rows effect.\n"%(effect_row)) result = cursor.fetchone() # 获取一条结果 print(result) result = cursor.fetchone() # 获取结果后 游标将自动向下移动 print(result) cursor.scroll(-2, mode="relative") # 滚动游标 相对 result = cursor.fetchmany(2) # 获取多条结果 print(result) cursor.scroll(0, mode="absolute")# 滚动游标 绝对 result = cursor.fetchall() # 获取所有查询结果 print(result) # 对于增删改等操作需要提交 conn.commit() # 关闭游标 cursor.close() # 关闭连接 conn.close() ''' # 新插入的自增id # 创建连接 conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="123", db="scott") # 获取游标 cursor = conn.cursor() # 默认返回结果为元组形式 sql = "INSERT INTO userinfo VALUES('test', 'test');" # 执行SQL语句 cursor.execute(sql) # 对于增删改等操作需要提交 conn.commit() # 新插入的自增id print(cursor.lastrowid) # 关闭游标 cursor.close() # 关闭连接 conn.close()
2.ORM框架:SQLAlchemy
-
SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。
-
ORM框架类型(Django ORM两个都支持, SQLAlchemy默认只支持code first)
- DB first 手动创建数据库以及表 -> ORM框架 -> 自动生成类
- code first 手动创建数据库和类 -> ORM框架 -> 自动生成表
-
安装SQLAlchemy
pip3 install sqlalchemy -i https://pypi.douban.com/simple -
SQLAlchemy本身无法操作数据库，其必须依赖pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：
MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> 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执行SQL语句。
-
根据类生成表
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, Index from sqlalchemy import create_engine Base = declarative_base() class UserType(Base): __tablename__ = "usertype" id = Column(Integer, primary_key=True, autoincrement=True) # 设置主键,自增 title =Column(String(32), nullable=False) # 设置不能为空 __table_args__ = ( UniqueConstraint('id', 'title', name='uix_id_title'), # 创建联合唯一约束 # 设置外键ForeignKeyConstraint(['other_id'], ['othertable.other_id']) Index("ix_title", title) # 设置title为索引 ) class Users(Base): __tablename__ = "users" id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(32), nullable=False) email = Column(String(64), unique=True, nullable=False) # 设置唯一 不能为空 user_type = Column(Integer, ForeignKey("usertype.id")) # 设置外键 # 将继承了Base的类 生成数据库表 def create_db(): engine = create_engine("mysql+pymysql://root:123@localhost:3306/test2?charset=utf8", max_overflow=5) Base.metadata.create_all(engine) # 将继承了Base的类生成的表 从数据库中删除 def drop_db(): engine = create_engine("mysql+pymysql://root:123@localhost:3306/test2?charset=utf8", max_overflow=5) Base.metadata.drop_all(engine) create_db() -
增删改查
from sqlalchemy import create_engine from sqlalchemy import Integer, String, Index, Column from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker Base = declarative_base() class UserType(Base): __tablename__ = "usertype" id = Column(Integer, primary_key=True, autoincrement=True) # 设置主键,自增 title =Column(String(32), nullable=False) # 设置不能为空 __table_args__ = ( Index("ix_title", title), # 设置title为索引 ) engine = create_engine("mysql+pymysql://root:123@localhost:3306/test2?charset=utf8", max_overflow=5) Session = sessionmaker(bind=engine) session = Session() # 单个增加 # u1 = UserType(title="普通用户") # session.add(u1) ''' objs = [ UserType(title="超级用户"), UserType(title="白金用户"), UserType(title="黑金用户"), ] # 多个增加 session.add_all(objs) ''' # 查找 # print(session.query(UserType)) # 查看生成的SQL语句 ''' ret = session.query(UserType).all() for e in ret: print(e.id, e.title) ''' ret = session.query(UserType.title).filter(UserType.id>2) for e in ret: print(e.title) # 删除 # session.query(UserType).filter(UserType.id>2).delete() # 更新 session.query(UserType).update({"title":"黑金"}) session.query(UserType).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() -
其他
-
条件
ret = session.query(Users).filter_by(name='alex').all() 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)[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).filter(Users.id == Favor.nid).all() ret = session.query(Person).join(Favor).all() ret = session.query(Person).join(Favor, isouter=True).all() -
组合
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() -
子查询放置在FROM之后
SELECT * FROM (SELECT * FROM usertype WHERE id > 0) AS b;q1 = session.query(UserType).filter(UserType.id > 0).subquery() result = session.query(q1).all() -
子查询放置在SELECT中
SELECT id, (SELECT * FROM users WHERE users.user_type_id.id=usertype.id) FROM usertype;session.query(UserType.id, session.query(Users).filter(UserType.id==Users.user_type_id).scalar()).all() -
relationship
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, Index from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker, relationship Base = declarative_base() class UserType(Base): __tablename__ = "usertype" id = Column(Integer, primary_key=True, autoincrement=True) # 设置主键,自增 title =Column(String(32), nullable=False) # 设置不能为空 __table_args__ = ( Index("ix_title", title), # 设置title为索引 ) class Users(Base): __tablename__ = "users" id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(32), nullable=False) email = Column(String(64), unique=True, nullable=False) # 设置唯一 不能为空 user_type_id = Column(Integer, ForeignKey("usertype.id")) # 设置外键 user_type = relationship("UserType", backref="xxoo") # 跟UserType生成关系 backref给UserType用的 engine = create_engine("mysql+pymysql://root:123@localhost:3306/test2?charset=utf8") Session = sessionmaker(bind=engine) session = Session() # 获取用户信息以及与其关联的用户类型名称(正向操作) ''' user_list = session.query(Users, UserType).join(UserType, isouter=True) for row in user_list: print(row[0].id, row[0].name, row[0].email, row[1].id, row[1].title) ''' ''' # user_list = session.query(Users.id, Users.name, Users.email, UserType.title).join(UserType, isouter=True).all() user_list = session.query(Users.id, Users.name, Users.email, UserType.title).join(UserType, isouter=True) # 不加all()返回迭代器 for row in user_list: for e in row: print(e, end='') print('') ''' ''' user_list = session.query(Users) for row in user_list: print(row.id, row.name, row.email, row.user_type.title) ''' # 获取用户类型和用户类型下的所有用户(反向操作) ''' 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)
-
浙公网安备 33010602011771号