Syntax Error with Insert using MS SQL ODBC

Question

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?

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)")

schumacherj · Accepted Answer · 2015-01-06 21:12:01Z

4

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:

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.

edited Jan 6, 2015 at 21:12

schumacherj

1,3246 gold badges19 silver badges33 bronze badges

answered Jan 6, 2015 at 20:51

G. J.

666 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

schumacherj Over a year ago

Ya... I was pairing up parentheses, but the ones in quotes are hard to check. There is one more error in my statement, mention to get the upvote. Add a "'" after the equal sign in the where clause and on the other side.

FlipperPA Over a year ago

Just another thing to consider: it is a much better practice for avoiding problems like SQL injection to use bound parameters. The documentation is here: code.google.com/p/pyodbc/wiki/Cursor But for a brief, short example, you could run your select, grab it into variables, and then perform the statement like this: cursor.execute("INSERT INTO room_use_log (thisroom, thatroom, otherroom) VALUES (?, ?, ?)", pyv_thisroom, pyv_thatroom, pyv_otherroom)") where anything starting with pyv* is a Python variable from the select. Sorry for syntax errors, currently sleep deprived!

Collectives™ on Stack Overflow

Syntax Error with Insert using MS SQL ODBC

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest