12 Jun 18 复习, pymsql
12 Jun 18 复习 pymysql，orm
- pymysql 的安装
pip3 install pymysql
2. pymysql的基本语法
mport pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="123",
database="day62",
charset="utf8"，
autocommit=True,
)
cursor = conn.cursor()
cursor.execute("select * from user;")
ret = cursor.fetchall() # fetchone, fetchmany
# conn.commit()
cursor.close()
conn.close()
3 pymsql：execute（）之sql注入
原因： 符号--会注释掉它之后的sql
例如： 在一条sql语句中如果遇到select * from t1 where id > 3 -- and name='egon';则--之后的条件被注释掉了
#1、sql注入之：用户存在，绕过密码
egon' -- 任意字符
#2、sql注入之：用户不存在，绕过用户与密码
xxx' or 1=1 -- 任意字符
解决方案：不自行进行sql语句拼接；execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了
sql="select * from userinfo where name=%s and password=%s" #！！！
res=cursor.execute(sql,[user,pwd])
4 单例简版orm（youku版）
Mysql_singleton.py
import pymysql
class Mysql:
__instance=None
def __init__(self):
self.conn=pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
charset='utf8',
database='youku',
autocommit=True
)
self.cursor=self.conn.cursor(cursor=pymysql.cursors.DictCursor)
def close_db(self):
self.cursor.close()
self.conn.close()
def select(self, sql, args=None):
self.cursor.execute(sql, args)
return self.cursor.fetchall()
def execute(self, sql, args):
try:
self.cursor.execute(sql, args)
affected = self.cursor.rowcount
except BaseException as e:
print(e)
return affected
@classmethod
def singleton(cls):
if not cls.__instance:
cls.__instense=cls()
return cls.__instance
fuckorm.py
from orm import Mysql_singleton
class Fileld:
def __init__(self,name,column_type,primary_key,default):
self.name=name
self.column_type=column_type
self.primary_key= primary_key
self.default=default
class StringFileld(Fileld):
def __init__(self,name=None,column_type='varchar(200)',primary_key=False,default=None):
super().__init__(name,column_type,primary_key,default)
class IntegerFileld(Fileld):
def __init__(self,name=None,column_type='int',primary_key=False,default=0):
super().__init__(name,column_type,primary_key,default)
class ModlesMetaclass(type):
def __new__(cls, name,bases,attrs):
if name=='Modles':
return type.__new__(cls,name,bases,attrs)
table_name=attrs.get('table_name',None)
primary_key=None
mappings=dict()
for k,v in attrs.items():
if isinstance(v,Fileld):#v 是不是Field的对象
mappings[k]=v
if v.primary_key:
#找到主键
if primary_key:
raise TypeError('主键重复：%s'%k)
primary_key=k
for k in mappings.keys():
attrs.pop(k)
if not primary_key:
raise TypeError('没有主键')
attrs['table_name']=table_name
attrs['primary_key']=primary_key
attrs['mappings']=mappings
return type.__new__(cls,name,bases,attrs)
class Modles(dict,metaclass=ModlesMetaclass):
def __init__(self,**kwargs):
super().__init__(**kwargs)
def __setattr__(self, key, value):
self[key]=value
def __getattr__(self, item):
try:
return self[item]
except TypeError:
raise ('没有该属性')
@classmethod
def select_one(cls,**kwargs):
key=list(kwargs.keys())[0]
value=kwargs[key]
sql='select * from %s where %s=?'%(cls.table_name,key)
sql=sql.replace('?','%s')
ms=Mysql_singleton.Mysql().singleton()
re=ms.select(sql,value)
if re:
u=cls(**re[0])
return u
else:
return
@classmethod
def select_many(cls,**kwargs):
ms = Mysql_singleton.Mysql().singleton()
if kwargs:
key=list(kwargs.keys())[0]
value=kwargs[key]
sql = 'select * from %s where %s=?' % (cls.table_name, key)
sql = sql.replace('?', '%s')
re = ms.select(sql, value)
else:
sql = 'select * from %s'%(cls.table_name)
re = ms.select(sql)
if re:
lis_obj=[cls(**r) for r in re]
return lis_obj
else:
return
def update(self):
ms = Mysql_singleton.Mysql().singleton()
filed=[]
pr=None
args=[]
for k,v in self.mappings.items():
if v.primary_key:
pr=getattr(self,v.name,None)
else:
filed.append(v.name + '=?')
args.append(getattr(self,v.name,v.default))
sql='update %s set %s where %s =%s'%(self.table_name,','.join(filed),self.primary_key,pr)
sql=sql.replace('?','%s')
ms.execute(sql,args)
def save(self):
ms = Mysql_singleton.Mysql().singleton()
filed=[]
values=[]
args=[]
for k,v in self.mappings.items():
if not v.primary_key:
filed.append(v.name)
values.append('?')
args.append(getattr(self,v.name,v.default))
sql ='insert into %s (%s) VALUES (%s)'%(self.table_name,','.join(filed),','.join(values))
sql= sql.replace('?','%s')
ms.execute(sql,args)