526互联

[pymysql]新增数据-手工新增-自动新增-批量执行

发布时间 2023-03-26 23:30:25作者: LeoShi2020

1. 手工新增

import pymysql

# 获取连接
conn = pymysql.connect(
    host='10.105.212.1',
    port=3306,
    user='root',
    password='DemoDemo',
    database='db',
    charset='utf8'
)

# 获取游标
# 字典方式显示 pymysql.cursors.DictCursor {'id': 1, 'name': 'liuxu', 'password': '12345'}
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 手动新增数据
sql = 'insert into users(name, password) values("liuxu5", "123456");'
cursor.execute(sql)
conn.commit()			# 需要确认一次, 否则数据库中不会保存记录

2. 自动新增

import pymysql

# 获取连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='12345',
    database='db',
    charset='utf8',
    autocommit=True			# autocommit=True，表示execute后自动提交保存，无需再手动commit()
)

cursor = conn.cursor(pymysql.cursors.DictCursor)


sql = 'insert into users(name, password) values("liuxu6", "123456")'
cursor.execute(sql)

3. 批量执行 executemany

import pymysql

# 获取连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='12345',
    database='db',
    charset='utf8',
    autocommit=True
)

cursor = conn.cursor(pymysql.cursors.DictCursor)


sql = 'insert into users(name, password) values(%s, %s)'	# 占位, 注意和 "%s"的区别！！！
item_list = [("liuxu10", "12345"), ("liuixu11", "12345"), ("liuxu12", "12345")]		# 多个替换数据
cursor.executemany(sql, args=item_list)		# 返回受影响的函数