pymysql模块_mysql_内存溢出

概述一、模块安装#安装pip3installpymysql二、链接、执行sql、关闭（游标）一、模块安装pip3 install pyMysqL二、链接、执行sql、关闭（游标）

=input(=input(#链接
conn=pyMysqL.connect(host='localhost',user='root',password='123',database='egon',charset='utf8) #指定字符编码，可显示中文
#游标
cursor=conn.cursor()

#执行SQL语句
sql='select * from userinfo where name="%s" and password="%s"' %(user,pwd) #注意%s需要加引号
print(sql)
res=cursor.execute(sql) #执行SQL语句，返回SQL查询成功的记录数目
print(res)

cursor.close()
conn.close()

if res:
print('登录成功')
else:
print('登录失败')

三、execute()之sql注入

注意：符号--会注释掉它之后的sql，正确的语法：--后至少有一个任意字符

根本原理：就根据程序的字符串拼接name='%s'，我们输入一个xxx' -- haha,用我们输入的xxx加'在程序中拼接成一个判断条件name='xxx' -- haha'

最后那一个空格，在一条SQL语句中如果遇到select * t1 where ID > 3 -- name=;则--#1、sql注入之：用户存在，绕过密码
egon' -- 任意字符

#2、sql注入之：用户不存在，绕过用户与密码
xxx' or 1=1 -- 任意字符

#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）
sql="select * from userinfo where name=%s and password=%s" #！！！注意%s需要去掉引号，因为pyMysqL会自动为我们加上

单条数据执行SQL语句
cursor.execute(sql,[user,pwd]) #pyMysqL模块自动帮我们解决sql注入的问题，只要我们按照pyMysqL的规矩来。

四、增、删、改：conn.commit()conn=pyMysqL.connect(host=,database=cursor=#执行SQL语句

part1 sql='insert into userinfo(name,password) values("root","123456");' res=cursor.execute(sql) #执行SQL语句，返回sql影响成功的行数 print(res)

#part2

sql='insert into userinfo(name,password) values(%s,%s);' res=cursor.execute(sql,("root","123456")) #执行SQL语句，返回sql影响成功的行数 print(res)

#part3
sql='insert into userinfo(name,%s);'
res=cursor.executemany(sql,[("root","123456"),("lhf","12356"),("eee","156")]) #执行SQL语句，返回sql影响成功的行数
print(res)

conn.commit() #提交后才发现表中插入记录成功
cursor.close()
conn.close()

五、查：fetchone，fetchmany，fetchall

conn=pyMysqL.connect(host=,charset='utf8'cursor=

sql==cursor.execute(sql)

#查单条res1=res2=res3=　　（3，‘root’,'123456'）

res4=cursor.fetchmany(2 ,）

res5=( %conn.commit() <span >#<span >提交后才发现表中插入记录成功
<span >cursor.close()
conn.close()
<span >'''<span >
(1,'root','123456')
(2,'123456')
(3,'123456')
((4,'123456'),(5,'123456'))
((6,(7,'lhf','12356'),(8,'eee','156'))
8 rows in set (0.00 sec)
<span >'''

六、获取插入的最后一条数据的自增ID=pyMysqL.connect(host=,charset='utf8=sql='insert into userinfo(name,password) values("xxx","123");'
rows=cursor.execute(sql)

conn.commit()
print(cursor.lastrowID) #在commit之前和之后都可以查看
cursor.close()
conn.close()

七、自定义sqlhelper!class sqlHelper(object):

</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;def</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800080;"&gt;<a href="https://www.jb51.cc/tag/init/" target="_blank" >__init__</a></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;(self):    self.conn </span>=<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; None    self.cursor </span>=<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; None</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;def</span> open(self,cursor=<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;py<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" >MysqL</a>.cursors.DictCursor):    self.conn </span>=<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; db_po<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>.PO<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>.connection()    self.cursor </span>= self.conn.cursor(cursor=<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;cursor)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;def</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; close(self):    self.cursor.close()    self.conn.close()</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;def</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; fetchone(self,<a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>,p<a href="https://www.jb51.cc/tag/ara/" target="_blank" >ara</a>ms):    cursor </span>=<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; self.cursor    cursor.execute(<a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>,p<a href="https://www.jb51.cc/tag/ara/" target="_blank" >ara</a>ms)    res<a href="https://m.jb51.cc/tag/ul/" target="_blank" >ul</a>t </span>=<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; cursor.fetchone()    </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;return</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; res<a href="https://m.jb51.cc/tag/ul/" target="_blank" >ul</a>t</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;def</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; fetchall(self,p<a href="https://www.jb51.cc/tag/ara/" target="_blank" >ara</a>ms)    res<a href="https://m.jb51.cc/tag/ul/" target="_blank" >ul</a>t </span>=<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; cursor.fetchall()    </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;return</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; res<a href="https://m.jb51.cc/tag/ul/" target="_blank" >ul</a>t</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;def</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; create(self,p<a href="https://www.jb51.cc/tag/ara/" target="_blank" >ara</a>ms)    self.conn.commit()    </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;return</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; self.cursor.lastrow<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;def</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; delete(self,p<a href="https://www.jb51.cc/tag/ara/" target="_blank" >ara</a>ms)    self.conn.commit()</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;def</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; update(self,p<a href="https://www.jb51.cc/tag/ara/" target="_blank" >ara</a>ms)    self.conn.commit()</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;def</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800080;"&gt;__enter__</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;(self):    self.<a href="https://www.jb51.cc/tag/open/" target="_blank" >open()</a>    </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;return</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; self</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;def</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800080;"&gt;__exit__</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;(self,exc_type,exc_val,exc_tb):    self.close()</span></pre>

<div >

         sqlHelper()  helper.fetchone(,        res<a href="https://m.jb51.cc/tag/ul/" target="_blank" >ul</a>t2 </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;"&gt;=</span> helper.fetchall(<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;'</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;select * from users </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;'</span>,<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;[]</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;)  <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000;"&gt;#查所有记录</span>        helper.</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;create</span>(<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;'</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;insert into user (<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>,pwd) value(<a href="https://www.jb51.cc/tag/s/" target="_blank" >%s</a>,<a href="https://www.jb51.cc/tag/s/" target="_blank" >%s</a>)</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;'</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;,</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;[</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;'hc',123</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;"&gt;]</span>)   <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000;"&gt; # <a href="https://m.jb51.cc/tag/chuangjian/" target="_blank" >创建</a>记录</span></pre>
使用时
          总结       以上是内存溢出为你收集整理的pymysql模块全部内容，希望文章能够帮你解决pymysql模块所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。					
										


					
						欢迎分享，转载请注明来源：内存溢出
原文地址: http://outofmemory.cn/sjk/1169513.html

pymysql模块

发表评论

评论列表（0条）