pyodbc 插入 sql [英] pyodbc insert into sql

问题描述

我使用 MS SQL express 数据库.我可以连接并获取数据.但是插入数据不起作用:

I use a MS SQL express db. I can connect and fetch data. But inserting data does not work:

cursor.execute("insert into [mydb].[dbo].[ConvertToolLog] ([Message]) values('test')")

我没有收到任何错误，但没有在表中插入任何内容.在我获取数据后，直接获取插入的行.但什么也没有保存.

I get no error but nothing is inserted into the table. Directly after I fetch the data the inserted row is fetched. But nothing is saved.

在 MS SQL Server Management Studio 中，插入确实有效.

In MS SQL Server Management Studio the insertion does work.

推荐答案

您需要提交数据.每个 SQL 命令都在一个事务中，必须提交事务才能将事务写入 SQL Server，以便其他 SQL 命令可以读取它.

You need to commit the data. Each SQL command is in a transaction and the transaction must be committed to write the transaction to the SQL Server so that it can be read by other SQL commands.

在 MS SQL Server Management Studio 下，默认设置是允许自动提交，这意味着每个 SQL 命令立即生效，您无法回滚.

Under MS SQL Server Management Studio the default is to allow auto-commit which means each SQL command immediately works and you cannot rollback.

示例来自 pyodbc 入门文档

首先打开数据库并设置游标

First opening the database and set up a cursor

import pyodbc

# Specifying the ODBC driver, server name, database, etc. directly
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass')

# Create a cursor from the connection
cursor = cnxn.cursor()

文档中的插入示例则为

# Do the insert
cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
#commit the transaction
cnxn.commit()

或更好地使用参数

cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
cnxn.commit()

正如文件所说

注意对 cnxn.commit() 的调用.您必须调用 commit 否则您的更改将丢失！ 当连接关闭时，任何挂起的更改都将回滚.这使得错误恢复非常容易，但你必须记住调用 commit.

Note the calls to cnxn.commit(). You must call commit or your changes will be lost! When the connection is closed, any pending changes will be rolled back. This makes error recovery very easy, but you must remember to call commit.

这篇关于pyodbc 插入 sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！