Inserting data into Oracle table with Python

Question

I know this is a very simple question but I am stumped. I am successful when using Python to insert a chunk of data into my Oracle table via SQLDeveloper, but it fails when I also try to insert just a few additional values.

I have a DataFrame of 23 rows and 5 columns, and I can easily export that to my Oracle table. I also would like to fill one column of my table with a single value, "NEFS 2", and another column with another value, "1", and I cannot execute both exports successfully.

Here is my export code:

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

cursor.close()
con.close()

It fails on line cursor.executemany(sql_query, exported_data) with the error TypeError: expecting string or bytes object

Do I need to break up my sql_query into two different statements? If so, do I then need to have two different cursor.executemany statements? Or is it a larger problem than that? Forgive me for being a novice with SQL. And thanks for any help.

Yes, basically these are two separate SQL statements.

BobC
– BobC

2017-01-24 15:14:55 +00:00
Commented Jan 24, 2017 at 15:14 — BobC
– BobC, Commented Jan 24, 2017 at 15:14

Vasily Shapovalov · Accepted Answer · 2017-01-24 15:21:42Z

1

As far as I understand, you want to fill ROUGHTABLE (species, date_posted, stock_id, pounds, money, sector_name, ask) with values (:1, :2, :3, :4, :5, 'NEFS 2', 1). If that's the case, SQL required is:

INSERT INTO ROUGHTABLE(species, date_posted, stock_id, pounds, money, sector_name, ask) 
VALUES (:1, :2, :3, :4, :5, 'NEFS 2', 1)

And line 3 of python code should be:

sql_query = "INSERT INTO ROUGHTABLE (species, date_posted, stock_id, pounds, money, sector_name, ask) VALUES (:1, :2, :3, :4, :5, 'NEFS 2', 1)"

answered Jan 24, 2017 at 15:21

Vasily Shapovalov

363 bronze badges

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

3 Comments

theprowler Over a year ago

Wow that really was so simple. I really should've been able to figure that out on my own. Thanks for taking the time to answer :)

theprowler Over a year ago

Hey, if you don't mind, I don't really want to ask an entire new StackOverflow question for this really simple problem I'm having... I want to fill ROUGHTABLE with an additional value, but this one won't be the same all the time like 'NEFS 2' and '1' were, it's parsed from the document but it's NOT in the DataFrame 'df_Quota.values' so I'm confused how to also export this value... I imagine it'd be something like INSERT INTO ROUGHTABLE(species, , , , trade_year) VALUES(:1, , , , 'NEFS 2', 1, date) since it's stored in the variable date, but that fails... Can you help?

Vasily Shapovalov Over a year ago

You should ask a question, I think. The short answer to it is that SQL would be INSERT INTO ROUGHTABLE(species, , , , trade_year) VALUES(:1, , , , 'NEFS 2', 1, :6) and you should append date to your dataset in python. Mind that inserting dates in sql databases is tricky.

Collectives™ on Stack Overflow

Inserting data into Oracle table with Python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related