使用python将数据插入MSSQL服务器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30589571/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
insert data into MSSQL server using python
提问by amol desai
I am trying to insert data to the sql server table using following code,
我正在尝试使用以下代码将数据插入到 sql server 表中，
import pyodbc
user='sa'
password='PC#1234'
database='climate'
port='1433'
TDS_Version='8.0'
server='192.168.1.103'
driver='FreeTDS'
con_string='UID=%s;PWD=%s;DATABASE=%s;PORT=%s;TDS=%s;SERVER=%s;driver=%s' % (user,password, database,port,TDS_Version,server,driver)
cnxn=pyodbc.connect(con_string)
cursor=cnxn.cursor()
cursor.execute("INSERT INTO mytable(name,address) VALUES (%s,%s)",('thavasi','mumbai'))
cnxn.commit()
Its giving me the following error while executing,
它在执行时给我以下错误，
Traceback (most recent call last):
File "sql.py", line 26, in <module>
cursor.execute("INSERT INTO mytable(name,address) VALUES (%s,%s)",('thavasi','mumbai'))
pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 2 parameters were supplied', 'HY000')
I have checked the syntax for the insert statement its correct. So what causes this error?
我已经检查了插入语句的语法是否正确。那么是什么导致了这个错误呢？
回答by itzMEonTV
stringformatting done here is wrong. Try this
string这里做的格式化是错误的。尝试这个
cursor.execute("INSERT INTO mytable(name,address) VALUES (%s,%s)" %('thavasi','mumbai'))
Look into http://www.diveintopython.net/native_data_types/formatting_strings.html
查看http://www.diveintopython.net/native_data_types/formatting_strings.html
回答by frlan
If are using placeholders in a string, you need to use % operator in favor of using a parameterlist with ,. so your call should look like:
如果在字符串中使用占位符，则需要使用 % 运算符来支持使用带有 , 的参数列表。所以你的电话应该是这样的：
cursor.execute("INSERT INTO mytable(name,address) VALUES (%s,%s)" %('thavasi','mumbai'))
回答by Shruti Srivastava
You forgot the %sign before the values also the %smust be in inverted commas
您忘记了%值之前的符号，也%s必须用引号引起来
try this:
尝试这个：
cursor.execute('INSERT INTO mytable(name,address) VALUES ("%s","%s")',% ('thavasi','mumbai'))
cursor.execute('INSERT INTO mytable(name,address) VALUES ("%s","%s")',% ('thavasi','mumbai'))
回答by Dsw Wds
cursor.execute("INSERT INTO mytable(name,address) VALUES (?,?)",('thavasi','mumbai'))
use ?instead of %in pyodbc module
使用? 而不是pyodbc 模块中的%
回答by Paula
try this:
尝试这个：
a1 = row[0]
a2 = row[1]
valores = (a1, a2)
cursor2.execute('INSERT INTO [CENTRAL_ALARM].[dbo].[EEE](E3TimeStamp, CaptacaoSemAgua) VALUES (?,?)', valores)
回答by PKInd007
This worked for me.
这对我有用。
cursor.execute("INSERT INTO tablename(field1,field2) VALUES(?,?) ", (data1,data2))
回答by Sergej Steinhauer
This works with SQL Server 2012 and later and integrated security
这适用于 SQL Server 2012 及更高版本和集成安全性
import pyodbc
conn = pyodbc.connect('Driver={SQL Server Native Client 11.0};'
r'Server=YourServer\YourInstance;'
'Database=YourDatabase;'
'Trusted_Connection=yes;') #integrated security
cursor = conn.cursor()
SQLCommand = ("INSERT INTO PY.PackageTables (PackageName,PackageTables) VALUES (?,?);")
Values = ['Test1','Test3']
#Processing Query
cursor.execute(SQLCommand,Values)
conn.commit()
print("Data Successfully Inserted")
conn.close()
