pymysql模块

一、模块方法

1.安装pymysql模块

pip install pymysql

2.卸载pymysql模块

pip uninstall pymysql

 

3.连接

db = pymsql.connect()    创建一个连接参数：host - 数据库服务器所在的主机user - 以登录身份登录的用户名password - 要使用的密码。database - 要使用的数据库，None不使用特定的数据库。port - 要使用的MySQL端口，默认通常都可以。（默认值：3306）bind_address - 当客户端具有多个网络接口时，请指定从中连接到主机的接口。参数可以是主机名或IP地址。unix_socket - 您可以选择使用unix套接字而不是TCP / IP。read_timeout - 以秒为单位读取连接的超时（默认值：无 - 无超时）write_timeout - 以秒为单位写入连接的超时（默认值：无 - 无超时）charset - 你要使用的Charset。sql_mode - 要使用的默认SQL_MODE。read_default_file - 指定my.cnf文件以从[client]部分下读取这些参数。conv - 使用转换字典而不是默认字典。这用于提供类型的自定义编组和解组。见转换器。use_unicode - 是否默认为unicode字符串。对于Py3k，此选项默认为true。client_flag - 要发送给MySQL的自定义标志。在constants.CLIENT中查找潜在值。cursorclass - 要使用的自定义游标类。init_command - 建立连接时要运行的初始SQL语句。connect_timeout - 连接时抛出异常之前的超时。（默认值：10，最小值：1，最大值：31536000）ssl - 类似于mysql_ssl_set（）参数的参数的dict。read_default_group - 要在配置文件中读取的组。compress - 不支持named_pipe - 不支持autocommit - 自动提交模式。无表示使用服务器默认值。（默认值：False）local_infile - 允许使用LOAD DATA LOCAL命令的布尔值。（默认值：False）max_allowed_pa​​cket - 发送到服务器的最大数据包大小（以字节为单位）。（默认值：16MB）仅用于限制小于默认值（16KB）的“LOAD LOCAL INFILE”数据包的大小。defer_connect - 不要明确连接contruction - 等待连接调用。（默认值：False）auth_plugin_map - 插件名称的一个字典，用于处理该插件的类。该类将Connection对象作为构造函数的参数。该类需要一个认证方法，将认证包作为参数。对于对话框插件，可以使用提示（echo，prompt）方法（如果没有authenticate方法）从用户返回字符串。（实验）server_public_key - SHA256 authenticnticaiton插件公钥值。（默认：无）db - 数据库的别名。（与MySQLdb兼容）passwd - 密码的别名。（与MySQLdb兼容）binary_prefix - 在字节和bytearray上添加_binary前缀。（默认值：False）常用连接参数：    host:数据库服务器的主机名或者IP地址（localhost,IP,10.127.1.1）    user:数据库服务器的登录用户名    password:密码    database:数据库名称    port:   数据库端口，默认3306    charset:设置字符集名称

　　

写法1：conn = pymysql.connect(host='localhost',user='root',password='',database='userinfo',charset='utf8')写法2：db_config = {    'host':'localhost',    'user':'root',    'password':'',    'database':'userinfo',    'charset':'utf8'}conn = pymysql.connect(**db_config)

　　

4.conn(连接)对象支持的方法

conn = pymysql.connect(host='localhost',user='root',password='',database='test',charset='utf8')conn.cursor(cursor=None)   #创建一个游标    cursor默认为None,设置为pymysql.cursors.DictCursor，返回值为字典形式conn.commit()   #提交当前事务 在增、删、改数据时，才会保存数据。conn.rollback() #回滚当前事务conn.close()    #关闭连接

　　

5.cursor(游标)对象支持的方法

cursor.execute(query,args=None)    #执行一个SQL语句    参数：        query 要执行查询的SQL语句        args  传递的参数可为 元组、列表或字典    返回值：        返回值为受影响的行数，类型为int;        受影响的行数也可以用cursor.rowcount获得cursor.executemany()    #执行多条语句cursor.fetchone()   #取得结果的一条数据cursor.fetchmany(size=None)  #取得结果的多条数据，指定size的值可以设置取多少数据cursor.fetchall()   #取得结果的所有数据cursor.lastrowid    #获取上次插入数据的自增ID值cursor.close()  #关闭游标对象

　　

二、pymysql操作mysql数据库

创建数据库 userinfocreate database userinfo default charset utf8;创建数据表 userCreate Table: CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `username` char(32) NOT NULL,  `password` char(64) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8插入数据insert into user(username,password) values ('felix','123');

　

 

1.增

 (1)增加1条记录

import pymysqlconn = pymysql.connect(host='localhost',user='root',password='',database='userinfo',charset='utf8') #连接数据库获得一个连接cursor = conn.cursor()  #创建一个游标# sql = "insert into user(username,password) values('test','123')"  #SQL语句sql = "insert into user(username,password) values(%s,%s)"try:    # res = cursor.execute(sql)    res = cursor.execute(sql, ('test', 123))    #执行一个SQL语句    print('受影响的行数：',res)    conn.commit()   #提交数据，此操作使用与增、删、改操作except BaseException as err:    print('错误：',err)    conn.rollback() #事务回滚finally:    cursor.close()  #关闭游标    conn.close()    #关闭连接

　　 

执行结果：

 

(2)增加多条记录

import pymysqlconn = pymysql.connect(host='localhost',user='root',password='',database='userinfo',charset='utf8')cursor = conn.cursor()# sql = "insert into user(username,password) values('test','123')"sql = "insert into user(username,password) values(%s,%s)"try:    # res = cursor.execute(sql)    res = cursor.executemany(sql, [('test1', '123'), ('test2', '123'), ('test3', '123')])    print('受影响的行数：',res)    conn.commit()   #提交数据except BaseException as err:    print('错误：',err)    conn.rollback()finally:    cursor.close()    conn.close()

　

 

(3)获取自增ID值

　　cursor.lastrowid　获取自增ID值

import pymysqlconn = pymysql.connect(host='localhost',user='root',password='',database='userinfo',charset='utf8') #连接数据库获得一个连接cursor = conn.cursor()  #创建一个游标sql = "insert into user(username,password) values(%s,%s)"try:    res = cursor.execute(sql, ('test', 123))    #执行一个SQL语句    print('受影响的行数：',res)    conn.commit()    last_insert_id = cursor.lastrowid   #使用cursor.lastrowid 获取刚刚插入的自增ID    print('刚刚插入的自增ID为：{}'.format(last_insert_id))except BaseException as err:    print('错误：',err)    conn.rollback() #事务回滚finally:    cursor.close()  #关闭游标    conn.close()    #关闭连接执行结果：受影响的行数： 1刚刚插入的自增ID为：11

　　　　　  

2.删

 (1)删除一个记录

import pymysqlconn = pymysql.connect(host='localhost',user='root',password='',database='userinfo',charset='utf8') #连接数据库获得一个连接cursor = conn.cursor()  #创建一个游标# sql = "delete from"  #SQL语句# sql = "delete from user where =3"  #SQL语句sql = "delete from user where username = %s"  #SQL语句try:    # res = cursor.execute(sql)    res = cursor.execute(sql, 'test2')    #执行一个SQL语句    print('受影响的行数：',res)    conn.commit()   #提交数据，此操作使用与增、删、改操作except BaseException as err:    print('错误：',err)    conn.rollback() #事务回滚finally:    cursor.close()  #关闭游标    conn.close()    #关闭连接

 

(2)删除多个记录

import pymysqlconn = pymysql.connect(host='localhost',user='root',password='',database='userinfo',charset='utf8') #连接数据库获得一个连接cursor = conn.cursor()  #创建一个游标sql = "delete from user where username = %s"  #SQL语句try:    res = cursor.executemany(sql, ('alina','felix'))    #执行多个SQL语句    print('受影响的行数：',res)    conn.commit()   #提交数据，此操作使用与增、删、改操作except BaseException as err:    print('错误：',err)    conn.rollback() #事务回滚finally:    cursor.close()  #关闭游标    conn.close()    #关闭连接

 

3.改

 (1)更改1条记录

import pymysqlconn = pymysql.connect(host='localhost',user='root',password='',database='userinfo',charset='utf8')cursor = conn.cursor()# sql = "update user set username='alina',password='t123' where id = 3"sql = "update user set username= %s,password = %s where id = %s "try:    #res = cursor.execute(sql)    res = cursor.execute(sql, ('alina','t123',3))    print('受影响的行数：',res)    conn.commit()   #提交数据except BaseException as err:    print('错误：',err)    conn.rollback()finally:    cursor.close()    conn.close()

 

(2)更改多条记录

import pymysqlconn = pymysql.connect(host='localhost',user='root',password='',database='userinfo',charset='utf8')cursor = conn.cursor()sql = "update user set password = %s where id = %s "try:    res = cursor.executemany(sql, [('123456',1),('123456',2),('123456',3)])    print('受影响的行数：',res)    conn.commit()   #提交数据except BaseException as err:    print('错误：',err)    conn.rollback()finally:    cursor.close()    conn.close()

　　

4.查

 (1)获取1条数据　　

cursor.fetchone()

import pymysqlconn = pymysql.connect(host='localhost',user='root',password='',database='userinfo',charset='utf8')cursor = conn.cursor()sql = "select * from user where username=%s and password=%s"try:    user = input('>>>')    passwd = input('>>>')    res = cursor.execute(sql,(user,passwd))    print('受影响的行数：',res)    ret = cursor.fetchone()    print(ret)    if ret:        print('登录成功')        print('用户名：{} 密码：{}'.format(ret[1],ret[2]))    else:        print('用户名密码错误！登录失败！')except BaseException as err:    print('错误：',err)finally:    cursor.close()    conn.close()

　　

注意：

不要使用字符串拼接方式使用SQL语句，防止SQL注入问题，如下实例：uuuu' or 1=1 --

SQL注入问题：

import pymysqlconn = pymysql.connect(host='localhost',user='root',password='',database='userinfo',charset='utf8')cursor = conn.cursor()try:    user = input('>>>')    passwd = input('>>>')    sql = "select * from user where username='%s' and password='%s'" % (user, passwd)    res = cursor.execute(sql)    print('受影响的行数：',res)    ret = cursor.fetchone()    print(ret)    if ret:        print('登录成功')        print('用户名：{} 密码：{}'.format(ret[1],ret[2]))    else:        print('用户名密码错误！登录失败！')except BaseException as err:    print('错误：',err)finally:    cursor.close()    conn.close()执行结果：>>>uuuuu' or 1=1 -- >>>受影响的行数： 3(1, 'felix', '123456')登录成功用户名：felix 密码：123456

 

(2)获取多条数据　　

　　cursor.fetchmany()　　获取多条数据

　　cursor.fetchall()　　获取所有数据

 

import pymysqlconn = pymysql.connect(host='localhost',user='root',password='',database='userinfo',charset='utf8')cursor = conn.cursor()try:    user = input('>>>')    passwd = input('>>>')    sql = "select * from user where username='%s' and password='%s'" % (user, passwd)    res = cursor.execute(sql)    print('受影响的行数：',res)    # ret = cursor.fetchone()   #获取1条数据    # ret = cursor.fetchmany(3) #获取指定条数据    # ret = cursor.fetchmany(size=4)    #获取指定条数据    ret = cursor.fetchall()     #获取所有数据    print(ret)    if ret:        print('登录成功')        print('用户名：{} 密码：{}'.format(ret[0][1],ret[0][2]))    else:        print('用户名密码错误！登录失败！')except BaseException as err:    print('错误：',err)finally:    cursor.close()    conn.close()执行结果：>>>test>>>123456受影响的行数： 4((3, 'test', '123456'), (4, 'test', '123456'), (5, 'test', '123456'), (6, 'test', '123456'))登录成功用户名：test 密码：123456

 

(3)fetch获取数据类型

　　fetch默认获取的数据是元祖类型，如果想要获取字典类型的数据，则设置游标为字典类型

　　cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) 　　　参数cursor=pymysql.cursors.DictCursor 结果以字典形式返回

import pymysqlconn = pymysql.connect(host='localhost',user='root',password='',database='userinfo',charset='utf8')cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #cursor=pymysql.cursors.DictCursor 结果以字典形式返回try:    sql = "select * from user"    res = cursor.execute(sql)    print('受影响的行数：',res)    ret = cursor.fetchall()    print(ret)except BaseException as err:    print('错误：',err)finally:    cursor.close()    conn.close()执行结果：受影响的行数： 3[{'id': 1, 'username': 'felix', 'password': '123456'}, {'id': 2, 'username': 'alina', 'password': '123456'}, {'id': 3, 'username': 'test', 'password': '123456'}]

　　

(4)指定游标位置

　　在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

cursor.scroll(3,mode='absolute')　　 #相对绝对位置移动 即都是相对首行位置移动，不受当前位置影响

cursor.scroll(3, mode='relative')　　 #相对当前位置移动，受当前位置影响

　　

import pymysqldb_config = {    'host':'localhost',    'user':'root',    'password':'',    'database':'userinfo',    'charset':'utf8'}conn = pymysql.connect(**db_config)cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)sql = "select * from user"res = cursor.execute(sql)print('受影响的行数：',res)ret = cursor.fetchone()print('未指定游标位置获取第1条数据',ret)ret = cursor.fetchone()print('未指定游标位置获取第2条数据',ret)cursor.scroll(3,mode='absolute')  #相对绝对位置移动 即相对首行位置移动ret = cursor.fetchone()print('mode=absolute 相对首行位置移动后',ret)cursor.scroll(-2, mode='relative')   #相对当前位置移动 当前位置为第4行ret = cursor.fetchone()print('mode=relative 相对当前位置移动后',ret)cursor.scroll(3, mode='relative')   #相对当前位置移动 当前位置为第4行ret = cursor.fetchone()print('mode=relative 相对当前位置移动后',ret)cursor.scroll(3,mode='absolute')  #相对绝对位置移动 即相对首行位置移动ret = cursor.fetchone()print('mode=absolute 相对首行位置移动后',ret)cursor.close()conn.close()执行结果：受影响的行数： 9未指定光标位置获取第1条数据 {'id': 1, 'username': 'felix', 'password': '123456'}未指定光标位置获取第2条数据 {'id': 2, 'username': 'alina', 'password': '123456'}mode=absolute 相对首行位置移动后 {'id': 4, 'username': 'test', 'password': '123'}mode=relative 相对当前位置移动后 {'id': 3, 'username': 'test', 'password': '123456'}mode=relative 相对当前位置移动后 {'id': 7, 'username': 'test', 'password': '123'}mode=absolute 相对首行位置移动后 {'id': 4, 'username': 'test', 'password': '123'}