How can I do a batch insert into an Oracle database using Python?

by

Here’s what I’ve come up with which appears to work well (but please comment if there’s a way to improve this):

# build rows for each date and add to a list of rows we'll use to insert as a batch 
rows = [] 
numberOfYears = endYear - startYear + 1
for i in range(numberOfYears):
    for j in range(12):
        # make a date for the first day of the month
        dateValue = datetime.date(startYear + i, j + 1, 1)
        index = (i * 12) + j
        row = (stationId, dateValue, temps[index], precips[index])
        rows.append(row)

# insert all of the rows as a batch and commit
ip = '192.1.2.3' 
port = 1521
SID = 'my_sid'
dsn = cx_Oracle.makedsn(ip, port, SID)
connection = cx_Oracle.connect('username', 'password', dsn)
cursor = cx_Oracle.Cursor(connection)
cursor.prepare('insert into ' + database_table_name + ' (id, record_date, temp, precip) values (:1, :2, :3, :4)')
cursor.executemany(None, rows)
connection.commit()
cursor.close()
connection.close()

More Related Contents:

  1. Python module “cx_Oracle” module could not be found
  2. Behavior of exec function in Python 2 and Python 3
  3. How do I create test and train samples from one dataframe with pandas?
  4. Dead simple example of using Multiprocessing Queue, Pool and Locking
  5. Python: URLError:
  6. Python: count repeated elements in the list [duplicate]
  7. Parse a tuple from a string?
  8. What is the difference between len() and sys.getsizeof() methods in python?
  9. Why does this not work as an array membership test? [duplicate]
  10. How to redirect stdout to both file and console with scripting?
  11. Download and save PDF file with Python requests module
  12. Why does Python’s multiprocessing module import __main__ when starting a new process on Windows?
  13. Making an API call in Python with an API that requires a bearer token
  14. How to: Macports select python
  15. Python regex for unicode capitalized words
  16. Multiple conditions with if/elif statements [duplicate]
  17. FTPS with Python ftplib – Session reuse required
  18. How to get Windows short file name in python?
  19. How to plot a chart in the terminal
  20. How do I flatten a list of lists/nested lists? [duplicate]
  21. Read .doc file with python
  22. Making HTTP HEAD request with urllib2 from Python 2
  23. I want to multiply two columns in a pandas DataFrame and add the result into a new column
  24. What is different between makedirs and mkdir of os?
  25. Python list greater than number [duplicate]
  26. Python pickle protocol choice?
  27. How to pass all Python’s traffics through a http proxy?
  28. Python Pandas: remove entries based on the number of occurrences
  29. Pathname too long to open?
  30. How do I get a decimal value when using the division operator in Python?

Leave a Comment