Python Bulk Insert Data From excel to sql server | Dataframe to SQL Table | Sqlalchemy Engine

# Sqlalchemyengine #sqlalchemy #insertbulkdatatosqlserver #exceltosqlserver #pythonbukupload #sqlalchemyexecutesqlquries #pandastosqlserver #dataframetosqltable #bukinsertintomssql #insertdataframetomssqltable #executemany #executequeryusing #sqlalchemyengine

00:00 how to Insert Bulk Data from excel to Sql table
03:00 insert Dataframe to sql data table
05:00 create sqlalchemy engine for sql server
07:00 python bulk insert to sql table
08:00 Sqlalchemy execute sql query

[Raj-phpy]

Very clear explanation.
Thanks for the video and your time and effort.

[dhruvshah]

Thank you so much for this!
It was really helpful.

[nareshchoudhary]

How can I insert more than 1 Excel file at once ?

[nareshchoudhary]

Can we create table and insert at the same time ?

[ranganaik]

Hi All, I have 4 millions records iy's taking more than half an hour time to load how to improve it

[venkateshyadala]

Hello,

I am trying to insert 1.8L records to sql using python pyodbc. Here is the below script which i used to insert the data into sql. Please let me know what are the changes need to make.

Code: 1

# Establish the Python SQL Server Connection
cnxn_str = ('Driver={SQL Server Native Client 11.0};'
'Server=;'
'Database=;'
'Trusted_Connection=yes;')
cnxn = py.connect(cnxn_str)

cursor = cnxn.cursor() # allows to execute queries
# cursor.execute() # code place where we right the sql queries
# cnxn.commit() # This is action code, which will execute the the query update in the code: cursor.execute()

# Checking the LFM1 Text table exists or not and uploading the data accordingly
if cursor.tables(table='Temp_LFM1_Table',
value = "Exist"
else:
value = "Doesn't Exist"

print('Table Status = ' + value)

# Creating the table for LFM1 Data
cursor.execute('''
CREATE TABLE Temp_LFM1_Table (
System TEXT NOT NULL, Supplier TEXT NOT NULL, Purch_Organization TEXT NULL, Terms_of_Payment TEXT NULL, Incoterms TEXT NULL, Incoterms_Part_2 TEXT NULL,
Order_currency TEXT NULL, Created_By TEXT NULL, Created_On date NULL, Purch_block_for_purchasing_organization TEXT NULL,
TEXT NULL, GR_Based_Inv_Verif TEXT NULL, Eval_receipt_sett TEXT NULL, Active_Inactive Text NULL
);
''')
cnxn.commit()


# DAS System
for row_count in range(0, das.shape[0]):
chunk = das.iloc[row_count:row_count + 1, :].values.tolist()
tuple_of_tuples = tuple(tuple(x) for x in chunk)
cursor.executemany(
'''INSERT INTO Temp_LFM1_Table (System, Supplier, Purch_Organization, Terms_of_Payment, Incoterms, Incoterms_Part_2, Order_currency, Created_By,
Created_On, Purch_block_for_purchasing_organization, Delete_flag_for_purchasing_organization, GR_Based_Inv_Verif,
Eval_receipt_sett, Active_Inactive)

VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''',

tuple_of_tuples
)

cnxn.commit()

Code: 2

# Variable names
Server=''
Database=''
Driver='SQL Server Native Client 11.0'
Database_Con = 'mssql+pyodbc://' + Server + '/' + Database + + Driver

# Creating an engine connection
engine = create_engine(Database_Con, fast_executemany=True)
engine = engine.connect()


das.to_sql('Temp_LFM1_Table',
engine,
schema='dbo',
if_exists='append',
index=False,
)
Note: das is the dataframe where read and make changes to the data