Python Forum

How to insert FinalData into sql table through python ? In finaldata i have joining data of two excel.

import pandas as pd
import pandas.io.sql
import pyodbc
server = 'Lppp-5CD812F42\SQLEXPRESS'
db = 'HDb'
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + server + ';DATABASE=' + db + ';Trusted_Connection=yes')
cursor = conn.cursor()
data = pd.read_excel('C:\\Users\\neeraj.ya\\Xerox\\Python\\Address.xlsx')
data1 = pd.read_excel('C:\\Users\\neeraj.ya\\Xerox\\Python\\BAddress.xlsx')
  
FinalData =data.join(data1, on='AddressID', how='inner', lsuffix='_left', rsuffix='_right')
print(FinalData)
query1 = """
    CREATE TABLE [dbo].[pythtbl1] (
    AddressID varchar(255), 
    PostalCode varchar(255),    
    AddressTypeID varchar(255)     
    )"""
query = """INSERT INTO [dbo].[pythtbl1] (AddressID, PostalCode, AddressTypeID) VALUES (?,?,?)"""    
try:
    cursor.execute(query1)
    conn.commit()
except pyodbc.ProgrammingError:
    pass

I got the solution.

try:
cursor.execute(query1)
conn.commit()
except pyodbc.ProgrammingError:
pass
for india,data in FinalData.iterrows():# Dont remove India TypeError: tuple indices must be integers or slices, not str
AddresID=data["AddressID"]
PostalCod=data["PostalCode"]
AddressTypeD=data["AddressTypeID"]
values=(AddresID,PostalCod,AddressTypeD)
cursor.execute(query, values)
conn.commit()
conn.close()

FinalData =data.join(data1, on='AddressID', how='inner', lsuffix='_left', rsuffix='_right')

data= FinalData.split(",")
cursor.execute(query1, data[0], data[1], data[2]) 

Always dont create the table in the fly . Create a table in advance and use Insert/ update statement alone .