CODEWITHSUNDEEP
pythonpython-3.xpandaspyodbc
Ratha
Ratha

Reputation: 9702

Why to_sql is not working with pyodbc in pandas?

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?

Upvotes: 3

Views: 5354

Answers (2)

GiovaniSalazar
GiovaniSalazar

Reputation: 2094

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

Upvotes: 2

Oleg O
Oleg O

Reputation: 1065

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.

Upvotes: 0

Related Questions