Connecting python3 on Ubuntu to SQL Server 2014

Question

I am trying to retrieve SQL data on a Linux Server (Ubuntu 16.04.2) in a Python 3.5 script using the ODBC Driver 13 for SQL Server. Running the script on SQL Server and Python in windows goes fine. Running the script in Python in Linux throws a SQL Server Syntax error:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '0x107c'. (102) (SQLExecDirectW)")

As I add or remove columns it changes '0x107c' to a different character suggesting that it is not a single illegal character, but several. With a more limited amount of columns the script even runs (excluded [Order Type text] and [Order Nr]).This leads me to suspect something goes wrong in character set conversion. What am I doing wrong and how do I fix it?

Python3.5:

import pandas as pd
import pyodbc

#Set parameters
sql_file = 'file.sql'

#Define methods
def SQLDataToDataframe(filename):
    fd = open('file.sql','r')
    content = fd.read()
    fd.close()
df = pd.read_sql(content, connection)
return df

#Import Data from SQL
connection = pyodbc.connect('Driver={ODBC Driver 13 for SQL Server};'
                            'Server=Server;'
                            'Database=DB;'
                            'uid=User;pwd=Password')

 dataframe = SQLDataToDataframe(sql_file)

file.sql:

SELECT [ID]
  ,[Company Code]
  ,[Description]
  ,[Order Category]
  ,[Order Category Text]
  ,[Order Type]
  ,[Order Type text]
  ,[Order Nr]
FROM [TABLE]

Imre_G · Accepted Answer · 2017-02-16 15:29:37Z

2

Solution for future reference. Don't use pyodbc on Linux to connect to MS SQL Server. Use pymssql instead. Instructions here: https://learn.microsoft.com/en-us/sql/connect/python/pymssql/step-1-configure-development-environment-for-pymssql-python-development.

answered Feb 16, 2017 at 15:29

Imre_G

2,5251 gold badge20 silver badges36 bronze badges

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

2 Comments

alys Over a year ago

I see the opposite recommendation from the same website. "There are several python SQL drivers available. However, Microsoft places its testing efforts and its confidence in pyodbc driver." learn.microsoft.com/en-us/sql/connect/python/…

Imre_G Over a year ago

Hi Shoof, I am not sure about that. On Windows machines I always use pyodbc. However, for this purpose pymssql works better. Don't ask me why :)

Collectives™ on Stack Overflow

Connecting python3 on Ubuntu to SQL Server 2014

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

Related