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

[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 ()