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 stackoverflow.com/questions/15750711/… — Oleg O
– Oleg O, Commented Dec 10, 2019 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.
– Paul D., Commented Dec 10, 2019 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
– Ratha, Commented Dec 11, 2019 at 0:02
@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
– Ratha, Commented Dec 11, 2019 at 23:05

GiovaniSalazar · Accepted Answer · 2019-12-11 23:14:19Z

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

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: sqlalche.me/e/rvf5)
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
check this link kite.com/python/docs/sqlalchemy.dialects.mssql.pyodbc ...how is calling the DRIVER , using import urllib

Oleg O · Accepted Answer · 2019-12-10 23:44:42Z

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?

Collectives™ on Stack Overflow

Why to_sql is not working with pyodbc in pandas?

2 Answers 2

6 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Linked

Related