Search code examples
sql-serverpyodbcunixodbc

Inserting Rows to SQL SERVER using pyodbc

I am having terrible time connecting to SQL Server using pyodbc from the linux machine (Ubuntu 16.04).

conn = pyodbc.connect(r'DRIVER={FreeTDS};PORT=**; SERVER=**; DATABASE=**;UID=AA;PWD=hfghj;')
curr = conn.cursor()
curr.fast_executemany = True
query = "INSERT INTO dbo.STG_CONTACTABILITY_SCORE VALUES (?" + ",?"*21 + ")"
sql_data = list(map(tuple, i.values))
curr.executemany(query, sql_data)

I am getting following error:

('HY004', '[HY004] [FreeTDS][SQL Server]Invalid data type (0) (SQLBindParameter)')

The same insert query works from my windows laptop when I change the connection string to:

conn = pyodbc.connect(r'DRIVER={SQL Server};PORT=**; SERVER=**; DATABASE=**; UID=AA; PWD=hfghj;')

If I change the Driver from FreeTDS to SQL SERVER on my Linux machine I get the following error:

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)")

I searched for answers but there isn't much out there that could solve mine problem. What is the driver to connect to SQL SERVER from Linux machine?

Solution

  • What is the driver to connect to SQL SERVER from Linux machine?

    If you install Microsoft's ODBC driver as described in

    Installing the Microsoft ODBC Driver for SQL Server on Linux and macOS

    then you can use DRIVER=ODBC Driver 17 for SQL Server or DRIVER=ODBC Driver 13 for SQL Server depending on which version you choose.