Python - help with getting JSON from one DB and load to another DB

[qIekm]

New to Python and JSON, I am trying to do the following in Python 3.7:

1.Connect to Oracle database

2.Get data from specific table in proper JSON output

3.Connect to SQL server (v18.4)

4.Load data from step 2. in corresponding table in SQL Server (table is already created there)

I am not sure how to get table name in JSON output in step 2 and how to further load it in SQL server using Python. I am getting this kind of JSON output at the moment, without a table name:

[
 {
  "col1": 128583,
  "col2": "surname",
  "col3": "93 3j-039"
 }
]

And here is the code so far:

import cx_Oracle
import pyodbc
import json
import MySQLdb


#Connect to Oracle DB and get table data output as JSON object
dsn_tns = cx_Oracle.makedsn('xxx', 'xxx', 'xxx')
conn = cx_Oracle.connect(user='xxx', password='xxx', dsn=dsn_tns)
c = conn.cursor()

#Allows to pass date objects
class DatetimeEncoder(json.JSONEncoder):
    def default(self, obj): # pylint: disable=method-hidden
        try:
            return super(DatetimeEncoder, obj).default(obj)
        except TypeError:
            return str(obj)

cursor = conn.cursor()
cursor.execute('select * from test_table' )

r = [dict((cursor.description[i][0], value) \
               for i, value in enumerate(row)) for row in cursor.fetchall()]
print(json.dumps(r,cls=DatetimeEncoder, indent=2))  #How to return name of table as well?

#SQL Server import data
#Connect to SQL Server
server = 'xxx'
database = 'xxx'
username = 'xxx'
password = 'xx'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

# not a working code at the moment, just a rough idea
def insertDb():
    try:
        cursor.execute("""
        INSERT INTO nameoftable(nameofcolumn) \
        VALUES (%s) """, (row))
        cursor.close()
    except Exception as e:
        print (e)

[Larz60+]

why not use sql output?
It's easy and portable.

[qIekm]

why not use sql output?
It's easy and portable.

How exactly would I do that in my case?

[Larz60+]

You would use export command:
see: https://docs.oracle.com/cd/E17781_01/ser...m#ADMQS256

[qIekm]

Thing is, I need/want to accomplish this in Python only and have been trying to insert only one row with one column. I have created 2 equal tables in Oracle and MSSQL, like this:

#create table with test data in Oracle
create table test_t (
onecol varchar2 (50)
)
INSERT INTO test_t
(onecol)
VALUES
('testinginsert');

And

#create table in MSSQL to insert data to
create table test_t (
onecol varchar (50)
)
commit;

And here is updated code: It is giving me 'pyodbc.Error: ('07002'. '[07002]' [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')'

#Importing modules

import cx_Oracle
import pyodbc
import json
import MySQLdb
import pytds


#Create Orcle DB connection

dsn_tns = cx_Oracle.makedsn('zz', 'zzz', 'zz')
conn = cx_Oracle.connect(user='xx', password='xx', dsn=dsn_tns)
c = conn.cursor()


#Allows to pass date objects if there are any to avoid JSON not serializable error

class DatetimeEncoder(json.JSONEncoder):
    def default(self, obj): # pylint: disable=method-hidden
        try:
            return super(DatetimeEncoder, obj).default(obj)
        except TypeError:
            return str(obj)

cursor_orcl = conn.cursor()
cursor_orcl.arraysize= 50000

#Option 1:
# Each row in the database becomes one dictionary and each field in the row a key-value pair, producing JSON output
#r = [dict((cursor_orcl.description[i][0], value) \
#               for i, value in enumerate(row)) for row in cursor_orcl.fetchall()]    
#cursor_orcl.connection.close()
#Checking JSON
# print(json.dumps(r,cls=DatetimeEncoder, indent=2))

#Option 2:
cursor_orcl.execute("""
                    Select * from test_t where rownum <=1
            """)

rows = cursor_orcl.fetchall()
columns = [desc[0] for desc in cursor_orcl.description]
result = []

for row in rows:
    row = dict(zip(columns, row))
    result.append(row) 

#Checking JSON output
print ((result))

 
#Create MSSQL connectionc
server = 'aaaa'
database = 'aaa'
username = 'aaa'
password = 'aaa'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

cursor_insert = cnxn.cursor()
cursor_insert.arraysize= 50000
sql = (
    "INSERT INTO dbo.test_t (onecol) " + " VALUES("
    "SELECT onecol " +
    "FROM OPENJSON(?))"
      )

cursor_insert.execute(sql)   
cursor_insert.close ()
cnxn.commit ()