使用MS SQL ODBC插入的语法错误

I am trying to insert into a Microsoft SQL Server database using the Microsoft ODBC 11 SQL Driver for Linux from a python program using pyodbc. (That's a mouthful) So I have tested the connect, the subquery, and using dateadd in separate queries. Those all work, but when I try to put them all together into this insert statement I get a syntax error at the second to last ')' on the second to last line. What am I missing here?

我正在尝试从使用pyodbc的python程序中使用针对Linux的Microsoft ODBC 11 SQL驱动程序插入到Microsoft SQL服务器数据库中。(这有点拗口)所以我已经在单独的查询中测试了connect、子查询和dateadd。这些都可以，但是当我尝试将它们放在这个insert语句中时，会在倒数第二行出现语法错误)。我错过了什么?

cursor.execute("INSERT INTO room_use_log VALUES ("+
       "(SELECT bldg_flr_spc_id FROM bldg_flr_spc WHERE rm_atl_nbr="+
         rooms[y]["Name"] +"),'t','f',dateadd(ms,"+
         str(int(str(r.get(rooms[y]["Name"]))[1:])) +", '1970-01-01'),"+
         " dateadd(ms,"+
         str(int(time.time())) +
         ", '1970-01-01')" #<----that one
        )

This is the entire error:

这是整个错误:

Traceback (most recent call last):
  File "DBGetRedis.py", line 59, in <module>
    ", '1970-01-01')"
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 11 for SQL Server]
[SQL Server]Incorrect syntax near ')'. (102) (SQLExecDirectW)")

1 个解决方案

#1

Appears that you're missing the closing paren in your INSERT statement. The final paren in the string is closing dateadd, you still need another to close the VALUES section. Try:

在插入语句中，显示您丢失了结束符。字符串中的最后一个paren是关闭dateadd，您仍然需要另一个来关闭VALUES部分。试一试:

cursor.execute("INSERT INTO room_use_log VALUES ("+
   "(SELECT bldg_flr_spc_id FROM bldg_flr_spc WHERE rm_atl_nbr='"+
     rooms[y]["Name"] +"'),'t','f',dateadd(ms,"+
     str(int(str(r.get(rooms[y]["Name"]))[1:])) +", '1970-01-01'),"+
     " dateadd(ms,"+
     str(int(time.time())) +
     ", '1970-01-01'))" #<----that one
    )

Edit: Added the single quotes that were mentioned in the comment below to fix the statement.

编辑:添加下面注释中提到的单引号来修复语句。

#1