How to push data from sheet excel file using pyodbc, to_sql, and sql server managment studio express r2 using windows authentication

Question

I am trying to push data from excel file to sql_server express management studio 2008 using pandas and pyodbc, as I need to push data by creating a table in a database myDB

and here's my code

import pyodbc
import pandas as pd
import os

import sqlalchemy
from sqlalchemy import create_engine

# connect db
engine = create_engine('mssql+pyodbc://WWX542337CDCD\SMARTRNO_EXPRESS/myDB?driver=SQL+Server+Native+Client+11.0')
cursor = engine.raw_connection().cursor()


mydir = (os.getcwd()).replace('\\', '/') + '/'

lte_details = pd.read_excel(r'' + mydir + 'MNM_Rotterdam_5_Daily_Details-20191216081027.xlsx', sheet_name='LTE Details')

lte_details.columns = lte_details.columns.str.replace(' ', '')

# reading and insert one file at a time
for file in os.listdir('.'):
    # only process excels files
    file_basename, extension = file.split('.')
    if extension == 'xlsx':
        lte_details.to_sql(file_basename.lower(), con=engine.connect(), if_exists='replace')

and I find this error:

Traceback (most recent call last):
  File "C:/Users/mwx825326/PycharmProjects/MyReference/test.py", line 22, in <module>
    file_basename, extension = file.split('.')
ValueError: not enough values to unpack (expected 2, got 1)

and this is my connection Any one have any idea how to solve this issue?

Did you try sqlalchemy engine? docs.sqlalchemy.org/en/13/faq/connections.html — E. Zeytinci
– E. Zeytinci, Commented Dec 27, 2019 at 19:49

E. Zeytinci · Accepted Answer · 2019-12-27 21:14:36Z

1

IMHO, you can try use to sqlalchemy connection. Because con in pd.read_sql uses sqlalchemy engine.

from sqlalchemy import create_engine

engine = create_engine('mssql+pyodbc://**server**/**db**?driver=SQL+Server+Native+Client+11.0')
cursor = engine.raw_connection().cursor()

lte_details.to_sql(file_basename.lower(), con=engine.connect(), if_exists='replace')

You can try this for split,

file_basename, extension = tuple(file.split('.'))

edited Dec 27, 2019 at 21:14

answered Dec 27, 2019 at 20:04

E. Zeytinci

2,6332 gold badges23 silver badges40 bronze badges

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

7 Comments

Mahmoud Al-Haroon Over a year ago

thanks alot for your answer, but for my case I am using windows authentication no user name and password as when I tried to use your code I found this error

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

please check edits, I am not using SQL Authentication here

E. Zeytinci Over a year ago

Can you check this link? stackoverflow.com/questions/37161574/… I think you need to specify driver.

Mahmoud Al-Haroon Over a year ago

to be clear when I am connecting to server I an using Server type Database Engine, Servername, Authentication Windows Authentication like in the posted Picture, Check edits please

E. Zeytinci Over a year ago

Can you try adding .raw_connection() to the end of the expression? Like this,

engine = sqlalchemy.create_engine('mssql://*server_name*\\SQLEXPRESS/*database_name*?trusted_connection=yes').raw_connection()

Mahmoud Al-Haroon Over a year ago

it gave me the same error I got before, as I posted now :(

|

Collectives™ on Stack Overflow

How to push data from sheet excel file using pyodbc, to_sql, and sql server managment studio express r2 using windows authentication

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related