Mar-30-2020, 09:41 AM
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:
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)