0

I'm facing below error while using pandas to_sql for entering data into my SQL SERVER database:

ERROR:

(pymssql.ProgrammingError) (102, b"Incorrect syntax near '('.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

Code:

connection = sqlalchemy.create_engine('mssql+pymssql://' + self.username + ":" + self.password + "@" +self.Server + "/" + self.Database, echo=False, isolation_level="AUTOCOMMIT" )
chunks = pandas.read_csv(self.input_file, chunksize=100000)
for chunk in chunks:
    chunk.to_sql(table_name, if_exists='append', con=connection, index=False)

Table Structure:

CREATE TABLE [table_name] (
  [index] varchar(200) NOT NULL,
  [ColumnA (unit)] varchar(255),
  [ColumnB] float,
  CONSTRAINT PK_table_name PRIMARY KEY NONCLUSTERED ([index])
)

NOTE: I feel like the error is because of the '(' in column names. But I just can't remove it from column names in the database.

Any other answer/suggestion is welcome. Advance thanks.

Sumit Pandey
  • 448
  • 2
  • 9
  • 1
    Why would you ever want to call a column `[ColumnA (unit)]`? Now every query you write you will run into this issue. Do yourself a favor and don't do that and you'll thank yourself later. Same with a column named index. As for fixing this, where is your SQL query? You haven't included that. – Jacob H Feb 13 '18 at 13:37
  • @JacobH That's my trouble here, The team fetching from database is the Tableau team and they want it like that :( – Sumit Pandey Feb 13 '18 at 13:41
  • Fair enough, it's your funeral. – Jacob H Feb 13 '18 at 13:42
  • Tell the team fetching the data that if they want their column to have a nice name for presentation that the value belongs at the presentation layer. We don't modify the database so it looks pretty to the end user. – Sean Lange Feb 13 '18 at 14:33

0 Answers0