Insert Python variable into Oracle Database

Question

I can successfully insert an entire DataFrame into my Oracle table, but I cannot figure out how to insert a single variable into my it. I've failed using both bind variables and regular Python variables.

My variable year prints out 2014, in this case, because my code parses the year out of the document I run it on; and it'll eventually have to be run on 100's of documents.

So my SQL statements are as follows:

cursor = con.cursor()
exported_data = [tuple(x) for x in df_Quota.values]
new_variable = year
sql_query = ("INSERT INTO ROUGHTABLE(species, date_posted, stock_id, pounds, money, sector_name, ask)" "VALUES (:1, :2, :3, :4, :5, 'NEFS 2', '1')")
year_command = ("INSERT INTO ROUGHTABLE(trade_year)" "VALUES (:1)")
cursor.executemany(sql_query, exported_data)
cursor.executemany(year_command, new_variable)
con.commit() 

cursor.close()
con.close()

and that fails with the error cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number... It also fails when I simply try:

year_command = ("INSERT INTO ROUGHTABLE(trade_year)" "VALUES (year)")
cursor.execute(year_command)

with the error cx_Oracle.DatabaseError: ORA-00984: column not allowed here...

So I am very confused as to how to get a single Python variable into my Oracle table. Ideally I would like for the value of my variable year to export to the column trade_year in my Oracle table. Any help solving this would be greatly appreciated.

df_Quota:

   AvailableQuota DatePosted    ID LiveWeightPounds   price
0         GOM COD       5/20  1724             2328  $9,000
1        GOM HADD       5/20  1724              445  $9,000
2          GOM BB       5/20  1724             3007  $9,000
3        GREYSOLE       5/20  1724              850  $9,000
4            DABS       5/20  1724             3101  $9,000
5          GOM YT       5/20  1724             1995  $9,000
6         GBE COD       5/20  1578              538   $1.00
7           GB BB       5/20  1578             1755   $0.20
8           GB YT       5/20  1578              243   $1.00
9          SNE BB       5/20  1578              490   $0.45
10         SNE YT       5/20  1578              153   $0.50
11         GOM BB       5/20  1578             3965   $0.15
12          Whake       5/20  1578             2727   $0.20
13           POLL       5/20  1578             9227   $0.01
14            RED       5/20  1578            15060   $0.01
15        GBE COD       5/20   310              825  15,000
16        GBW COD       5/20   310             9033  15,000
17           DABS       5/20   310            12419  15,000
18          WHAKE       5/20   310             3120  15,000
19           POLL       5/20   310            65234  15,000
20            RED       5/20   310            76610  15,000
21         SNE BB       5/20   310             2121  15,000
22         GOM BB       5/20   310             7285  15,000

Curious, why don't you use pandas' to_sql for the dataframe insert? — Parfait
– Parfait, Commented Jan 26, 2017 at 20:32
I didn't actually know Pandas had that function...I'm kind of a noob with Pandas and only recently discovered it can also read_html — theprowler
– theprowler, Commented Jan 27, 2017 at 13:58

BobC · Accepted Answer · 2017-01-26 19:21:11Z

2

Here is a simple example:

import cx_Oracle
import csv,sys

dsnStr = cx_Oracle.makedsn("xxxxxxxxxxxxx", "1521", "xxxxxxxx")
con = cx_Oracle.connect(user="scott", password="tiger", dsn=dsnStr)
print "Database version " + con.version

myCur = con.cursor()
myCur.prepare( 'insert into roughtable( trade_year ) values ( :theYear )' )

myYear='2016'
myCur.execute(None, {'theYear':myYear})
con.commit()
con.close()

answered Jan 26, 2017 at 19:21

BobC

4,4421 gold badge14 silver badges16 bronze badges

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

7 Comments

theprowler Over a year ago

But instead of setting myYear equal to '2016' I would like to set it to a variable because in each document I run my code on, it'll parse out whatever year is in the document, so that value will change.

BobC Over a year ago

@theprowler Yes, I understand that. myYear is a variable. So you can set it to anything you want in your program. What I was showing you was how to bind a program variable to the sql statement.

BobC Over a year ago

Now, if you want to write a bunch of myYears to the database, you can populate an array (list I think in python lingo) and bind the array, and performs an "array inset" ( in Oracle lingo)

theprowler Over a year ago

Ohhhhh ok ok ok that makes a lot of sense. Give me a sec and I'll try it all right now, thanks

BobC Over a year ago

@theprowler I'll add an array insert example in about an hour (I need to check in for a flight :) )

|

Collectives™ on Stack Overflow

Insert Python variable into Oracle Database

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related