SQLAlchemy writing to SQL Server: incorrect syntax

[Lorenzo R]

Hi, having an error when writing a dataframe to db and can't find a solution out there. Have used sqlalchemy with sql server extensively before but can't crack this one. Help would be greatly appreciated!

To reproduce:

from sqlalchemy import create_engine

import urllib

connection_string = "Driver={};Server={};Database={};UID={};PWD={};encoding='latin1'".format('SQL Server',

                                                                               config['server'],

                                                                               config['database'],

                                                                               config['uid'],

                                                                               config['pwd'])

connection_string = urllib.parse.quote(connection_string)

connection_string = "mysql+pyodbc:///?odbc_connect=%s" % connection_string

engine = create_engine(connection_string, convert_unicode=True)

df = pd.DataFrame(np.random.randn(6, 4), columns=list('ABCD'))

df.to_sql("test_table", engine, if_exists='replace', index=False)

Error on the first run:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'LIKE'. (156) (SQLExecDirectW)") [SQL: "SHOW VARIABLES LIKE 'character_set%%'"]

Error on second run:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '`'. (102) (SQLExecDirectW)") [SQL: 'DESCRIBE `test_table`']

Stack:

python=3.6

sqlalchemy=1.1.13

pyodbc=4.0.17

Sql Server=2008

Pandas=0.20.3

[Mike Bayer]

On Mon, Dec 18, 2017 at 11:35 AM, Lorenzo R <lo.r...@gmail.com> wrote:
> Hi, having an error when writing a dataframe to db and can't find a solution
> out there. Have used sqlalchemy with sql server extensively before but can't
> crack this one. Help would be greatly appreciated!
>
> To reproduce:
>
> from sqlalchemy import create_engine
> import urllib
>
> connection_string =
> "Driver={};Server={};Database={};UID={};PWD={};encoding='latin1'".format('SQL
> Server',
>
> config['server'],
>
> config['database'],
>
> config['uid'],
>
> config['pwd'])
> connection_string = urllib.parse.quote(connection_string)
> connection_string = "mysql+pyodbc:///?odbc_connect=%s" % connection_string

this should be "mssql+pyodbc", those statements are for a MySQL database :)

totally a mistake I make sometimes :)

> engine = create_engine(connection_string, convert_unicode=True)
>
> df = pd.DataFrame(np.random.randn(6, 4), columns=list('ABCD'))
>
> df.to_sql("test_table", engine, if_exists='replace', index=False)
>
> Error on the first run:
> sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000',
> "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
> near the keyword 'LIKE'. (156) (SQLExecDirectW)") [SQL: "SHOW VARIABLES LIKE
> 'character_set%%'"]
>
> Error on second run:
> sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000',
> "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
> near '`'. (102) (SQLExecDirectW)") [SQL: 'DESCRIBE `test_table`']
>
> Stack:
> python=3.6
> sqlalchemy=1.1.13
> pyodbc=4.0.17
> Sql Server=2008
> Pandas=0.20.3
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Lorenzo R]

no way thank you so much! promise to myself: contribute by adding code to help people with this error in the future!