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:
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
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/testpasss@1.1.1.1: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.
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.