conn = pymssql.connect(host='xxx', port="xxx", user="xxx", password="xxx", database="xxx",charset="utf8", autocommit=False)
if conn:
print('连接数据库成功!')
cursor = conn.cursor()
with conn.cursor() as cursor:
try:
if not tradedf_1.empty:
sql = "update gold_transaction set A=(%s),state=(%s),B=(%s),C=(%s),D=(%s),E=(%s),F=(%s) where id=(%s) "
cursor.executemany(sql,commit_list_1) # commit_list_1为state=1的更新列表
print('state=1数据更新成功')
conn.commit()
elif not tradedf_2.empty:
sql = "update gold_transaction set A=(%s),state=(%s),B=(%s),C=(%s),D=(%s),E=(%s),F=(%s) where id=(%s) "
cursor.executemany(sql,commit_list_2) # commit_list_2为state=2的更新列表
print('state=2数据更新成功')
conn.commit()
elif not tradedf_3.empty:
sql = "update gold_transaction set state=(%s) where id=(%s) "
cursor.executemany(sql,commit_list_3) # commit_list_1为state=-1的更新列表
print('state=-1数据更新成功')
conn.commit()
except:
logging.exception("exception")
conn.rollback()
print('数据回滚')
conn.close()
python mssql 使用executemany，6000多条数据更新update需要65秒，和execute类似，按照其他文章测算，应该是0.6秒左右，有什么问题？怎样才能提速？
commit_list_1的格式为：[(1.2082, 1, -20, 0.0, -20.0, '2022-12-27 18:33:31', '2022-11-24 10:25:15', 164140), (0.6762, 1, 9, 0.0, 10.0, '2022-12-27 18:33:31', '2022-12-12 23:57:21', 164148), (0.9362, 1, 90, 0.0, 98.1344, '2022-12-27 18:33:31', '2022-12-14 00:00:00', 164151)]
可能有以下几个问题导致更新数据较慢：
cursor.executemany() 方法的调用方式。 executemany() 方法需要传入一个列表，其中每个元素都是一个要更新的数据记录，但是您的代码中使用了多个 if-elif 语句来分别更新不同状态的数据，这种方式会在每次更新时重新创建游标并重新连接数据库，导致效率较低。建议将需要更新的数据按状态分类后合并成一个列表，然后在一次连接和游标创建的情况下调用 executemany() 方法。
数据库连接和游标的创建方式。您的代码中在第 3 行创建了一个数据库连接，并在第 5 行创建了一个游标对象，但是在第 7 行又重新创建了一个游标对象。建议使用 with 语句来管理数据库连接和游标，避免手动创建和关闭连接和游标。
更新数据的条数。如果您的数据记录数过多，一次性更新可能会导致数据库性能下降。建议分批更新，例如每次更新 1000 条数据。
数据库索引设置。如果要更新的表中存在索引，建议在更新前先禁用索引，更新完成后再重新启用索引，可以提高更新效率。
修改后的代码如下：
conn = pymssql.connect(host='xxx', port="xxx", user="xxx", password="xxx", database="xxx",charset="utf8", autocommit=False)
if conn:
print('连接数据库成功!')
try:
with conn.cursor() as cursor:
commit_list = []
if not tradedf_1.empty:
commit_list = commit_list_1 # commit_list_1为state=1的更新列表
print('state=1数据更新成功')
elif not tradedf_2.empty:
commit_list = commit_list_2 # commit_list_2为state=2的更新列表
print('state=2数据更新成功')
elif not tradedf_3.empty:
commit_list = commit_list_3 # commit_list_1为state=-1的更新列表
print('state=-1数据更新成功')
if commit_list:
sql = "update gold_transaction set A=(%s),state=(%s),B=(%s),C=(%s),D=(%s),E=(%s),F=(%s) where id=(%s) "
cursor.executemany(sql, commit_list)
conn.commit()
except:
logging.exception("exception")
conn.rollback()
print('数据回滚')
finally:
conn.close()
此外，你还可以考虑以下优化方式：
使用数据库连接池管理数据库连接，以避免频繁地创建和关闭连接。
使用数据库的批量更新功能，例如 SQL Server 中的 UPDATE ... FROM 语句，以一次更新多条记录。
使用 SQL Server Profiler 工具来分析 SQL Server 数据库的性能瓶颈，并进行相应的优化。
您好，关于您提出的executemany更新太慢的问题，这里建议如下:
1.第4行的cursor这行删除，不需要，下面的with那行会创建cursor
2.数据表字段加索引，尤其是主键字段id，以及经常要用来查询的字段
3.批量更新，一次性最多1000条左右，不能太多。分批次提交。效率更高
在使用 executemany() 批量更新数据时，可以尝试以下方法来提高更新速度：
1.使用 SQL Server 的 Bulk Insert 功能。可以将待更新数据存储为 CSV 文件，然后使用 SQL Server 自带的 Bulk Insert 功能快速地将 CSV 文件中的数据导入到数据库中。示例代码如下：
import pandas as pd
import pymssql
# 生成 CSV 文件
df = pd.DataFrame(commit_list_1, columns=["A", "state", "B", "C", "D", "E", "F", "id"])
df.to_csv("data.csv", index=False)
# 使用 Bulk Insert 导入数据
conn = pymssql.connect(host='xxx', port="xxx", user="xxx", password="xxx", database="xxx", charset="utf8")
with conn.cursor() as cursor:
cursor.execute("BULK INSERT gold_transaction FROM 'data.csv' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n', FIRSTROW=2)")
conn.commit()
2.使用多线程或协程并行更新数据库。如果更新的数据量较大，可以考虑使用多线程或协程并行更新数据库，以提高更新速度。示例代码如下：
from concurrent.futures import ThreadPoolExecutor
import pymssql
def update_data(commit_list):
conn = pymssql.connect(host='xxx', port="xxx", user="xxx", password="xxx", database="xxx", charset="utf8")
with conn.cursor() as cursor:
sql = "update gold_transaction set A=(%s),state=(%s),B=(%s),C=(%s),D=(%s),E=(%s),F=(%s) where id=(%s) "
cursor.executemany(sql, commit_list)
conn.commit()
conn.close()
# 使用 ThreadPoolExecutor 并行更新数据
with ThreadPoolExecutor(max_workers=4) as executor:
executor.submit(update_data, commit_list_1)
executor.submit(update_data, commit_list_2)
executor.submit(update_data, commit_list_3)
更新慢的原因可能与以下几个方面有关：
网络传输速度慢：如果网络传输速度不够快，就会导致更新操作变慢。可以考虑将数据库和应用程序部署在同一台服务器上，或者使用更快的网络连接。
数据库表结构设计不合理：如果数据库表结构设计不合理，也会导致更新操作变慢。可以考虑优化表结构，增加索引等。
数据库服务器负载过高：如果数据库服务器负载过高，也会导致更新操作变慢。可以考虑将数据库服务器升级或者添加更多的服务器来分担负载。
执行语句不合理：在执行 SQL 语句时，应该避免使用 select * 这样的语句，可以只查询需要的列。此外，应该尽量避免使用死锁等操作，这些都会降低更新操作的速度。
除此之外，还有一些针对具体代码的优化建议：
将 autocommit 设置为 True，这样可以减少执行 SQL 语句的次数。
通过分批次提交 SQL 语句来减少网络传输次数。可以考虑将 commit_list_1 等列表分成多个子列表，每个子列表包含一定数量的元素，然后分批次执行 SQL 语句。
使用批量插入语句，如 INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3), (value1, value2, value3)，这样可以减少 SQL 语句的执行次数。
对于频繁查询的数据，可以使用缓存，以减少数据库的访问次数。
可以考虑把多条更新组合为一个 update语句，然后再去调用数据库的更新操作，这样可以实现和数据库的交互减少
根据你提供的代码，你使用了多次的cursor.executemany语句，这意味着你在每个分支中都会创建新的游标，而不是在一个游标中执行多个语句。这可能会导致一定的性能损失，因为每个游标都需要进行连接和通信。你可以尝试将所有的语句都放在同一个游标中执行，以避免这种情况。
此外，你可以尝试将executemany改为execute并使用参数化查询。使用参数化查询可以减少网络通信和数据库解析时间，因为查询只会被编译一次，并且参数的值会被替换为占位符。
以下是将所有更新语句合并到一个游标并使用参数化查询的示例代码：
conn = pymssql.connect(host='xxx', port="xxx", user="xxx", password="xxx", database="xxx", charset="utf8", autocommit=False)
if conn:
print('连接数据库成功!')
cursor = conn.cursor()
try:
if not tradedf_1.empty:
sql = "update gold_transaction set A=%s, state=%s, B=%s, C=%s, D=%s, E=%s, F=%s where id=%s"
cursor.execute(sql, [(x[0], x[1], x[2], x[3], x[4], x[5], x[6], x[7]) for x in commit_list_1])
print('state=1数据更新成功')
elif not tradedf_2.empty:
sql = "update gold_transaction set A=%s, state=%s, B=%s, C=%s, D=%s, E=%s, F=%s where id=%s"
cursor.execute(sql, [(x[0], x[1], x[2], x[3], x[4], x[5], x[6], x[7]) for x in commit_list_2])
print('state=2数据更新成功')
elif not tradedf_3.empty:
sql = "update gold_transaction set state=%s where id=%s"
cursor.execute(sql, [(x[0], x[1]) for x in commit_list_3])
print('state=-1数据更新成功')
conn.commit()
except:
logging.exception("exception")
conn.rollback()
print('数据回滚')
conn.close()
这里我们只使用一个游标和一个execute方法来更新所有的数据。同时，我们使用了列表推导式来将commit_list中的元组转换为参数列表。这种方法可以减少网络通信和数据库解析时间，并提高性能。
该回答引用ChatGPT
executemany 方法的执行时间受多种因素影响，例如网络延迟、数据库负载、硬件性能等。在你的情况下，如果更新6000多条数据需要65秒的时间，那么可能存在一些性能问题。以下是一些可能影响性能的因素，以及如何解决这些问题：
1、网络延迟
如果你的数据库和应用程序在不同的服务器上，并且它们之间的网络连接不稳定或延迟较高，那么可能会导致执行时间延长。可以尝试优化网络连接，例如减少网络传输数据量、增加网络带宽等方法。
2、数据库负载
如果你的数据库负载较高，即正在同时处理大量的读写请求，那么可能会导致执行时间延长。可以尝试优化数据库负载，例如通过增加硬件资源、优化查询语句、减少不必要的查询等方法来降低负载。
3、硬件性能
如果你的服务器硬件性能较低，例如 CPU、内存等，那么可能会导致执行时间延长。可以尝试升级硬件或增加服务器数量来提高性能。
4、数据库连接方式
你可以尝试使用不同的数据库连接方式，例如 ODBC、ADO.NET 或直接使用数据库的客户端库来连接数据库，看是否能提高执行效率。
5、批量提交
executemany 方法可以将多个参数提交到数据库中，但是每个参数都会产生一次数据库操作。你可以考虑将多个参数合并成一个语句，然后一次性提交到数据库中，可以减少数据库操作的次数，从而提高执行效率。
总之，你需要先了解执行过程中的瓶颈在哪里，然后通过相应的方法来解决这些问题。在实践中，往往需要根据具体情况来进行测试和优化，以找到最佳的执行方案。
import pyodbc
# 连接到 SQL Server 数据库
cnxn = pyodbc.connect("Driver={SQL Server};Server=myserver;Database=mydb;UID=myusername;PWD=mypassword")
cursor = cnxn.cursor()
# 定义 SQL 查询语句
sql = "UPDATE mytable SET value1=?, value2=?, value3=? WHERE id=?"
# 定义要更新的数据列表
data_list = [(1.2082, 1, -20, 164140), (0.6762, 1, 9, 164148), (0.9362, 1, 90, 164151)]
# 将多个参数合并成一个 SQL 语句并提交到数据库中
sql_values = ",".join(["(?, ?, ?, ?)"] * len(data_list))
sql_query = f"{sql} {sql_values}"
params = [item for sublist in data_list for item in sublist]
cursor.execute(sql_query, params)
# 提交事务
cnxn.commit()
# 关闭数据库连接
cnxn.close()
将executemany改成了execute，并在循环内逐条更新数据库：
with conn.cursor() as cursor:
try:
if not tradedf_1.empty:
sql = "update gold_transaction set A=%s, state=%s, B=%s, C=%s, D=%s, E=%s, F=%s where id=%s"
for commit in commit_list_1:
cursor.execute(sql, commit) # commit_list_1为state=1的更新列表
print('state=1数据更新成功')
conn.commit()
elif not tradedf_2.empty:
sql = "update gold_transaction set A=%s, state=%s, B=%s, C=%s, D=%s, E=%s, F=%s where id=%s"
for commit in commit_list_2:
cursor.execute(sql, commit) # commit_list_2为state=2的更新列表
print('state=2数据更新成功')
conn.commit()
elif not tradedf_3.empty:
sql = "update gold_transaction set state=%s where id=%s"
for commit in commit_list_3:
cursor.execute(sql, commit) # commit_list_3为state=-1的更新列表
print('state=-1数据更新成功')
conn.commit()
except:
logging.exception("exception")
conn.rollback()
print('数据回滚')
conn.close()
您可以尝试一些方法来提高代码的性能：
使用 SQL Server 的批量复制功能而不是 executemany：
executemany 不是在 SQL Server 中更新大量数据的最有效方法。 相反，您可以使用 SQL Server 的批量复制功能，该功能专为高性能数据传输而设计。 pymssql 库通过 pymssql.bulkcopy 模块支持此功能。
以下是如何使用批量复制更新数据的示例：
import pymssql
from pymssql import bulkcopy
conn = pymssql.connect(host='xxx', port="xxx", user="xxx", password="xxx", database="xxx",charset="utf8", autocommit=False)
if conn:
print('连接数据库成功!')
with conn.cursor() as cursor:
try:
if not tradedf_1.empty:
with bulkcopy(conn) as bc:
bc.batch_size = 1000 # adjust batch size as needed
bc.destination_table_name = "gold_transaction"
bc.add_rows(commit_list_1)
bc.commit()
print('state=1数据更新成功')
elif not tradedf_2.empty:
with bulkcopy(conn) as bc:
bc.batch_size = 1000 # adjust batch size as needed
bc.destination_table_name = "gold_transaction"
bc.add_rows(commit_list_2)
bc.commit()
print('state=2数据更新成功')
elif not tradedf_3.empty:
sql = "update gold_transaction set state=(%s) where id=(%s) "
cursor.executemany(sql,commit_list_3) # commit_list_1为state=-1的更新列表
print('state=-1数据更新成功')
conn.commit()
except:
logging.exception("exception")
conn.rollback()
print('数据回滚')
conn.close()
2 使用参数化查询而不是字符串格式：
参数化查询是在 Python 中执行 SQL 语句的一种更安全、更高效的方式，因为它们可以防止 SQL 注入攻击并允许 SQL Server 优化查询执行计划。 您可以将参数化查询与游标对象的执行方法一起使用，如下例所示：
if not tradedf_1.empty:
sql = "update gold_transaction set A=%s,state=%s,B=%s,C=%s,D=%s,E=%s,F=%s where id=%s"
cursor.execute(sql, commit_list_1)
conn.commit()
print('state=1数据更新成功')
3 优化数据库更新：
如果您的数据库没有针对更新进行优化，它可能会降低查询的性能。 您可以尝试为更新语句的 WHERE 子句中使用的列建立索引，并在更新操作期间禁用触发器和约束。 但是，更改数据库模式时要小心，因为它们可能会产生意想不到的后果。
我希望这些技巧可以帮助您提高代码的性能！如果对您有帮助，请给与采纳，谢谢。
请问一下如何分批录入，比如每批1000条？----感谢大家，我分别尝试了大家的办法，除了csv批量插入的建议暂时没想清楚怎么测试，其他大部分都按大家的方法测试了，但和之前的速度没有太大变化，还要1分钟左右，可能我还没运用清楚，在试试
以下答案基于ChatGPT与GISer Liu编写：
在你的代码中，你使用了pymssql库与SQL Server数据库进行交互，并使用executemany()方法来更新数据。这个方法是批量执行SQL语句的，它可以提高数据更新的效率，但是数据量过大时仍然会影响性能。
为了提高性能，可以尝试以下几个方法：
- 批量提交事务：你的代码中使用了autocommit=False来关闭自动提交，但是你在每次更新后都手动提交了事务。这样做会影响性能，因为每次提交事务都需要与数据库进行交互，建议将多个update操作组合在一个事务中，然后再统一提交，这样可以减少与数据库的交互次数。
- 使用批量更新：如果你更新的数据量很大，可以尝试使用SQL Server的批量更新功能。这样可以将多个update操作合并成一个操作，从而减少与数据库的交互次数。你可以通过调整executemany()方法的batchsize参数来设置每批次更新的数据量。
- 调整数据库连接：在代码中，你使用了pymssql库连接到SQL Server数据库。可以尝试使用其他库来连接数据库，比如pyodbc、pypyodbc等，看是否能提高性能。
- 调整表结构和索引：如果你的表结构和索引不合理，可能会影响更新性能。可以尝试优化表结构和索引，比如添加索引、删除不必要的索引、修改表结构等。
最后，建议使用SQL Server Profiler或其他数据库性能监控工具来分析数据库的性能瓶颈，找到问题的根源，再针对性地进行优化。
executemany反复往返于MySQL服务器，然后它需要解析查询，执行查询并返回结果。即使它更复杂，这可能比在单个SQL语句中执行所有操作的速度慢10倍。
但是，对于INSERT，this表示它将做聪明的事情，并为您构造多行INSERT，从而提高了效率。
因此，IN(1,2,3,...)比UPDATE;UPDATE;UPDATE...更有效率
如果您具有ID序列，那么最好说WHERE id BETWEEN 1 and 1000。这是因为它可以简单地扫描行，而不是从头开始查找每一行。 （我假设id已被索引，可能是PRIMARY KEY。）
另外，您可能正在使用使每个插入/更新/删除成为其自己的“事务”的设置。这给每个UPDATE增加了很多开销。在这种情况下，这可能不是理想的。我怀疑您希望整个1000行更新都是原子的。
底线：仅对必须单独运行的（a）executemany或（b）语句使用INSERTs。
该回答引用ChatGPT
您可以尝试使用executemany的批量更新功能来提高更新效率。此外，您还可以尝试使用SQL语句中的JOIN操作，减少更新操作的次数，以及使用索引来提高查询效率。
1、使用SQL中的bulk insert/update。这样可以大幅度减少交互次数，加快运行速度；
2、使用临时表处理大量数据。将数据插入临时表后，可以构建更复杂的语句进行更新，可以减少数据库处理次数；
3、采用分布式存储技术，实现数据库分片处理，提高更新的效率；
4、使用 GUI 编程工具的多线程并发编程，可以编写多线程程序，用于处理大量数据；
5、有时，增加硬件，比如添加更多的硬盘、内存、CPU资源，都可能大大提高数据库性能。