Load data into Oracle using Python from imeicheck.com API

Question

I'm new to Python, and I want to get IMEI data from https://imeicheck.com/ website, using Python and load it into Oracle DB. I've 2 issues:

1. provided API link from this website return only the specified IMEI data while I want to get the whole data.

   https://alpha.imeicheck.com/api/modelBrandName?imei=352322311421731&format=json

2. when importing to Oracle DB, I receive the below error:

   DatabaseError                             Traceback (most recent call last)
   <ipython-input-4-203f77e9ca0f> in <module>()
         8  models,
         9  model_name) 
   ---> 10 VALUES (:1,:2,:3,:4)''',rows)
        11 db.commit()
   
   DatabaseError: ORA-01036: illegal variable name/number
   

Here is my code:

import requests
import json
import pandas as pd
import cx_Oracle

url = "https://alpha.imeicheck.com/api/modelBrandName?imei=352322311421731&format=json"
response_API = requests.get(url, verify=False)

data = response_API.json()
df = pd.DataFrame(data)

rows = [list(x) for x in df.values]

conn_str = 'bi/[email protected]:1521/ORABI'
db = cx_Oracle.connect(conn_str)
curs = db.cursor()
curs.execute('truncate table DIM_IMEI')

curs.executemany('''INSERT INTO DIM_IMEI(IMEI,
 brand,
 models,
 model_name) 
VALUES (:1,:2,:3,:4)''',rows)
db.commit()

I would be thankful if you could assist with these issues.

Answer 1

If you have a recent Oracle Database version, then you could simply store the returned data in an Oracle JSON column:

import os
import platform
import json

import requests
import oracledb

un = os.environ.get('PYTHON_USERNAME')
pw = os.environ.get('PYTHON_PASSWORD')
cs = os.environ.get('PYTHON_CONNECTSTRING')

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:
        try:
            cursor.execute("drop table dim_imei purge")
        except oracledb.DatabaseError:
            pass

        cursor.execute("create table dim_imei (j json)")

url = "https://alpha.imeicheck.com/api/modelBrandName?imei=352322311421731&format=json"
response_API = requests.get(url, verify=False)
data = response_API.json()

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:
        connection.autocommit = True
        cursor.setinputsizes(oracledb.DB_TYPE_JSON)
        cursor.execute("insert into dim_imei (j) values (:bv)", [data])

        for r in cursor.execute("select * from dim_imei"):
            print(r)

Or if you need to store the values in an existing relational table then something like this might work:

import os
import platform
import json

import requests
import oracledb

un = os.environ.get('PYTHON_USERNAME')
pw = os.environ.get('PYTHON_PASSWORD')
cs = os.environ.get('PYTHON_CONNECTSTRING')

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:
        try:
            cursor.execute("drop table dim_imei purge")
        except oracledb.DatabaseError:
            pass

        cursor.execute("create table dim_imei (brand varchar2(20), models varchar2(20), model_name varchar2(20))")

url = "https://alpha.imeicheck.com/api/modelBrandName?imei=352322311421731&format=json"
response_API = requests.get(url, verify=False)
data = response_API.json()
b = data['object']['brand']
m = data['object']['model']
n = data['object']['name']

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:
        connection.autocommit = True
        cursor.execute("insert into dim_imei (brand, models, model_name) values (:b1, :b2, :b3)", [b, m, n])

        for r in cursor.execute("select * from dim_imei"):
            print(r)

You'll have to work out which bits of the returned response you actually want to store. Using Pandas to do a conversion will just add overhead.

To insert one row I simply use execute(), not executemany(). Also I set autocommit for this one row to save the extra overhead of an explicit commit() call.

Note I'm using the latest version of cx_Oracle, which is now called python-oracledb: see the release announcement.