Python Forum
Insert data into sql after joining two excel data from python
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Insert data into sql after joining two excel data from python
#1
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
Reply
#2
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()
Reply
#3
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 .
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Help with to check an Input list data with a data read from an external source sacharyya 3 318 Mar-09-2024, 12:33 PM
Last Post: Pedroski55
  need help with data analysing with python and sqlite Hardcool 2 300 Jan-30-2024, 06:49 AM
Last Post: Athi
  Better python library to create ER Diagram by using pandas data frames as tables klllmmm 0 989 Oct-19-2023, 01:01 PM
Last Post: klllmmm
  Bulk loading of data using python shivamsvmsri 2 615 Sep-28-2023, 09:04 AM
Last Post: shivamsvmsri
  Python loop for posting data to web marciokoko 10 1,479 Aug-26-2023, 02:17 AM
Last Post: deanhystad
  How to detect abnormal data in big database python vanphuht91 5 1,064 Jun-27-2023, 11:22 PM
Last Post: Skaperen
  Copy data from Excel and paste into Discord (Midjourney) Joe_Wright 4 1,926 Jun-06-2023, 05:49 PM
Last Post: rajeshgk
  Insert 10gb csv files into sql table via python mg24 2 1,833 Apr-28-2023, 04:14 PM
Last Post: snippsat
  Python Serial: How to read the complete line to insert to MySQL? sylar 1 783 Mar-21-2023, 10:06 PM
Last Post: deanhystad
  Reading data from excel file –> process it >>then write to another excel output file Jennifer_Jone 0 1,047 Mar-14-2023, 07:59 PM
Last Post: Jennifer_Jone

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020