CODEWITHSUNDEEP
sql-serverpandassqlalchemypyodbcubuntu-18.04
ilmatic9000
ilmatic9000

Reputation: 51

Connection issues using pandas.Dataframe.to_sql and sqlalchemy?

I've been trying to insert a Pandas dataframe into an SQL Server I have running on Docker. I'm using python 3.6.9 on Ubuntu 18.04.



# import the module
from sqlalchemy import create_engine
import pyodbc

# create sqlalchemy engine
engine = create_engine("mssql+pyodbc://{user}:{pw}@{sv}:1433/{db}"
                       .format(user="sa",
                               pw="sa",
                               db="fuel_check_hist",
                               sv='192.168.86.39'))

# Insert whole DataFrame into MySQL
df.to_sql('prices', con=engine, if_exists = 'append', chunksize = 1000, index = False)

gives me the following error

InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)') (Background on this error at: http://sqlalche.me/e/rvf5)

I'm at a loss as to what to try next.

Note the following code does work so I do know that my instance of SQL Server is available and pyodbc can connect to it.

import pyodbc 
server = '192.168.86.39' 
database = 'fuel_check_hist' 
username = 'sa' 
password = 'sa' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

#Sample select query
cursor.execute("SELECT @@version;") 
row = cursor.fetchone() 
while row: 
    print(row[0])
    row = cursor.fetchone()

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
Sep 24 2019 13:48:23
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS) **

Upvotes: 1

Views: 1299

Answers (1)

ilmatic9000
ilmatic9000

Reputation: 51

Ok figured it out, I needed to add a string to specify the driver in the connection parameters. '?driver=ODBC+DRIVER+17+for+SQL+Server'

# create sqlalchemy engine
engine = create_engine("mssql+pyodbc://{user}:{pw}@{sv}:1433/{db}?driver=ODBC+DRIVER+17+for+SQL+Server"
                       .format(user="sa",
                               pw="sa",
                               db="fuel_check_hist",
                               sv='192.168.86.39'))

Helpful links

Upvotes: 2

Related Questions