Python Forum
Loading HUGE data from Python into SQL SERVER
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Loading HUGE data from Python into SQL SERVER
#1
Hi All,

I have used the below python code to insert the data frame from Python to SQL SERVER database.But when I am using one lakh rows to insert then it is taking more than one hour time to do this operation. Could I get an optimized Python code for my task?


import time
start_time = time.time()
import pyodbc
from sqlalchemy import create_engine
import urllib

params = urllib.parse.quote_plus(r'DRIVER={SQL Server};SERVER=ROSQC50;DATABASE=PADB;Trusted_Connection=yes')
conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
engine = create_engine(conn_str)
df.to_sql(name='DummyTodaynow',con=engine, if_exists='append',index=False)
print(" %s seconds ---" % (time.time() - start_time))
Appreciate for your help on this!

Thanks,
Sandeep
Reply
#2
Obviously df.to_sql (and sqlalchemy?) do not make bulk import, but execute individual insert for each row
see https://github.com/pandas-dev/pandas/issues/8953
and also https://stackoverflow.com/questions/3381...r-database
Reply
#3
Thanks for the reply Buran. I am not able to understand the codes that is in the links which you mentioned.
Any other code would be helpful?

Thanks,
Sandeep
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  [solved] how to speed-up huge data in an ascii file ? paul18fr 4 1,208 May-16-2023, 08:36 PM
Last Post: paul18fr
  Finding the median of a column in a huge CSV file markagregory 5 1,733 Jan-24-2023, 04:22 PM
Last Post: DeaD_EyE
  loading data astral_travel 7 3,638 Dec-12-2022, 03:29 PM
Last Post: astral_travel
  visualizing huge correation matrix erdemath 3 1,964 Oct-13-2021, 09:44 AM
Last Post: erdemath
  huge and weird values after applying some calculations karlito 2 2,131 Dec-13-2019, 08:32 AM
Last Post: karlito
  Loading .csv data using Pandas zaki424160 1 2,859 Jul-15-2019, 09:48 AM
Last Post: perfringo
  loading a csv file into python from pandas. Variable is not recognized vijjumodi 2 2,877 Apr-19-2019, 04:09 AM
Last Post: kus
  Problem with saving data and loading data to mysql kirito85 4 3,865 Feb-08-2019, 10:53 AM
Last Post: kirito85

Forum Jump:

User Panel Messages

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