Insert data into sql after joining two excel data from python

Question

I have joining data into FinalData variable.Now i want to inset that data into sql table.

    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')
   ##  Joining on data1 and data ##
    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

How to insert FinalData into sql table through python ? In finaldata i have joining data of two excel. — Neeraj Yadav
– Neeraj Yadav, Commented Jan 28, 2020 at 12:56
I am not familiar with Python but you will need a connection to the SQL server before you can execute any queries. Try Google.There are plenty of examples. — Peter Smith
– Peter Smith, Commented Jan 28, 2020 at 13:00
Does this answer your question? insert data into MSSQL server using python — Peter Smith
– Peter Smith, Commented Jan 28, 2020 at 13:00
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() — Neeraj Yadav
– Neeraj Yadav, Commented Jan 29, 2020 at 11:27

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

I got the solution.

for loop

for india,data in FinalData.iterrows():
AddresID=data["AddressID"]
PostalCod=data["PostalCode"]
AddressTypeD=data["AddressTypeID"]
values=(AddresID,PostalCod,AddressTypeD)
cursor.execute(query, values)
conn.commit()
conn.close()

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 29, 2020 at 11:34

Neeraj Yadav

2412 gold badges5 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

Insert data into sql after joining two excel data from python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related