Connection issues using pandas.Dataframe.to_sql and sqlalchemy?

Question

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) **

off topic , but you should NOT be using the 'sa' user to connect to your database. Any DBA that knows their stuff will have disabled the 'sa' user.... — Mitch Wheat
– Mitch Wheat, Commented Dec 17, 2019 at 3:33
I'm just learning about connecting Python to MSSQL in my sandbox. Nothing here is connected to the internet and I'm definitely not using any of this environment for production. — ilmatic9000
– ilmatic9000, Commented Dec 17, 2019 at 3:39

ilmatic9000 · Accepted Answer · 2019-12-18 01:21:00Z

2

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

answered Dec 18, 2019 at 1:21

ilmatic9000

511 silver badge9 bronze badges

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

Collectives™ on Stack Overflow

Connection issues using pandas.Dataframe.to_sql and sqlalchemy?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related