How to create new table and insert data in to table using python based on json

Question

I am trying to create a new database table and insert the data into that table. Example I have two tables data with table column name and column datatype. I want to create those two tables and insert the data in to correct table name. I have used python function with connection parameters. Request your suggestion to achive this.

json data

data_JSON =  """
[
    {
        "dbName": "sampledata",
        "tableName": [
            "status_in","status_out"
        ],
        "tableColumn": [
            [
                {
                    "type": "INT",
                    "title": "phoneno"
                },
                {
                    "type": "VARCHAR (60)",
                    "title": "name"
                },
                {
                    "type": "DATE",
                    "title": "date"
                }
            ],
            [
                {
                    "type": "INT",
                    "title": "phoneno"
                },
                {
                    "type": "VARCHAR (60)",
                    "title": "name"
                },
                {
                    "type": "DATE",
                    "title": "date"
                }
            ]
        ],
        "tableData": [
            [
                {
                    "phoneno": "99999",
                    "name": "ticker1",
                    "date": "01/01/2021"
                },
                {
                    "phoneno": "99999",
                    "name": "ticker2",
                    "date": "01/01/2021"
                },
                {
                    "phoneno": "99999",
                    "name": "ticker3",
                    "date": "01/01/2021"
                }
            ],
            [
                {
                    "phoneno": "99999",
                    "name": "few1",
                    "date": "01/01/2021"
                },
                {
                    "phoneno": "99999",
                    "name": "few2",
                    "date": "01/01/2021"
                },
                {
                    "phoneno": "99999",
                    "name": "few1",
                    "date": "01/01/2021"
                }
            ]
        ]
    }
]
"""

Python Code

import json
import psycopg2
from psycopg2 import sql
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
def create_table(data_JSON):
    try:
        filedata = json.loads(data_JSON)
        db_name = filedata[0] ['dbname']
        con = psycopg2.connect(user='postgres', host='127.0.0.1', database = db_name, password='test@123')
        con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
        cur = con.cursor()
        for sheet in data_dict[0]['tableName']:
            sheet_name = sheet   
        for z in data_dict[0]['tableColumn']:
            for column in z[0][title]:
                column_name = column
            for dtype in z[0]['type']:
                datatype = dtype
                cur.execute(f"CREATE TABLE {sheet_name}({column_name} {data_type});")
        for key,value in data_dict[0]['tableData']:
            cur.execute(f"INSERT INTO {sheet_name}({column_name})VALUES ({value});")
            con.commit()
            cur.close()
            con.close()
    except Exception:
        return False
    return True

Answer 1

Hey there, I have modified it and tested it. Here is refactored code:

import json
import psycopg2
from psycopg2 import sql
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
def create_table(data_JSON):
    try:
        filedata = json.loads(data_JSON)
        db_name = filedata[0]['dbName']
        print(db_name)
        
        con = psycopg2.connect(user='postgres', host='127.0.0.1', database = db_name, password='password')
        print ("Opened database successfully")
        con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
        cur = con.cursor()
        table_names = filedata[0]['tableName'] 
        table_columns = filedata[0]['tableColumn']
        table_datum = filedata[0]['tableData']
        for table_name, table_column, table_data in zip(table_names, table_columns, table_datum):
            column_to_insert = ""
            for td in table_column:
                column_to_insert += td['title'] + " " + td['type'] +", "
            
            column_to_insert = column_to_insert[:len(column_to_insert) - 2]
            cur.execute(f"CREATE TABLE {table_name} ({column_to_insert});")
        
            for row in table_data:
                
                keys = ", ".join(row.keys())
                values = ""
                for val in row.values():
                    values += f"'{val}', "
                
                values = values[: len(values) - 2]
                print(keys, values)
                cur.execute(f"INSERT INTO {table_name} ({keys}) VALUES ({values});")
            con.commit()
        cur.close()
        con.close()
    except Exception as e:
        print(e)
        return False
    return True