python 操作MySQL ，sql注入，sqlalchemy
一.pymysql
1.安装pymysql
pip3 insatll mysql
2.使用
#!/usr/bin/env python # --*-- encoding:utf-8 --*-- import pymysql #创建连接 conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='root',db='dbtwo') #创建游标 cursor = conn.cursor() #执行sql语句 插入 effect_row = cursor.execute("insert into tbone(nname,npwd) values('eee','eee')") #格式化字符串，插入 name = 'fff' pwd = 'fff' effect_row = cursor.execute("insert into tbone(nname,npwd) values(%s,%s)",(name,pwd)) #插入多条数据 effect_row = cursor.executemany("insert into tbone(nname,npwd) values(%s,%s)",[('abc','bcd'),('cde','def')]) #修改 effect_row = cursor.execute("update tbone set npwd ='111' where nname='eee'") #删除 effect_row = cursor.execute("delete from tbone where nid=3") #提交数据 ,否则无法修改数据库 conn.commit() #关闭游标 cursor.close() #关闭连接 conn.close()
3.查询数据
import pymysql conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='root',db='dbtwo') cursor=conn.cursor() cursor.execute("select * from tbone") #获取所有数据 res = cursor.fetchall() #获取第一行数据 one = cursor.fetchone() #获取前几行数据 many = cursor.fetchmany(3) conn.commit() cursor.close() conn.close()
注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：
cursor.scroll(1,mode='relative') # 相对当前位置移动 当前位置跳过一个 可以为负数
cursor.scroll(2,mode='absolute') # 相对绝对位置移动 从头开始跳过2个
注：有多个结果集时：cursor.nextset() cursor.fetchall()
4.默认的fetch函数获取的是元组类型((),(),())，想要获取字典类型[{},{},{}]：
创建游标时，添加参数：cursor=conn.cursor(pymysql.cursors.DictCursor)
5.获取新增数据的自增ID
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='root',db='dbtwo') cursor=conn.cursor(pymysql.cursors.DictCursor) effect_row = cursor.execute("insert into tbone(nname,npwd) values('ttt','ttt')") #获取新增数据的最后一条自增ID last_id = cursor.lastrowid print(last_id) conn.commit() cursor.close() conn.close()
二 sql注入
1.使用字符串拼接 ，会导致sql注入 ： （数据库中的用户名:abc 密码:abc）
#!/usr/bin/env python # --*-- encoding:utf-8 --*-- import pymysql conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='root',db='dbone') cursor=conn.cursor(pymysql.cursors.DictCursor) username=input('请输入用户名') password=input('请输入密码') sql_str = "select * from user where username = '%s' and password ='%s'"%(username,password) cursor.execute(sql_str) result = cursor.fetchone() if result: print('登陆成功') else: print('登陆失败') conn.commit() cursor.close() conn.close()
当用户名输入：abc' --
sql: select * from user where username = 'abc' -- ' and password ='123' 即可登陆成功 （-- 前后有空格）
当用户输入 ：sb' or 1=1 --
sql : select * from user where username = 'sb' or 1=1 -- ' and password ='sb' 也可登陆成功（-- 前后有空格）
2.使用系统提供的函数，则不会导致sql注入
#!/usr/bin/env python # --*-- encoding:utf-8 --*-- import pymysql conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='root',db='dbone') cursor=conn.cursor(pymysql.cursors.DictCursor) username=input('请输入用户名') password=input('请输入密码') cursor.execute("select * from user where username = %s and password =%s",(username,password)) result = cursor.fetchone() if result: print('登陆成功') else: print('登陆失败') conn.commit() cursor.close() conn.close()
注：字符串拼接要加 '%s' ,系统提供的函数 %s 没有使用引号
三.SQLAlchemy
SQLAlchemy是python的一个数据库ORM工具，提供了强大的对象模型间的转换，可以满足绝大多数数据库操作的需求，并且支持多种数据库引擎（sqlite，mysql，postgres, mongodb等）,将对象转换为SQL语句，利用数据API执行SQL语句
安装： pip install sqlalchemy
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
1.内部处理
使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。
#!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy 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()
2.创建表
#!/usr/bin/env python # --*-- encoding:utf-8 --*-- import sqlalchemy from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,UniqueConstraint,Index from sqlalchemy.orm import sessionmaker #连接数据库 engine = create_engine('mysql+pymysql://root:root@127.0.0.1:3306/dbtwo?charset=utf8',echo=True) #生成一个sqlorm 的基类 Base = declarative_base() class User(Base): # 表名 __tablename__ = 'userinfo' #字段 aid = Column(Integer,primary_key=True) aname = Column(String(10),index=True) fullname = Column(String(10),unique=True) password = Column(String(10)) contry = Column(String(10)) address = Column(String(20)) school = Column(String(10)) grade = Column(String(10)) #联合索引 __table_args__ =( UniqueConstraint('contry','address',name='uni_contry_address'), Index('ind_sch_gra','school','grade') ) Base.metadata.create_all(engine)
#!/usr/bin/env python # --*-- encoding:utf-8 --*-- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/dbone",echo=True,max_overflow=5) Base = declarative_base() # 单表 class User(Base): __tablename__ = 'user' id = Column(Integer,primary_key=True) name = Column(String(32)) extra = Column(String(32)) __table_args__ = ( UniqueConstraint('id','name',name='uix_id_name'), Index('ix_id_name','name','extra') ) # 一对多 class Favor(Base): __tablename__ = 'favor' nid = Column(Integer,primary_key=True) caption = Column(String(50),default='red',unique=True) class Person(Base): __tablename__ = 'person' nid = Column(Integer,primary_key=True) name = Column(String(32),index=True,nullable=True) favor_id = Column(Integer,ForeignKey('favor.nid')) #多对多 class Group(Base): __tablename__='group' id = Column(Integer,primary_key=True) name = Column(String(64),unique=True,nullable=True) port = Column(Integer,default=10000) class Server(Base): __tablename__ = 'server' id = Column(Integer,primary_key=True,autoincrement=True) hostname = Column(String(64),unique=True,nullable=False) class ServerToGroup(Base): __tablename__ = 'servertogroup' nid = Column(Integer,primary_key=True,autoincrement=True) server_id = Column(Integer,ForeignKey('server.id')) group_id = Column(Integer,ForeignKey('group.id')) def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine)
注：设置外键的另一种方式 ForeignKeyConstraint(['other_id'], ['othertable.other_id'])
3.操作表（未定义__repr__，则返回的是类的对象）
#!/usr/bin/env python # --*-- encoding:utf-8 --*-- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index from sqlalchemy.orm import sessionmaker,relationship from sqlalchemy import create_engine engine = create_engine('mysql+pymysql://root:root@127.0.0.1/dbthree',echo=True,max_overflow=5) Base = declarative_base() class Users(Base): __tablename__='user' id = Column(Integer,primary_key=True) name = Column(String(32)) extra = Column(String(16)) __table_args__=( UniqueConstraint('id','name',name='uni_id_name'), Index('in_name_extra','name','extra') ) def __repr__(self): return "%s-%s"%(self.id,self.name) #一对多 class Favor(Base): __tablename__='favor' nid = Column(Integer,primary_key=True) caption = Column(String(32),default='red',unique=True) def __repr__(self): return "%s-%s"%(self.nid,self.caption) class Person(Base): __tablename__= 'person' nid = Column(Integer,primary_key=True) name= Column(String(32),index=True,nullable=True) favor_id = Column(Integer,ForeignKey('favor.nid')) #生成与表结构无关，仅便于查询 favor = relationship('Favor',backref ='pers') #多对多 class ServerToGroup(Base): __tablename__ = 'servertogroup' nid = Column(Integer,primary_key=True,autoincrement=True) server_id = Column(Integer,ForeignKey('server.id')) group_id = Column(Integer,ForeignKey('group.id')) group = relationship('Group',backref='s2g') server = relationship('Server',backref='s2g') class Group(Base): __tablename__ = 'group' id = Column(Integer,primary_key=True) name = Column(String(64),unique=True,nullable=True) port = Column(Integer,default=1000) # group = relationship('Group',secondary = ServerToGroup,backref='host_list') class Server(Base): __tablename__='server' id = Column(Integer,primary_key=True,autoincrement=True) hostname= Column(String(64),unique=True,nullable=False) def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine) # init_db() Session = sessionmaker(bind=engine) session = Session()
#增 # obj = Users(name='aaa',extra='bbb') # session.add(obj) # session.add_all([ # Users(name='a',extra='b'), # Users(name='b',extra='c') # ]) # session.commit() #删 # session.query(Users).filter(Users.id>2).delete() # session.commit() #改 # session.query(Users).filter(Users.id>1).update({'name':'ddd'}) # session.query(Users).filter(Users.id>1).update({Users.name:Users.name+"sb"},synchronize_session=False) #修改id # session.query(Users).filter(Users.id>1).update({'id':Users.id+1},synchronize_session='evaluate') # session.commit() # 查 : # ret = session.query(Users).all() # __repr__() :[1-aaa, 3-dddsb] # ret = session.query(Users.name,Users.extra).all() # [('aaa', 'bbb'), ('dddsb', 'b')] # ret = session.query(Users).filter_by(name='aaa').all()# __repr__() :[1-aaa] # ret = session.query(Users).filter_by(name='dddsb').first() # 3-dddsb #其他查询 #条件 ret = session.query(Users).filter_by(name='c').all() #[5-c] ret = session.query(Users).filter(Users.id>2,Users.name=='e').all() #[6-e] ret = session.query(Users).filter(Users.id.between(5,7),Users.name=='g').all() #[7-g] ret = session.query(Users).filter(Users.id.in_([3,4,5])).all() #[4-a, 5-c, 3-dddsb] ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='e'))).all() #[6-e] from sqlalchemy import and_,or_ ret = session.query(Users).filter(and_(Users.id>5),Users.name=='e').all() # [6-e] ret = session.query(Users).filter(or_(Users.id< 3,Users.name=='aaa')).all() # [1-aaa] ret = session.query(Users).filter( or_( Users.id < 3, Users.name =='aaa', Users.extra !='' ) ).all() #通配符 ret = session.query(Users).filter(Users.name.like('e%')).all() #[6-e] ret = session.query(Users).filter(~Users.name.like('e%')).all() #[4-a, 1-aaa, 5-c, 3-dddsb, 7-g, 8-j] # 限制 ret = session.query(Users)[3:4] # [3-dddsb] #排序 ret = session.query(Users).order_by(Users.name.desc()).all() ret = session.query(Users).order_by(Users.name.asc()).all() #分组 分组时出现错误：sql_mode='NO_ENGINE_SUBSTITUTION' from sqlalchemy.sql import func # ret = session.query(Users.name).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(Users.id>2).all() # 连表 ret = session.query(Users,Favor).filter(Users.id==Favor.nid).all() ret = session.query(Person).join(Favor).all() # 返回的是Person 对象 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() print(ret)
4.关联（relationship） 第三张表放在前面
# ！/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine engine = create_engine(‘mysql+pymysql://root:123456@127.0.0.1:3306/kong‘) # 连接已存在的数据库 Base = declarative_base() # 根据SQL创建一个基类 class Son(Base): __tablename__ = ‘son‘ id = Column(Integer, primary_key=True) name = Column(String(32)) age = Column(String(16)) father_id = Column(Integer, ForeignKey(‘father.id‘)) # 外键（外键放在哪个类下，哪个就是多） class Father(Base): __tablename__ = ‘father‘ id = Column(Integer, primary_key=True) name = Column(String(32)) age = Column(String(16)) son = relationship(‘Son‘) # 是取与son关联的数据（通过父关联子找） # son = relationship(‘Son‘, backfe="father") # backfe="father"是（“backfe”是关键字通过子关联父找） Base.metadata.create_all(engine) # 创建所有的表 # Base.metadata.drop_all(engine) # 删除表 Session = sessionmaker(bind=engine) session = Session() f1 = Father(name=‘alvin‘, age=50) # session.commit() w1 = Son(name=‘little alvin1‘, age=4) w2 = Son(name=‘little alvin2‘, age=5) w3 = Son(name=‘little alvin3‘, age=5) f1.son = [w1, w2, w3] session.add_all([f1, w1, w2]) session.commit()
#!/usr/bin/env python #-*- coding:utf-8 -*- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine engine = create_engine(‘mysql+pymysql://root@127.0.0.1:3306/kong‘) Base = declarative_base() class Son(Base): __tablename__ = ‘son‘ id = Column(Integer, primary_key=True) name = Column(String(32)) age= Column(String(16)) father_id=Column(Integer,ForeignKey(‘father.id‘)) # 外键关系，关联两张表的关系（下面的关联查询） class Father(Base): __tablename__ =‘father‘ id = Column(Integer, primary_key=True) name = Column(String(32)) age= Column(String(16)) son=relationship(‘Son‘,backref=‘father‘) # 相当于在father类下写father=relationship(‘father‘)和在son类下写son=relationship(‘son‘)一样的效果 # 通过儿子关联并找父亲的信息；通过父亲关联并找儿子的信息（这就是relationship的关系） Base.metadata.create_all(engine) # Base.metadata.drop_all(engine) Session = sessionmaker(bind=engine) session = Session() # ret=session.query(Father.name.label(‘kkk‘),Son.name.label(‘ppp‘)).join(Son) # （关联查询）关联儿子并拿出所有的符合条件的数据 # print(ret) # Son.name.label(‘ppp‘)) 是给son.name起一个名字；label是关键字 #f1=session.query(Father).filter_by(id=1).first() # 查询父亲的信息 # print(f1.son) # s1=session.query(Son).filter_by(id=2).first() # # 查询儿子的信息；filter_by是键值对形式的查询；filter是条件的形式查询 # print(s1.father.name,s1.name) f1=session.query(Father).filter_by(id=1).first() # 不加first这类的索引只能得到sql语句不能得到具体的数据。 w4=Son(name=‘little alvin4‘,age=5) # 创建一条数据（这就是relationship内部帮实现的） f1.son.append(w4) # 插入这一条信息 session.add(f1) session.commit()
# ！/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine engine = create_engine(‘mysql+pymysql://root:123456@127.0.0.1:3306/kong?charset=utf8‘) # 连接已存在的数据库； 插入汉子就要添加编码解析?charset=utf8 Base = declarative_base() # 根据SQL创建ORM的基类 class Men_to_Wemon(Base): __tablename__ = ‘men_to_wemon‘ nid = Column(Integer, primary_key=True) id = Column(Integer, primary_key=True) men_id = Column(Integer, ForeignKey(‘men.id‘)) women_id = Column(Integer, ForeignKey(‘women.id‘)) class Men(Base): __tablename__ = ‘men‘ id = Column(Integer, primary_key=True) name = Column(String(32)) age = Column(String(16)) # gf = relationship("Women", secondary=Men_to_Wemon.__table__) # 可以在下面的backref=‘gf‘替代，表示关联； # secondary如果有第三张表会自动关联必须加__table__， class Women(Base): __tablename__ = ‘women‘ id = Column(Integer, primary_key=True) name = Column(String(32)) age = Column(String(16)) bf = relationship("Men", secondary=Men_to_Wemon.__table__, backref=‘gf‘) Base.metadata.create_all(engine) # 在数据库生成表 Session = sessionmaker(bind=engine) # 通过激活sessionmaker的__call__方法来return一个Session实例（Session类下提供了增删改查的具体方法） session = Session() # 下面是插入数据 # m1 = Men(name=‘alex‘, age=18) # m2 = Men(name=‘wusir‘, age=18) # w1 = Women(name=‘如花‘, age=26) # w2 = Women(name=‘铁蛋‘, age=30) # session.add_all([m1, m2, w1, w2]) # session.commit() # 提交添加的数据 # t1 = Men_to_Wemon(men_id=1, women_id=2) # 第三张表，让之前的两张表创建一个对应关系 m1 = session.query(Men).filter_by(id=2).first() # 查询Men的信息是（列表） w1 = session.query(Women).all() # 查询Women的信息是（列表） m1.gf = w1 # 让查询的信息创建关系 session.add_all([m1]) session.commit() # 需要注意的地方： # 1 查询时如果不加all，first等，得到的是sql语句，加上后，才是具体的结果；而all的结果是一个列表。 # 2 m1.gf是一个列表，里面存放着符合条件的对象。 # 3 filter与filter_by的区别：filter是拿键值对的参数，filter_by是拿条件判断的参数。
浙公网安备 33010602011771号