-1
cnxn = pyobdc.connect(...)
cursor = cnxn.cursor()

sql = 'create table if not exists tab1 (id integer primary key, entry varchar (200), exit varchar (200))'

cursor.execute(sql)
cnxn.commit()

in the above code, the query "sql" has the keyword "exit" as a column name which throws up an error. I therefore made the change of enclosing the keyword in quotes like

sql = 'create table if not exists tab1 (id integer primary key, entry varchar (200), "exit" varchar (200))'

and even tried

sql = 'create table if not exists tab1 (id integer primary key, entry varchar (200), `exit` varchar (200))'

Which still gives me the error:

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Serveer Driver][SQLServer]Incorrect syntax near '"'. (102) (SQLExecDirectW)")

or accordingly,

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Serveer Driver][SQLServer]Incorrect syntax near '`'. (102) (SQLExecDirectW)")

Please help me understand how to overcome this problem without having to change the column name, thanks!

Dale K
  • 25,246
  • 15
  • 42
  • 71
Dhanush Gontla
  • 9
  • 2
  • 1
    SQL Server doesn't support `CREATE TABLE IF NOT EXISTS` syntax. – Thom A Sep 14 '21 at 18:13
  • 2
    As for the keyword, don't use (reserved) keywords for object names; it's bad practice. Use a different name. – Thom A Sep 14 '21 at 18:13
  • 1
    Finally, for the last error, the backtick (`\``) isn't a delimit identifier in T-SQL. T-SQL's delimit identifier are brackets (`[]`), or you can use the ANSI delimit identifier double quotes (`"`). – Thom A Sep 14 '21 at 18:15
  • `create table if not exists` works for all the other tables. while I know it is a bad practice to use reserved words for object names, in this particular instance, it is a client engagement requirement. please help me get around this problem. – Dhanush Gontla Sep 14 '21 at 18:17
  • 2
    *"works for all the other tables"* I doubt this; again, SQL Server does *not* support `CREATE TABLE IF NOT EXISTS`. If that syntax is supported, you aren't using SQL Server. – Thom A Sep 14 '21 at 18:17
  • my bad, my query reads `if not exists create table...`. and thanks, enclosing it in brackets works! – Dhanush Gontla Sep 14 '21 at 18:22
  • 1
    `IF NOT EXISTS CREATE TABLE`, followed by a table definition is also not valid T-SQL syntax. – Thom A Sep 14 '21 at 18:27
  • Just to note that if you are using SQL Server >= 2016 you could change your workflow a bit to first drop the table if it exists, then create it. `DROP TABLE IF EXISTS tab1` followed by your create table statement like `CREATE TABLE tab1(...` – Jason Cook Sep 15 '21 at 14:31

1 Answers1

1

As I said in the comments, firstly SQL Server does not support CREATE TABLE IF NOT EXISTS. See this question for specific answers on that.

Next your second statement. This won't generate the error you describe. In fact, if you do try to run it, you get the errors:

Incorrect syntax near the keyword 'if'. Incorrect syntax near 'tab1'.

Again, this is because of comment 1. If you try the latter statement, with the backticks (`) you get the same identical errors, you don't get the error about the backtick. Though, as I also mentioned, the backticks are not a valid delimit identifier in T-SQL. Either use T-SQL own, brackets ([]), or ANSI SQLs, double quotes (").

Finally, to fix the problem, just don't use a reserved keyword for your object name. Perhaps something like this:

IF OBJECT_ID(N'dbo.tab1') IS NULL CREATE TABLE tab1 (id integer PRIMARY KEY, UserEntry varchar (200), UserExit varchar (200));
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • @charlieface I intentionally made it a "one-liner" as the OP was using them in their application code as one-liners too. – Thom A Sep 14 '21 at 19:18
  • Ah OK sorry. It's just unreadable though. Could use `"""` quotes and put newlines inside the string. – Charlieface Sep 14 '21 at 19:20