Skip to content

Reading excel file to dataframe and inserting records to SQL table using SQLAlchemy

import pandas as pd
import pyodbc as db
import sqlalchemy, urllib

file_directory = 'path to excel file'
# generate sqlalchemy engine
params = urllib.parse.quote_plus('SQL SERVER CONNECTION STRING')
engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params, fast_executemany=True)
# print(
# print (df.columns)
def main():
      print ('inserting excel into sql')
      df = pd.read_excel(file_directory)
      # add column before inserting records to db
      df['file_name'] = os.path.basename(file_directory)
      df['create_by'] = 'script name'
      df.to_sql('Table Name', con = engine, if_exists = 'append', index=False )
      print ('successfully imported sheet!')
    except db.Error as ex:
      print (ex)
if __name__ == '__main__':
See also  Equalize the Array - Hackerrank Challenge - Java Solution

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.