0

I have this group of python scripts where I do an API call, merge the resulting df together, then output to a table in an Oracle db. This exact script is working perfectly in three other scripts configured same, except a different API, but in this particular script, this error is getting thrown. I read up on binds, but I can't see how I'm doing it incorrectly for a tuple. Thanks in advance for your time.

sf_joined = pd.merge(sf_opp, sf_account,on=["CustID","CustID"])
    # sf_joined.to_csv('sf_joined.csv', index=False)
    
    # sf_types = sf_joined.dtypes
    # print(sf_types)
    
    char_columns = sf_joined.select_dtypes(include=['object']).columns
    
    for col in char_columns:
        if col not in  ['rundate','Amount','Estimated_GC','Probability','MDC','DaysOpen']:
            sf_joined[col] = sf_joined[col].fillna('')
            # sf_joined[col] = sf_joined[col].map(lambda x: x.encode('utf-8'))
            sf_joined[col] = sf_joined[col].map(lambda x: x[:1000])
    
    pw = '****'
    db_con = cx_Oracle.connect('mktg', pw, "prd-bia-db-***.o******.com:1521/BIPRD", encoding = "UTF-8", nencoding = "UTF-8")
    cur = db_con.cursor()
    print(db_con.version)
    
    
    cur.execute('drop table cs_salesforce')
    create_opps = """create table cs_salesforce(
        rundate date,
        sfoppid varchar(500)
    )
    """
    cur.execute(create_opps)
    
    all_opps = []
    
    for x in sf_joined.itertuples():
        all_opps.append(x[1:])
    insert_statement = """insert into cs_salesforce(rundate,sfoppid)values(:1, :2)"""
    
    cur.executemany(insert_statement, all_opps)
    db_con.commit()

Error:

runfile('C:/python_scripts_prod/cs_salesforce.py', wdir='C:/python_scripts_prod')
18.3.0.0.0
Traceback (most recent call last):

  File "C:\python_scripts_prod\cs_salesforce.py", line 163, in <module>
    cur.executemany(insert_statement, all_opps)

DatabaseError: ORA-01036: illegal variable name/number
JBSilverAge
  • 1
  • Does this answer your question? [DatabaseError: ORA-01036: illegal variable name/number](https://stackoverflow.com/questions/16392288/databaseerror-ora-01036-illegal-variable-name-number) – Jeff Holt Feb 24 '22 at 22:26
  • I feel like I’m doing exactly what’s in that post. I don’t see the difference. – JBSilverAge Feb 25 '22 at 00:25
  • I can see how all_opps is being bound to :1 instead of the two members of each all_opps being bound to :1 and :2. You probably have to call `executemany(insert_statement, rundate_arr, sfoppid_arr)` due to the limitations in the Oracle Call Interface in how arrays are bound to placeholders. Both placeholder names and proper bind parameters are mentioned in that post IIRC. In other words, you have to bind scalar arrays to each placeholder in a statement when you want to execute array DML statements. Definitely a pain in the butt unless your API makes the translation for you. – Jeff Holt Feb 25 '22 at 00:32
  • I don't think that is correct. I'm using the documentation directly from cx_oracle found here: https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html and this exact configuration is working for a different dataset I'm exporting to the same Oracle library. – JBSilverAge Feb 25 '22 at 15:02
  • Can you add to your post the contents of all_opps? That should be an array of 2-tuples (or list of length 2). As in ``[(1, 2), (3, 4), (5, 6)]``. – Anthony Tuininga Feb 25 '22 at 16:39

0 Answers0