51做网站建设企业官网,建站公司那家好,网站建站授权模板下载,陕西煤化建设集团网站在Python3.x中#xff0c;可以使用pymysql来MySQL数据库的连接#xff0c;并实现数据库的各种操作#xff0c;本次博客主要介绍了pymysql的安装和使用方法。PyMySQL的安装一、.windows上的安装方法#xff1a;在python3.6中#xff0c;自带pip3#xff0c;所以在python3中…在Python3.x中可以使用pymysql来MySQL数据库的连接并实现数据库的各种操作本次博客主要介绍了pymysql的安装和使用方法。PyMySQL的安装一、.windows上的安装方法在python3.6中自带pip3所以在python3中可以直接使用pip3去安装所需的模块pip3 install pymysql -i https://pypi.douban.com/simple二、.linux下安装方法1.tar包下载及解压下载tar包wget https://pypi.python.org/packages/29/f8/919a28976bf0557b7819fd6935bfd839118aff913407ca58346e14fa6c86/PyMySQL-0.7.11.tar.gz#md5167f28514f4c20cbc6b1ddf831ade772解压并展开tar包tar xf PyMySQL-0.7.11.tar.gz2.安装[rootlocalhost PyMySQL-0.7.11]# python36 setup.py install数据库的连接本次测试创建的数据及表#创建数据库及表然后插入数据mysql create databasedbforpymysql;mysql create table userinfo(id int not null auto_increment primary key,username varchar(10),passwd varchar(10))engineinnodb default charsetutf8;mysql insert into userinfo(username,passwd) values(frank,123),(rose,321),(jeff,666);#查看表内容mysql select * fromuserinfo;----------------------| id | username | passwd |----------------------| 1 | frank | 123 || 2 | rose | 321 || 3 | jeff | 666 |----------------------3 rows in set (0.00 sec)连接数据库importpymysql#连接数据库db pymysql.connect(localhost,root,LBLB1212,dbforpymysql)#使用cursor()方法创建一个游标对象cursor db.cursor()#使用execute()方法执行SQL语句cursor.execute(SELECT * FROM userinfo)#使用fetall()获取全部数据data cursor.fetchall()#打印获取到的数据print(data)#关闭游标和数据库的连接cursor.close()db.close()#运行结果((1, frank, 123), (2, rose, 321), (3, jeff, 666))要完成一个MySQL数据的连接在connect中可以接受以下参数def __init__(self, hostNone, userNone, password,databaseNone, port0, unix_socketNone,charset, sql_modeNone,read_default_fileNone, convNone, use_unicodeNone,client_flag0, cursorclassCursor, init_commandNone,connect_timeout10, sslNone, read_default_groupNone,compressNone, named_pipeNone, no_delayNone,autocommitFalse, dbNone, passwdNone, local_infileFalse,max_allowed_packet16*1024*1024, defer_connectFalse,auth_plugin_map{}, read_timeoutNone, write_timeoutNone,bind_addressNone):参数解释host: Host where the database serveris located #主机名或者主机地址user: Username to log in as #用户名password: Password to use. #密码database: Database to use, None to not use a particular one. #指定的数据库port: MySQL port to use, default is usually OK. (default: 3306) #端口默认是3306bind_address: When the client has multiple network interfaces, specifythe interfacefromwhich to connect to the host. Argument can bea hostnameor an IP address. #当客户端有多个网络接口的时候指点连接到数据库的接口可以是一个主机名或者ip地址unix_socket: Optionally, you can use a unix socket rather than TCP/IP.charset: Charset you want to use.#指定字符编码sql_mode: Default SQL_MODE to use.read_default_file:Specifies my.cnf file to read these parametersfromunder the [client] section.conv:Conversion dictionary to use instead of the default one.Thisis used to provide custom marshalling andunmarshaling of types.See converters.use_unicode:Whetheror notto default to unicode strings.This option defaults to trueforPy3k.client_flag: Custom flags to send to MySQL. Find potential valuesinconstants.CLIENT.cursorclass: Custom cursorclassto use.init_command: Initial SQL statement to run when connectionisestablished.connect_timeout: Timeout before throwing an exception when connecting.(default:10, min: 1, max: 31536000)ssl:A dict of arguments similar to mysql_ssl_set()s parameters.For now the capath and cipher arguments are notsupported.read_default_group: Group to readfrom inthe configuration file.compress; Not supportednamed_pipe: Not supportedautocommit: Autocommit mode. None means use server default. (default: False)local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)max_allowed_packet: Max size of packet sent to serverinbytes. (default: 16MB)Only used to limit size ofLOAD LOCAL INFILEdata packet smaller than default (16KB).defer_connect: Dont explicitly connect on contruction - wait for connect call.(default: False)auth_plugin_map: A dict of plugin names to aclassthat processes that plugin.Theclasswill take the Connection object as the argument to the constructor.Theclassneeds an authenticate method taking an authentication packet asan argument. For the dialog plugin, a prompt(echo, prompt) method can be used(if no authenticate method) for returning a string fromthe user. (experimental)db: Aliasfor database. (forcompatibility to MySQLdb)passwd: Aliasfor password. (for compatibility to MySQLdb)参数cursor其实是调用了cursors模块下的Cursor的类这个模块主要的作用就是用来和数据库交互的当你实例化了一个对象的时候你就可以调用对象下面的各种绑定方法classCursor(object):This is the object you use to interact with the database.defclose(self):Closing a cursor just exhausts all remaining data.def setinputsizes(self, *args):Does nothing, required by DB API.def setoutputsizes(self, *args):Does nothing, required by DB API.def execute(self, query, argsNone):Execute a query:param str query: Query to execute.:param args: parameters used with query. (optional):type args: tuple, list or dict:return: Number of affected rows:rtype: intIf args is a list or tuple, %s can be used as a placeholder in the query.If args is a dict, %(name)s can be used as a placeholder in the query.defexecutemany(self, query, args):#type: (str, list) - intRun several data against one query:param query: query to execute on server:param args: Sequence of sequences or mappings. It is used as parameter.:return: Number of rows affected, if any.This method improves performance on multiple-row INSERT andREPLACE. Otherwise it is equivalent to looping over args withexecute().deffetchone(self):Fetch the next rowdef fetchmany(self, sizeNone):Fetch several rowsdeffetchall(self):Fetch all the rows......一些绑定方法数据库操作一、数据库增删改操作commit()方法在数据库里增、删、改的时候必须要进行提交否则插入的数据不生效。importpymysqlconfig{host:127.0.0.1,user:root,password:LBLB1212,database:dbforpymysql}db pymysql.connect(**config)cursordb.cursor()sql INSERT INTO userinfo(username,passwd) VALUES(jack,123)cursor.execute(sql)db.commit()#提交数据cursor.close()db.close()或者在execute提供插入的数据importpymysqlconfig{host:127.0.0.1,user:root,password:LBLB1212,database:dbforpymysql}db pymysql.connect(**config)cursordb.cursor()sql INSERT INTO userinfo(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 |----------------------3 rows in set (0.00sec)小程序代码如下importpymysqluser input(username:)pwd input(password:)config{host:127.0.0.1,user:root,password:LBLB1212,database:dbforpymysql}db pymysql.connect(**config)cursor db.cursor(cursorpymysql.cursors.DictCursor)sql select * from userinfo where username%s and passwd%s %(user,pwd)resultcursor.execute(sql)cursor.close()db.close()ifresult:print(登录成功)else:print(登录失败)#正确登录的运行结果username:frankpassword:123result:1登录成功#错误登录的运行结果username:frankpassword:1231231result: 0登录失败看起来没有什么问题但是试试下面的方式吧----------------------------------------------username:or 11 --password:123result:3登录成功----------------------------------------------咦~也登录成功了.为什么呢可以看一下现在的执行的sql语句select* from userinfo where username or 11 -- and passwd123这里--后面的会被注释所以where一定会成功这里等于查看了所有行的内容返回值也不等于0所以就登录成功了。解决方法就是将变量或者实参直接写到execute中即可resultcursor.execute(sql,(user,pwd))在键入类似or 11 -- 的时候就不会登录成功了。MySQL的注入问题executemany()用来同时插入多条数据importpymysqlconfig{host:127.0.0.1,user:root,password:LBLB1212,database:dbforpymysql}db pymysql.connect(**config)cursordb.cursor()sql INSERT INTO userinfo(username,passwd) VALUES(%s,%s)cursor.executemany(sql,[(tom,123),(alex,321)])db.commit()#提交数据cursor.close()db.close()execute()和executemany()都会返回受影响的行数sql delete from userinfo where username%sres cursor.executemany(sql,(jack,))print(res,res)#运行结果res 1当表中有自增的主键的时候可以使用lastrowid来获取最后一次自增的IDimportpymysqlconfig{host:127.0.0.1,user:root,password:LBLB1212,database:dbforpymysql}db pymysql.connect(**config)cursordb.cursor()sql INSERT INTO userinfo(username,passwd) VALUES(%s,%s)cursor.execute(sql,(zed,123))print(the last rowid is,cursor.lastrowid)db.commit()#提交数据cursor.close()db.close()#运行结果the last rowid is 10二、数据库的查询操作这里主要介绍三个绑定方法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 |----------------------6 rows in set (0.00 sec)使用fetchone()importpymysqlconfig{host:127.0.0.1,user:root,password:LBLB1212,database:dbforpymysql}db pymysql.connect(**config)cursordb.cursor()sql SELECT * FROM userinfocursor.execute(sql)res cursor.fetchone() #第一次执行print(res)res cursor.fetchone() #第二次执行print(res)cursor.close()db.close()#运行结果(1, frank, 123)(2, rose, 321)使用fetchall()importpymysqlconfig{host:127.0.0.1,user:root,password:LBLB1212,database:dbforpymysql}db pymysql.connect(**config)cursordb.cursor()sql SELECT * FROM userinfocursor.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(cursorpymysql.cursors.DictCursor) #在实例化的时候将属性cursor设置为pymysql.cursors.DictCursor使用fetchall获取所有行的数据每一行都被生成一个字典放在列表里面importpymysqlconfig{host:127.0.0.1,user:root,password:LBLB1212,database:dbforpymysql}db pymysql.connect(**config)cursor db.cursor(cursorpymysql.cursors.DictCursor)sql SELECT * FROM userinfocursor.execute(sql)rescursor.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,moderelative) #相对当前位置移动cursor.scroll(2,modeabsolute) #相对绝对位置移动第一个值为移动的行数整数为向下移动负数为向上移动mode指定了是相对当前位置移动还是相对于首行移动例如sql SELECT * FROM userinfocursor.execute(sql)rescursor.fetchall()print(res)cursor.scroll(0,modeabsolute) #相对首行移动了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的文件操作中支持上下文管理器在操作数据库的时候也可以使用importpymysqlconfig{host:127.0.0.1,user:root,password:LBLB1212,database:dbforpymysql}db pymysql.connect(**config)with db.cursor(cursorpymysql.cursors.DictCursor) as cursor: #获取数据库连接的对象sql SELECT * FROM userinfocursor.execute(sql)rescursor.fetchone()print(res)cursor.scroll(2,moderelative)rescursor.fetchone()print(res)cursor.close()db.close()#运行结果{id: 1, username: frank, passwd: 123}{id: 5, username: bob, passwd: 123}上下文管理器可以使代码的可读性更强。
