Why to_sql is not working with pyodbc in pandas?

Question

I have an excel file. Im importing that to dataframe and trying to update a database table using the data.

import pyodbc

def get_sale_file():


    try:
            cnxn = pyodbc.connect('DRIVER=ODBC Driver 17 for SQL Server;'
                      'SERVER=' + server + ';DATABASE=' + database + ';UID=' + uname + ';PWD=' + pword,
                      autocommit=False)

        files = os.listdir(ile_path)
        df = pd.DataFrame()
        for f in files:
            if (f.endswith('.xlsx') or f.endswith('.xls')):
                df = pd.read_excel(os.path.join(sap_file_path, f))
                df.to_sql('temptable', cnxn, if_exists='replace')

        query = "UPDATE MList AS mas" + \
                " SET TTY = temp.[Territory Code] ," + \
                " Freq =temp.[Frequency Code]," + \

                " FROM temptable AS temp" + \
                " WHERE mas.SiteCode = temp.[ri a]"

When I execute above code block; I get

1/12/2019 10:19:45 AM  ERROR: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW)")

Am i trying in right way? Does panads have any other function to update mssql table other than to_sql? How can I overcome above error?

Edit Should i have to create temptable beforehand to load datafarme? If that so, my file contains 100s of column, it may vary..(except few columns) How could I make sure pandas to load only few columns to temptable?

You can use sqlalchemy, see this on how to make the connection https://stackoverflow.com/questions/15750711/connecting-to-sql-server-2012-using-sqlalchemy-and-pyodbc — Oleg O, Dec 10 '19 at 23:30
Are you trying to access an sqlite database with a SQL Server driver? The error is saying that sqlite_master is not valid. If so Python has built in sqlite support and there is no need to use ODBC. — Paul D., Dec 10 '19 at 23:55
@GiovaniSalazar sorry my bad, I think I have to create temptable in database before loading file to dataframe?If that so, my file contains 100s of column, it may vary..(except few columns) How could I make sure pandas to load only few columns to temptable? — Ratha, Dec 11 '19 at 00:02
Check the parameter 'con' un this link https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html — GiovaniSalazar, Dec 11 '19 at 00:37
Here you have an example https://stackoverflow.com/questions/47402225/python-sqlalchemy-trying-to-write-pandas-dataframe-to-sql-server-using-to-sql — GiovaniSalazar, Dec 11 '19 at 00:40
I wrote the code here .. https://pastebin.com/wJvBVm1i , I recommend you first run the script alone. — GiovaniSalazar, Dec 11 '19 at 05:54
@GiovaniSalazar using urllib+sqlalchemy+pyodbc it is working thanks a lot. You could post your pastebin answer here. urllib.parse.quote_plus is working — Ratha, Dec 11 '19 at 23:05

GiovaniSalazar · Accepted Answer · 2019-12-11T23:14:19.097

1

According the guide of pandas.DataFrame.to_sql (https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html) , the connection expect a connection type sqlalchemy.engine.Engine or sqlite3.Connection , then is necesary change your code using a connection like this :

import sqlalchemy
import pyodbc
cnxn = sqlalchemy.create_engine("mssql+pyodbc://<username>:<password>@<dsnname>")


df.to_sql("table_name", cnxn,if_exists='replace')

UPDATE : Using urllib

import urllib
import pyodbc

params = urllib.quote_plus("DRIVER={ODBC Driver 17 for SQL Server};SERVER=yourserver;DATABASE=yourdatabase ;UID=user;PWD=password")
cnxn = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

df.to_sql("table_name", cnxn,if_exists='replace')

edited Dec 11 '19 at 23:14

answered Dec 11 '19 at 04:35

GiovaniSalazar

1,999
2
8
15

When i switch to sqlalchemey to get connection parameter i get ERROR: (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) – Ratha Dec 11 '19 at 05:17
Im using MAC machine – Ratha Dec 11 '19 at 05:34
but you have installed the ODBC ? – GiovaniSalazar Dec 11 '19 at 05:35
What library then I need to install? Im running python program in the virtual environment. I have pyodbc inplace already, which is working for another DB process – Ratha Dec 11 '19 at 05:37
check this link https://kite.com/python/docs/sqlalchemy.dialects.mssql.pyodbc ...how is calling the DRIVER , using import urllib – GiovaniSalazar Dec 11 '19 at 05:49
Any chance to get the code example from the link running for a as/400 database? If I'm trying to use pandas.to_sql with the connection from the link, I cannot provide a schema and the execution of the generated sql statement fails – TheDude Jan 20 '22 at 15:33

score 0 · Answer 2 · answered Dec 10 '19 at 23:44

You can try another package, too, instead of pyodbc, e.g. pytds or adodbapi. The first one is very simple, with adodbapi the connection config looks like

from adodbapi import adodbapi as adba

raw_config_adodbapi = f"PROVIDER=SQLOLEDB.1;Data Source={server};Initial Catalog={database};trusted_connection=no;User ID={user};Password={password};"
conn = adba.connect(raw_config_adodbapi, timeout=120, autocommit=True)

Besides, it seems like the parameters in the connections string in pyodbc should be enclosed in {}, but maybe it's not mandatory.

I think issue is , i have to create temptable beforehand to load datafarme? If that so, my file contains 100s of column, it may vary..(except few columns) How could I make sure pandas to load only few columns to temptable? — Ratha, Dec 11 '19 at 00:02

Why to_sql is not working with pyodbc in pandas?

2 Answers2