python之pymysql

       PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。

 官方文档: https://pymysql.readthedocs.io/en/latest/index.html

安装:

pip3 install PyMySQL

 

常用参数:

pymysql.Connect()   参数说明

    host(str)     mysql服务器地址

    port(int)     端口

    user(str)    用户名

    passwd(str)   密码

    db(str)    数据库名称

    charset(str)    链接编码

 

更多参数:

host – Host where the database server is 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)
bind_address – When the client has multiple network interfaces, specify the interface from which to connect to the host. Argument can be a hostname or an IP address.
unix_socket – Use a unix socket rather than TCP/IP.
read_timeout – The timeout for reading from the connection in seconds (default: None - no timeout)
write_timeout – The timeout for writing to the connection in seconds (default: None - no timeout)
charset – Charset to use.
sql_mode – Default SQL_MODE to use.
read_default_file – Specifies my.cnf file to read these parameters from under the [client] section.
conv – Conversion dictionary to use instead of the default one. This is used to provide custom marshalling and unmarshalling of types. See converters.
use_unicode – Whether or not to default to unicode strings. This option defaults to true.
client_flag – Custom flags to send to MySQL. Find potential values in constants.CLIENT.
cursorclass – Custom cursor class to use.
init_command – Initial SQL statement to run when connection is established.
connect_timeout – The timeout for connecting to the database in seconds. (default: 10, min: 1, max: 31536000)
ssl – A dict of arguments similar to mysql_ssl_set()’s parameters.
ssl_ca – Path to the file that contains a PEM-formatted CA certificate.
ssl_cert – Path to the file that contains a PEM-formatted client certificate.
ssl_disabled – A boolean value that disables usage of TLS.
ssl_key – Path to the file that contains a PEM-formatted private key for the client certificate.
ssl_verify_cert – Set to true to check the server certificate’s validity.
ssl_verify_identity – Set to true to check the server’s identity.
read_default_group – Group to read from in the configuration file.
autocommit – 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 server in bytes. (default: 16MB) Only used to limit size of “LOAD LOCAL INFILE” data packet smaller than default (16KB).
defer_connect – Don’t explicitly connect on construction - wait for connect call. (default: False)
auth_plugin_map – A dict of plugin names to a class that processes that plugin. The class will take the Connection object as the argument to the constructor. The class needs an authenticate method taking an authentication packet as an argument. For the dialog plugin, a prompt(echo, prompt) method can be used (if no authenticate method) for returning a string from the user. (experimental)
server_public_key – SHA256 authentication plugin public key value. (default: None)
binary_prefix – Add _binary prefix on bytes and bytearray. (default: False)
compress – Not supported.
named_pipe – Not supported.
db – DEPRECATED Alias for database.
passwd – DEPRECATED Alias for password.

 

 

 

 

connection() 对象支持的方法

    cursor()    使用该链接创建并返回游标

    commit()    提交当前事务

    rollback()    回滚当前事务

    close()    关闭链接

 

curser()对象支持的方法

    execute(op)     执行一个数据库的查询命令

    fetchone()    取得结果集的下一行

    fetchmany(size)    获取结果集的下几行

    fetchall()   获取结果集中的所有行

    rowcount()   返回数据条数或影响行数

    close()   关闭游标对象

    lastrowid 获取自增ID

 

 

 

 

---

 

 

　　连接数据库;

import pymysql

connect = pymysql.Connect(
    host = 'localhost',
    port = 3306,
    user = 'root',
    passwd = '123456',
    db = 'python',
    charset = 'utf8',
    cursorclass = pymysql.cursors.DictCursor // 指定类型
)

//获取游标
cursor = connect.cursor()

 

　　插入数据:

sql = "INSERT INTO trade (name, account, saving) VALUES ( '%s', '%s', %.2f )"
data = ('雷军', '13512345678', 10000)
cursor.execute(sql % data)
connect.commit()
print('成功插入', cursor.rowcount, '条数据')

// 获取插入的ID(一定要在commit之前获取)
last_id = curs.lastrowid

 

　　修改数据:

sql = "UPDATE trade SET saving = %.2f WHERE account = '%s' "
data = (8888, '13512345678')
cursor.execute(sql % data)
connect.commit()
print('成功修改', cursor.rowcount, '条数据')

 

　　查询数据:

sql = "SELECT name,saving FROM trade WHERE account = '%s' "
data = ('13512345678',)
cursor.execute(sql % data)
for row in cursor.fetchall():
    print("Name:%s\tSaving:%.2f" % row)
print('共查找出', cursor.rowcount, '条数据')

# 返回结果是一个字典类型数据

 

　　删除数据:

sql = "DELETE FROM trade WHERE account = '%s' LIMIT %d"
data = ('13512345678', 1)
cursor.execute(sql % data)
connect.commit()
print('成功删除', cursor.rowcount, '条数据')

 

　　事务处理:

sql_1 = "UPDATE trade SET saving = saving + 1000 WHERE account = '18012345678' "
sql_2 = "UPDATE trade SET expend = expend + 1000 WHERE account = '18012345678' "
sql_3 = "UPDATE trade SET income = income + 2000 WHERE account = '18012345678' "

try:
    cursor.execute(sql_1)  # 储蓄增加1000
    cursor.execute(sql_2)  # 支出增加1000
    cursor.execute(sql_3)  # 收入增加2000
except Exception as e:
    connect.rollback()  # 事务回滚
    print('事务处理失败', e)
else:
    connect.commit()  # 事务提交
    print('事务处理成功', cursor.rowcount)

# 关闭连接
cursor.close()
connect.close()

 

　　

游标控制

所有的数据查询操作均基于游标，我们可以通过cursor.scroll(num, mode)控制游标的位置。

cursor.scroll(1, mode='relative') # 相对当前位置移动
cursor.scroll(2, mode='absolute') # 相对绝对位置移动

设置游标类型

查询时，默认返回的数据类型为元组，可以自定义设置返回类型。支持5种游标类型：

• Cursor: 默认，元组类型
• DictCursor: 字典类型
• DictCursorMixin: 支持自定义的游标类型，需先自定义才可使用
• SSCursor: 无缓冲元组类型
• SSDictCursor: 无缓冲字典类型

无缓冲游标类型，适用于数据量很大，一次性返回太慢，或者服务端带宽较小时。

 

事务处理

• 开启事务 connection.begin()

• 提交修改 connection.commit()

• 回滚事务 connection.rollback()

 

防 SQL 注入

转义特殊字符 connection.escape_string(str)

参数化语句 支持传入参数进行自动转义、格式化 SQL 语句，以避免 SQL 注入等安全问题。

# 插入数据(元组或列表)
effect_row = cursor.execute('INSERT INTO `users` (`name`, `age`) VALUES (%s, %s)', ('mary', 18))

# 插入数据(字典)
info = {'name': 'fake', 'age': 15}
effect_row = cursor.execute('INSERT INTO `users` (`name`, `age`) VALUES (%(name)s, %(age)s)', info)

# 批量插入
effect_row = cursor.executemany(
'INSERT INTO `users` (`name`, `age`) VALUES (%s, %s) ON DUPLICATE KEY UPDATE age=VALUES(age)', [
    ('hello', 13),
    ('fake', 28),
])

 

 

示例:

　　1. 执行SQL

cursor = connection.cursor()
    
# 创建数据表
effect_row = cursor.execute('''
CREATE TABLE `users` (
  `name` varchar(32) NOT NULL,
  `age` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
''')
    
# 插入数据(元组或列表)
effect_row = cursor.execute('INSERT INTO `users` (`name`, `age`) VALUES (%s, %s)', ('mary', 18))
    
# 插入数据(字典)
info = {'name': 'fake', 'age': 15}
effect_row = cursor.execute('INSERT INTO `users` (`name`, `age`) VALUES (%(name)s, %(age)s)', info)
    
connection.commit()

　　

# 获取游标
cursor = connection.cursor()
    
# 批量插入
effect_row = cursor.executemany(
    'INSERT INTO `users` (`name`, `age`) VALUES (%s, %s) ON DUPLICATE KEY UPDATE age=VALUES(age)', [
        ('hello', 13),
        ('fake', 28),
    ])
    
connection.commit()

　　注意: INSERT,UPDATE,DELETE等修改操作,需要手动执行commit()完成对数据的修改提交

　　2.查询数据

# 执行查询 SQL
cursor.execute('SELECT * FROM `users`')

# 获取单条数据
cursor.fetchone()

# 获取前N条数据
cursor.fetchmany(3)

# 获取所有数据
cursor.fetchall()

   3.In查询

GAME_APPIDS = (10000010, 10000039)

sql = 'SELECT id, app_id, app_name, status FROM `app_info` WHERE status=1 AND app_id in {app_ids}'.format(app_ids=str(GAME_APPIDS))

    cursor.execute(sql)
    return cursor.fetchall()

 

 

 

 

 

　常见问题:

　　1. 

pymysql.err.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'fas好",\'wozhidaole知道了\'" where title = "好"\' at line 1')

　　字符串中出现引号,导致SQL语句发生错误,需要进行转义

pymysql.escape_string(str)

 

　　2.MySQL升级到8.0之后, 出现报错:KeyError: 255

 

 

  原因: 由于之前是mysql5.6的版本, 现在升级到了8.0, 老的pymysql版本无法在新版本中正常使用

解决:

# 升级pymysql版本
pip install --upgrade PyMySQL

   3.紧接上个问题, 当升级了pysql之后, 发现之前的脚本竟然报错了,  

 

 为啥是语法错误, 之前跑得一直挺正常的, 后来发现 在之前使用 cursor.execute(sql) 的时候传递的并非一条SQL插入语句, 而是多个insert语句拼接 最后一并执行的

insert into `log`.`daily_count` (type,dnu,dau,wau,mau,npa,apa,fpa,app_id,created_date) values (1,2,818,1230,1851,0,6,6,'10000001','20220928');insert into `log`.`daily_count` (type,dnu,dau,wau,mau,app_id,created_date) values (3,0,0,1,1,'10000001','20220928');insert into `log`.`daily_count` (type,dnu,dau,wau,mau,app_id,created_date) values (4,2,634,985,1402,'10000001','20220928');insert into `log`.`daily_count` (type,dnu,dau,wau,mau,npa,apa,fpa,app_id,created_date) values (2,1,65,87,127,1,2,1,'10000001','20220928')

但是升级之后 就不允许这种操作,认为这是一种语法错误

解决:

1.将多个语法分割成单独的SQL, 然后分别执行即可

sql_list = sqls.split(';')

for sql in sql_list:
    cursor.execute(sql)
    cursor.commit()