CODEWITHSUNDEEP
pythonsqloracle-databasecx-oracle
kgs_doc
kgs_doc

Reputation: 11

Python/SQL Insert Into Missing Expression

Using Python, trying to write a row of values into a SQL table. The values (n=27) are a mix of data types (text, int, date) and using a custom built function to execute the transaction to the SQL table. I have a list of fields:

oracleFieldsList = ["REQ_ID", "MEMBER_ID", "DATA_ID", "DTTM_REQ", "DTTM_UPD", "DESCR_PROJECTION", "DESCR_LOCATION", "MEDIA_TRANSFER",
"FILE_FORMAT", "TAX_SUPPORTED", "REQ_NOTES", "USER_PROJECT", "DTTM_STAFF", "STAFF_ID", "DTTM_DELIVER", "FILE_SIZE", "SIZE_TYPE",
"NO_FILES", "DTTM_CANCEL", "CANCEL_BY_ID", "DWNLD_URL", "LABOR_HOURS", "LABOR_TOTAL", "MEDIA_QUANT", "MEDIA_TOTAL", "INVOICE_DATE", "ADMIN_ID"]
oracle_fields = ",".join(oracleFieldsList)

and a list of variables storing values for the one row:

entryValuesList = [str(REQ_ID), MEMBER_ID, DATA_ID, str(DTTM_REQ), str(DTTM_UPD), str(DESCR_PROJECTION), DESCR_LOCATION, MEDIA_TRANSFER,
str(FILE_FORMAT), TAX_SUPPORTED, REQ_NOTES, str(USER_PROJECT), DTTM_STAFF, STAFF_ID, DTTM_DELIVER, FILE_SIZE, SIZE_TYPE,
NO_FILES, DTTM_CANCEL, CANCEL_BY_ID, DWNLD_URL, LABOR_HOURS, LABOR_TOTAL, MEDIA_QUANT, MEDIA_TOTAL, INVOICE_DATE, ADMIN_ID]
entry_values = ",".join(entryValuesList)

and then execute the transaction with:

cmd = "INSERT INTO PERSONS (" + oracle_fields + """) VALUES\
        (""" + entry_values + ")"

def updateDatabase(query):
    try:    
        import cx_Oracle
        print(query)

        #submit query
        connstr = 'XXXXXXX'
        conn = cx_Oracle.connect('XX', 'XXX', connstr)
        curs = conn.cursor()
        curs.execute(query)
        conn.commit()
        conn.close()
    except Exception as e:
        print(str(e))

updateDatabase(cmd)

I keep getting the "ORA-00936: missing expression" or the "ORA-00917: missing comma"

I've double-checked for missing commas, unended parens, etc, and have not seen anything obvious causing this error. Any insight would be appreciated.

Google, trial/error, reading other posts, etc.

Upvotes: 0

Views: 61

Answers (2)

Anthony Tuininga
Anthony Tuininga

Reputation: 7086

Better yet would be to use bind variables. This eliminates potential security issues (sql injection) and eliminates issues with quoting, type conversions, etc. Using a simplified example:

fields_list = ["REQ_ID", "MEMBER_ID", "INVOICE_DATE"]
oracle_fields = ",".join(fields_list)

data = [2154, 1234, datetime.date.today()]
oracle_binds = ",".join(f":{i + 1}" for i in range(len(data)))
cmd = f"INSERT INTO PERSONS ({oracle_fields}) values {oracle_binds})"
cursor.execute(cmd, data)

Upvotes: 1

chrisbyte
chrisbyte

Reputation: 1608

I am not sure exactly how you got the cmd example in your comments, but when I run your code with some simple lines I don't get the final statement wrapped in commas for the string columns.

(This is a simplified example using your code as a start)

REQ_ID = 23288
MEMBER_ID = ""
MEDIA_TRANSFER = "Network Transfer (FTP)"

oracleFieldsList = ["REQ_ID", "MEMBER_ID", "MEDIA_TRANSFER"]
entryValuesList = [str(REQ_ID), MEMBER_ID, MEDIA_TRANSFER]

oracle_fields = ",".join(oracleFieldsList)
entry_values = ",".join(entryValuesList)

cmd = "INSERT INTO PERSONS (" + oracle_fields + """) VALUES (""" + entry_values + ")"

print(cmd)
# INSERT INTO PERSONS (REQ_ID,MEMBER_ID,MEDIA_TRANSFER) VALUES (23288,,Network Transfer (FTP))

What you are after is something like this?

entry_values = str(entryValuesList).strip("[]")

cmd = "INSERT INTO PERSONS (" + oracle_fields + """) VALUES (""" + entry_values + ")"

print(cmd)
# INSERT INTO PERSONS (REQ_ID,MEMBER_ID,MEDIA_TRANSFER,TAX_SUPPORTED) VALUES ('23288', '', 'Network Transfer (FTP)', 0)

Upvotes: 1

Related Questions