Reputation: 167
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.
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"
}
]
]
}
]
"""
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
Upvotes: 0
Views: 523
Reputation: 96
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
Upvotes: 1