Insert data in oracle database in python

Question

I can't insert data into database using a dynamic query in python script

def execute_query(self, qo):
    query_string = "INSERT INTO " +dep_table+ " (client, sis, entity_name_1, entity_name_2, flag_dep,process, flag_dep_det) VALUES (%s, %s, %s, %s, %s, %s, %s)" % ("'CO'","'"+qo.db_src+"'","'"+qo.table_src+"'","'"+qo.table_des+"'","'"+qo.check_func+"'","'"+qo.table_des+"'","'NULL'")+";"
    cursor.execute(query_string)

I got this error:

ERROR: Failed to set dependencies informations : ORA-00933: SQL command not properly ended

The connection to the database is okay, but I can't insert.

You shouldn't use the % operator to insert values into your SQL. xkcd.com/327 — khelwood
– khelwood, Commented Oct 30, 2019 at 21:41
%s as a placeholder in SQL is not the same as the string-formatting parameter in Python — roganjosh
– roganjosh, Commented Oct 30, 2019 at 21:42
query_string = "INSERT INTO " + dep_table + " (client, sis, entity_name_1, entity_name_2, flag_dep,process, flag_dep_det) VALUES (%s, %s, %s, %s, %s, %s, %s);", ("CO", qo.db_src, qo.table_src, qo.table_des, qo.check_func, qo.table_des, None). You can't parameterize table names, so you'll need to vet that against the schema of the db separately. — roganjosh
– roganjosh, Commented Oct 30, 2019 at 21:44
I tried your suggestion, I got ERROR: Failed to set dependencies informations : expecting string, unicode or buffer object — Siham MB
– Siham MB, Commented Oct 30, 2019 at 21:50

Gary Myers · Accepted Answer · 2019-10-30 23:00:58Z

1

Drop the semi-colon at the end of the string you are creating / executing.

It shouldn't be part of the SQL statement, rather used in some client tools to indicate the end of a statement so that the client can send it to the database to be executed.

answered Oct 30, 2019 at 23:00

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

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

Comments

Siham MB · Accepted Answer · 2019-10-31 09:45:06Z

1

I found the solution to the problem connection.commit()

answered Oct 31, 2019 at 9:45

Siham MB

3910 bronze badges

Comments

Ergi Nushi · Accepted Answer · 2019-10-31 14:01:49Z

0

You can use format method in Python like below:

def execute_query(self, qo):
    query_string = "INSERT INTO {0} (client, sis, entity_name_1, entity_name_2, flag_dep,process, flag_dep_det) VALUES ('{1}', '{2}', '{3}', '{4}', '{5}', '{6}', {7})".format(dep_table, 'CO', qo.db_src, qo.table_src, qo.table_des, qo.check_func, qo.table_des, 'NULL')
    cursor.execute(query_string)

answered Oct 31, 2019 at 14:01

Ergi Nushi

8731 gold badge6 silver badges18 bronze badges

Collectives™ on Stack Overflow

Insert data in oracle database in python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related