小白python Excel导入sql数据库问题 求助！

We.

代码如下

[Python] 纯文本查看 复制代码

#coding=utf-8
import pymssql
import xlrd

data = xlrd.open_workbook('fenlei.xls')
table = data.sheet_by_name('Sheet1')

nrows =table.nrows

conn = pymssql.connect(host='localhost', server='KDA', user='sa', password='', database='test', port='1433')
cur = conn.cursor()

for i in range(1, nrows):
    x = table.row_values(i)
    id = x[0]
    ia = x[1]
    cur.execute("INSERT INTO Table_1(test,test1) VALUES (id, ia)")
    conn.commit()
cur.close()
conn.close()

报错信息

[Python] 纯文本查看 复制代码

C:/Users/admin/PycharmProjects/untitled/car.py:2: DeprecationWarning: Using or importing the ABCs from 'collections' instead of from 'collections.abc' is deprecated since Python 3.3, and in 3.9 it will stop working
  import pymssql
Traceback (most recent call last):
  File "src\pymssql.pyx", line 448, in pymssql.Cursor.execute
  File "src\_mssql.pyx", line 1064, in _mssql.MSSQLConnection.execute_query
  File "src\_mssql.pyx", line 1095, in _mssql.MSSQLConnection.execute_query
  File "src\_mssql.pyx", line 1228, in _mssql.MSSQLConnection.format_and_run_query
  File "src\_mssql.pyx", line 1639, in _mssql.check_cancel_and_raise
  File "src\_mssql.pyx", line 1683, in _mssql.maybe_raise_MSSQLDatabaseException
_mssql.MSSQLDatabaseException: (128, b'The name "id" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:/Users/admin/PycharmProjects/untitled/car.py", line 17, in <module>
    cur.execute("INSERT INTO Table_1(test,test1) VALUES (id, ia)")
  File "src\pymssql.pyx", line 468, in pymssql.Cursor.execute
pymssql.OperationalError: (128, b'The name "id" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n')

单元格能遍历到数据，写入数据库也能写 但是通过Excel导入的这个逻辑 还没弄清楚。
希望大佬们能给指点一下

We.

网上查阅了很多代码，一个一个去思考。得出来了一个成功可行的办法

[Python] 纯文本查看 复制代码

#coding=utf-8
import pymssql
import xlrd

data = xlrd.open_workbook('fenlei.xls')
table = data.sheet_by_name('Sheet1')

nrows =table.nrows

conn = pymssql.connect(host='localhost', server='KDA', user='sa', password='', database='test', port='1433')
cur = conn.cursor()

for i in range(1, nrows):
    x = table.row_values(i)
    sql = "INSERT INTO Table_1(test,test1) VALUES (%s,%s)"
    value = (x[0], x[1])
    cur.execute(sql, value)
    conn.commit()
cur.close()
conn.close()

santus36

sql语句那边有问题，改成"INSERT INTO Table_1(test,test1) VALUES ({}, {})".format(id,ia)试试

We.

santus36 发表于 2020-4-24 20:57
sql语句那边有问题，改成"INSERT INTO Table_1(test,test1) VALUES ({}, {})".format(id,ia)试试

这个我运行的时候没问题。 新的问题来了。

[Python] 纯文本查看 复制代码

#coding=utf-8
import pymssql
import xlrd

data = xlrd.open_workbook('fenlei.xls')
table = data.sheet_by_name('Sheet1')

nrows =table.nrows

conn = pymssql.connect(host='localhost', server='KDA', user='sa', password='', database='UFDATA_001_2020', port='1433')
cur = conn.cursor()

for i in range(1, nrows):
    x = table.row_values(i)
    sql = "INSERT INTO InventoryClass(cInvCCode, cInvCName, iNvVGrade, bInvCEnd) VALUES (%s,%s,%s,%s)"
    IA = x[0]
    IB = x[1]
    IC = x[2]
    ID = x[3]
    value = (IA, IB, IC, ID)
    cur.execute(sql, value)
    conn.commit()
cur.close()
conn.close()

换个写法就又不行我要吐了都 ，字段都是对应好了的 然后就报错

[Python] 纯文本查看 复制代码

Traceback (most recent call last):
  File "C:/Users/admin/PycharmProjects/untitled/car.py", line 21, in <module>
    cur.execute(sql, value)
  File "src\pymssql.pyx", line 465, in pymssql.Cursor.execute
pymssql.ProgrammingError: (207, b"Invalid column name 'iNvVGrade'.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n")

进程已结束，退出代

We.

We. 发表于 2020-4-24 22:26
这个我运行的时候没问题。 新的问题来了。

[Python] 纯文本查看 复制代码

#coding=utf-8
import pymssql
[/quote]

我承认是我蠢了 没看到有个字段名字差了个字母 改好了以后新的问题又来了 大佬们帮忙看看 
[mw_shl_code=python,true]#coding=utf-8
import pymssql
import xlrd

data = xlrd.open_workbook('fenlei.xls')
table = data.sheet_by_name('Sheet1')

nrows =table.nrows

conn = pymssql.connect(host='localhost', server='KDA', user='sa', password='', database='UFDATA_001_2020', port='1433')
cur = conn.cursor()

for i in range(1, nrows):
    x = table.row_values(i)
    sql = "INSERT INTO InventoryClass(cInvCCode, cInvCName, iINvCGrade, bInvCEnd) VALUES (%s,%s,%s,%s)"
    IA = x[0]
    IB = x[1]
    IC = x[2]
    ID = x[3]
    value = (IA, IB, IC, ID)
    cur.execute(sql, value)
    conn.commit()
cur.close()
conn.close()

看看报错信息

[Python] 纯文本查看 复制代码

C:\Users\admin\AppData\Local\Programs\Python\Python38\python.exe C:/Users/admin/PycharmProjects/untitled/car.py
C:/Users/admin/PycharmProjects/untitled/car.py:2: DeprecationWarning: Using or importing the ABCs from 'collections' instead of from 'collections.abc' is deprecated since Python 3.3, and in 3.9 it will stop working
  import pymssql
Traceback (most recent call last):
  File "src\pymssql.pyx", line 450, in pymssql.Cursor.execute
  File "src\_mssql.pyx", line 1064, in _mssql.MSSQLConnection.execute_query
  File "src\_mssql.pyx", line 1095, in _mssql.MSSQLConnection.execute_query
  File "src\_mssql.pyx", line 1228, in _mssql.MSSQLConnection.format_and_run_query
  File "src\_mssql.pyx", line 1639, in _mssql.check_cancel_and_raise
  File "src\_mssql.pyx", line 1683, in _mssql.maybe_raise_MSSQLDatabaseException
_mssql.MSSQLDatabaseException: (2627, b"Violation of PRIMARY KEY constraint 'aaaaaInventoryClass_PK'. Cannot insert duplicate key in object 'dbo.InventoryClass'.DB-Lib error message 20018, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\n")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:/Users/admin/PycharmProjects/untitled/car.py", line 21, in <module>
    cur.execute(sql, value)
  File "src\pymssql.pyx", line 467, in pymssql.Cursor.execute
pymssql.IntegrityError: (2627, b"Violation of PRIMARY KEY constraint 'aaaaaInventoryClass_PK'. Cannot insert duplicate key in object 'dbo.InventoryClass'.DB-Lib error message 20018, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\n")

进程已结束，退出代码 1

百度说是pymssql.IntegrityError:（2627，b“违反主键约束aaaaaa inventoryclass_PK”。无法在对象“dbo.InventoryClass”中插入重复的键。DB Lib错误消息20018，严重性为14:\n常规SQL Server错误：检查来自SQL Server的消息\n“

这个怎么解决？ 超出了我的理解了。

We.

We. 发表于 2020-4-24 22:36
我承认是我蠢了 没看到有个字段名字差了个字母 改好了以后新的问题又来了 大佬们帮忙看看
[mw_shl_code ...

好了，问题已经解决，是我傻了。因为数据库里面我测试的时候放了一条测试信息没有删掉导致插入重复的键。 成功了。