Python之pymysql的使用小结
在python3.x中，可以使用pymysql来MySQL数据库的连接，并实现数据库的各种操作，本次博客主要介绍了pymysql的安装和使用方法。
PyMySQL的安装
一、.windows上的安装方法：
在python3.6中，自带pip3，所以在python3中可以直接使用pip3去安装所需的模块：
pip3installpymysql-ihttps://pypi.douban.com/simple
二、.linux下安装方法：
1.tar包下载及解压
下载tar包 wgethttps://pypi.python.org/packages/29/f8/919a28976bf0557b7819fd6935bfd839118aff913407ca58346e14fa6c86/PyMySQL-0.7.11.tar.gz#md5=167f28514f4c20cbc6b1ddf831ade772 解压并展开tar包 tarxfPyMySQL-0.7.11.tar.gz
2.安装
[root@localhostPyMySQL-0.7.11]#python36setup.pyinstall
数据库的连接
本次测试创建的数据及表：
#创建数据库及表，然后插入数据
mysql>createdatabasedbforpymysql;
mysql>createtableuserinfo(idintnotnullauto_incrementprimarykey,usernamevarchar(10),passwdvarchar(10))engine=innodbdefaultcharset=utf8;
mysql>insertintouserinfo(username,passwd)values('frank','123'),('rose','321'),('jeff',666);
#查看表内容
mysql>select*fromuserinfo;
+----+----------+--------+
|id|username|passwd|
+----+----------+--------+
|1|frank|123|
|2|rose|321|
|3|jeff|666|
+----+----------+--------+
3rowsinset(0.00sec)
连接数据库：
importpymysql
#连接数据库
db=pymysql.connect("localhost","root","LBLB1212@@","dbforpymysql")
#使用cursor()方法创建一个游标对象
cursor=db.cursor()
#使用execute()方法执行SQL语句
cursor.execute("SELECT*FROMuserinfo")
#使用fetall()获取全部数据
data=cursor.fetchall()
#打印获取到的数据
print(data)
#关闭游标和数据库的连接
cursor.close()
db.close()
#运行结果
((1,'frank','123'),(2,'rose','321'),(3,'jeff','666'))
要完成一个MySQL数据的连接，在connect中可以接受以下参数：
def__init__(self,host=None,user=None,password="",
database=None,port=0,unix_socket=None,
charset='',sql_mode=None,
read_default_file=None,conv=None,use_unicode=None,
client_flag=0,cursorclass=Cursor,init_command=None,
connect_timeout=10,ssl=None,read_default_group=None,
compress=None,named_pipe=None,no_delay=None,
autocommit=False,db=None,passwd=None,local_infile=False,
max_allowed_packet=16*1024*1024,defer_connect=False,
auth_plugin_map={},read_timeout=None,write_timeout=None,
bind_address=None):
参数解释：
host:Hostwherethedatabaseserverislocated#主机名或者主机地址
user:Usernametologinas#用户名
password:Passwordtouse.#密码
database:Databasetouse,Nonetonotuseaparticularone.#指定的数据库
port:MySQLporttouse,defaultisusuallyOK.(default:3306)#端口，默认是3306
bind_address:Whentheclienthasmultiplenetworkinterfaces,specify
theinterfacefromwhichtoconnecttothehost.Argumentcanbe
ahostnameoranIPaddress.#当客户端有多个网络接口的时候，指点连接到数据库的接口，可以是一个主机名或者ip地址
unix_socket:Optionally,youcanuseaunixsocketratherthanTCP/IP.
charset:Charsetyouwanttouse.#指定字符编码
sql_mode:DefaultSQL_MODEtouse.
read_default_file:
Specifiesmy.cnffiletoreadtheseparametersfromunderthe[client]section.
conv:
Conversiondictionarytouseinsteadofthedefaultone.
Thisisusedtoprovidecustommarshallingandunmarshalingoftypes.
Seeconverters.
use_unicode:
Whetherornottodefaulttounicodestrings.
ThisoptiondefaultstotrueforPy3k.
client_flag:CustomflagstosendtoMySQL.Findpotentialvaluesinconstants.CLIENT.
cursorclass:Customcursorclasstouse.
init_command:InitialSQLstatementtorunwhenconnectionisestablished.
connect_timeout:Timeoutbeforethrowinganexceptionwhenconnecting.
(default:10,min:1,max:31536000)
ssl:
Adictofargumentssimilartomysql_ssl_set()'sparameters.
Fornowthecapathandcipherargumentsarenotsupported.
read_default_group:Grouptoreadfromintheconfigurationfile.
compress;Notsupported
named_pipe:Notsupported
autocommit:Autocommitmode.Nonemeansuseserverdefault.(default:False)
local_infile:BooleantoenabletheuseofLOADDATALOCALcommand.(default:False)
max_allowed_packet:Maxsizeofpacketsenttoserverinbytes.(default:16MB)
Onlyusedtolimitsizeof"LOADLOCALINFILE"datapacketsmallerthandefault(16KB).
defer_connect:Don'texplicitlyconnectoncontruction-waitforconnectcall.
(default:False)
auth_plugin_map:Adictofpluginnamestoaclassthatprocessesthatplugin.
TheclasswilltaketheConnectionobjectastheargumenttotheconstructor.
Theclassneedsanauthenticatemethodtakinganauthenticationpacketas
anargument.Forthedialogplugin,aprompt(echo,prompt)methodcanbeused
(ifnoauthenticatemethod)forreturningastringfromtheuser.(experimental)
db:Aliasfordatabase.(forcompatibilitytoMySQLdb)
passwd:Aliasforpassword.(forcompatibilitytoMySQLdb)
cursor其实是调用了cursors模块下的Cursor的类，这个模块主要的作用就是用来和数据库交互的，当你实例化了一个对象的时候，你就可以调用对象下面的各种绑定方法：
classCursor(object): """ Thisistheobjectyouusetointeractwiththedatabase. """ defclose(self): """ Closingacursorjustexhaustsallremainingdata. """ defsetinputsizes(self,*args): """Doesnothing,requiredbyDBAPI.""" defsetoutputsizes(self,*args): """Doesnothing,requiredbyDBAPI.""" defexecute(self,query,args=None): """Executeaquery :paramstrquery:Querytoexecute. :paramargs:parametersusedwithquery.(optional) :typeargs:tuple,listordict :return:Numberofaffectedrows :rtype:int Ifargsisalistortuple,%scanbeusedasaplaceholderinthequery. Ifargsisadict,%(name)scanbeusedasaplaceholderinthequery. """ defexecutemany(self,query,args): #type:(str,list)->int """Runseveraldataagainstonequery :paramquery:querytoexecuteonserver :paramargs:Sequenceofsequencesormappings.Itisusedasparameter. :return:Numberofrowsaffected,ifany. Thismethodimprovesperformanceonmultiple-rowINSERTand REPLACE.Otherwiseitisequivalenttoloopingoverargswith execute(). """ deffetchone(self): """Fetchthenextrow""" deffetchmany(self,size=None): """Fetchseveralrows""" deffetchall(self): """Fetchalltherows""" ......
数据库操作
一、数据库增删改操作
commit()方法：在数据库里增、删、改的时候，必须要进行提交，否则插入的数据不生效。
importpymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db=pymysql.connect(**config)
cursor=db.cursor()
sql="INSERTINTOuserinfo(username,passwd)VALUES('jack','123')"
cursor.execute(sql)
db.commit()#提交数据
cursor.close()
db.close()
或者在execute提供插入的数据
importpymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db=pymysql.connect(**config)
cursor=db.cursor()
sql="INSERTINTOuserinfo(username,passwd)VALUES(%s,%s)"
cursor.execute(sql,("bob","123"))
db.commit()#提交数据
cursor.close()
db.close()
小知识点，mysql的注入问题：
在mysql中使用"--"代表注释，比如现在来实现一个用户登录的小程序：
用户名和密码都存在表userinfo中，表内容如下：
mysql>select*fromuserinfo;
+----+----------+--------+
|id|username|passwd|
+----+----------+--------+
|1|frank|123|
|2|rose|321|
|3|jeff|666|
+----+----------+--------+
3rowsinset(0.00sec)
小程序代码如下：
importpymysql
user=input("username:")
pwd=input("password:")
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db=pymysql.connect(**config)
cursor=db.cursor(cursor=pymysql.cursors.DictCursor)
sql="select*fromuserinfowhereusername='%s'andpasswd='%s'"%(user,pwd)
result=cursor.execute(sql)
cursor.close()
db.close()
ifresult:
print('登录成功')
else:
print('登录失败')
#正确登录的运行结果
username:frank
password:123
result:1
登录成功
#错误登录的运行结果
username:frank
password:1231231
result:0
登录失败
看起来没有什么问题，但是试试下面的方式吧
----------------------------------------------
username:'or1=1--
password:123
result:3
登录成功
----------------------------------------------
咦~也登录成功了.
为什么呢？可以看一下现在的执行的sql语句：
select*fromuserinfowhereusername=''or1=1--'andpasswd='123'
这里--后面的会被注释，所以where一定会成功，这里等于查看了所有行的内容，返回值也不等于0，所以就登录成功了。
解决方法就是将变量或者实参直接写到execute中即可：
result=cursor.execute(sql,(user,pwd))
在键入类似'or1=1--的时候就不会登录成功了。
executemany()：用来同时插入多条数据：
importpymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db=pymysql.connect(**config)
cursor=db.cursor()
sql="INSERTINTOuserinfo(username,passwd)VALUES(%s,%s)"
cursor.executemany(sql,[("tom","123"),("alex",'321')])
db.commit()#提交数据
cursor.close()
db.close()
execute()和executemany()都会返回受影响的行数：
sql="deletefromuserinfowhereusername=%s"
res=cursor.executemany(sql,("jack",))
print("res=",res)
#运行结果
res=1
当表中有自增的主键的时候，可以使用lastrowid来获取最后一次自增的ID：
importpymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db=pymysql.connect(**config)
cursor=db.cursor()
sql="INSERTINTOuserinfo(username,passwd)VALUES(%s,%s)"
cursor.execute(sql,("zed","123"))
print("thelastrowidis",cursor.lastrowid)
db.commit()#提交数据
cursor.close()
db.close()
#运行结果
thelastrowidis10
二、数据库的查询操作
这里主要介绍三个绑定方法：
- fetchone():获取下一行数据，第一次为首行；
- fetchall():获取所有行数据源
- fetchmany(4):获取下4行数据
先来查看表的内容：
mysql>select*fromuserinfo; +----+----------+--------+ |id|username|passwd| +----+----------+--------+ |1|frank|123| |2|rose|321| |3|jeff|666| |5|bob|123| |8|jack|123| |10|zed|123| +----+----------+--------+ 6rowsinset(0.00sec)
使用fetchone()：
importpymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db=pymysql.connect(**config)
cursor=db.cursor()
sql="SELECT*FROMuserinfo"
cursor.execute(sql)
res=cursor.fetchone()#第一次执行
print(res)
res=cursor.fetchone()#第二次执行
print(res)
cursor.close()
db.close()
#运行结果
(1,'frank','123')
(2,'rose','321')
使用fetchall()：
importpymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db=pymysql.connect(**config)
cursor=db.cursor()
sql="SELECT*FROMuserinfo"
cursor.execute(sql)
res=cursor.fetchall()#第一次执行
print(res)
res=cursor.fetchall()#第二次执行
print(res)
cursor.close()
db.close()
#运行结果
((1,'frank','123'),(2,'rose','321'),(3,'jeff','666'),(5,'bob','123'),(8,'jack','123'),(10,'zed','123'))
()
可以看到，第二次获取的时候，什么数据都没有获取到，这个类似于文件的读取操作。
默认情况下，我们获取到的返回值是元组，只能看到每行的数据，却不知道每一列代表的是什么，这个时候可以使用以下方式来返回字典，每一行的数据都会生成一个字典：
cursor=db.cursor(cursor=pymysql.cursors.DictCursor)#在实例化的时候，将属性cursor设置为pymysql.cursors.DictCursor
使用fetchall获取所有行的数据，每一行都被生成一个字典放在列表里面：
importpymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db=pymysql.connect(**config)
cursor=db.cursor(cursor=pymysql.cursors.DictCursor)
sql="SELECT*FROMuserinfo"
cursor.execute(sql)
res=cursor.fetchall()
print(res)
cursor.close()
db.close()
#运行结果
[{'id':1,'username':'frank','passwd':'123'},{'id':2,'username':'rose','passwd':'321'},{'id':3,'username':'jeff','passwd':'666'},{'id':5,'username':'bob','passwd':'123'},{'id':8,'username':'jack','passwd':'123'},{'id':10,'username':'zed','passwd':'123'}]
这样获取到的内容就能够容易被理解和使用了！
在获取行数据的时候，可以理解开始的时候，有一个行指针指着第一行的上方，获取一行，它就向下移动一行，所以当行指针到最后一行的时候，就不能再获取到行的内容，所以我们可以使用如下方法来移动行指针：
cursor.scroll(1,mode='relative')#相对当前位置移动 cursor.scroll(2,mode='absolute')#相对绝对位置移动
第一个值为移动的行数，整数为向下移动，负数为向上移动，mode指定了是相对当前位置移动，还是相对于首行移动
例如：
sql="SELECT*FROMuserinfo"
cursor.execute(sql)
res=cursor.fetchall()
print(res)
cursor.scroll(0,mode='absolute')#相对首行移动了0，就是把行指针移动到了首行
res=cursor.fetchall()#第二次获取到的内容
print(res)
#运行结果
[{'id':1,'username':'frank','passwd':'123'},{'id':2,'username':'rose','passwd':'321'},{'id':3,'username':'jeff','passwd':'666'},{'id':5,'username':'bob','passwd':'123'},{'id':8,'username':'jack','passwd':'123'},{'id':10,'username':'zed','passwd':'123'}]
[{'id':1,'username':'frank','passwd':'123'},{'id':2,'username':'rose','passwd':'321'},{'id':3,'username':'jeff','passwd':'666'},{'id':5,'username':'bob','passwd':'123'},{'id':8,'username':'jack','passwd':'123'},{'id':10,'username':'zed','passwd':'123'}]
上下文管理器
在python的文件操作中支持上下文管理器，在操作数据库的时候也可以使用：
importpymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db=pymysql.connect(**config)
withdb.cursor(cursor=pymysql.cursors.DictCursor)ascursor:#获取数据库连接的对象
sql="SELECT*FROMuserinfo"
cursor.execute(sql)
res=cursor.fetchone()
print(res)
cursor.scroll(2,mode='relative')
res=cursor.fetchone()
print(res)
cursor.close()
db.close()
#运行结果
{'id':1,'username':'frank','passwd':'123'}
{'id':5,'username':'bob','passwd':'123'}
上下文管理器可以使代码的可读性更强。
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。