def create(table_name):
conn=db_connect() # to get the connection object
cur=conn.cursor() # creting cursor object
if request.method=="POST":
flag=set_data(table_name,request.form)
else:
cur.execute("select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = "+table_name+";")
print cur.fetchall(),"$$$$$$$$$$$$$$$$"
return render_template('common_form.html',column)
def execute_sql(query, q_args=None, attempt=1):
reconnect_and_retry = False
try:
if q_args is None:
# execute a basic SQL query
connect.db_crsr.execute(query)
else:
# find out how many arguments there are
q_arg_len = len(q_args)
if q_arg_len == 1:
connect.db_crsr.execute(query, (q_args))
elif q_arg_len > 1:
connect.db_crsr.execute(query, q_args)
else:
print(
f'attempted to execute an SQL query with too many ({q_arg_len}) args'
)
except connect.psycopg2.errors.InFailedSqlTransaction as error:
reconnect_and_retry = True
print(
f'failed SQL transaction, reconnecting automatically: {type(error)}: {error}'
)
except connect.psycopg2.OperationalError as error:
reconnect_and_retry = True
print(
f'failed SQL transaction, reconnecting automatically: {type(error)}: {error}'
)
except connect.psycopg2.InterfaceError as error:
reconnect_and_retry = True
print(
f'failed SQL transaction, reconnecting automatically: {type(error)}: {error}'
)
except connect.psycopg2.DatabaseError as error:
reconnect_and_retry = True
print(
f'failed SQL transaction, reconnecting automatically: {type(error)}: {error}'
)
# reconnect to the database and try to re-execute the SQL query
if reconnect_and_retry:
success = connect.db_connect('automatic reconnection')
if success:
print('reconnection was a success')
# only try 3 times, if it still doesn't work there is some larger issue
if attempt <= 3:
execute_sql(query, q_args=q_args, attempt=(attempt + 1))
else:
print('connection failed')
return
def client_insertion():
conn=db_connect() # to get the connection object
cur=conn.cursor() # creting cursor object
cur.execute('select id from client')
max_id=max([i[0] for i in cur.fetchall()]) # to get max id in the total ids of the client table
max_id=max_id+1
dict_vals={'id':str(max_id),
'name':'name5',
'address':'address5',
'cell':'5689789',
'course_id':'1',
'remark1':'remarks1',
'remark2':'remarks2',
'remark3':'remarks3',
'remark4':'remarks4',}
query="INSERT INTO client(id,name,address,cell,course_id,remark1,remark2,remark3,remark4) VALUES (%(id)s,%(name)s,%(address)s,%(cell)s,%(course_id)s,%(remark1)s,%(remark2)s,%(remark3)s,%(remark4)s)"
cur.execute(query,dict_vals)
#cur.execute("insert into client(id,name,address,cell,course_id,remark1,remark2,remark3,remark4) values("+str(max_id+1)+",'name5','address5',5689789,1,'remarks1','remarks2','remarks3','remarks4') ") # query execution
conn.commit() # To save the changes in database
cur.close() # closing the cursor
conn.close() # closing the connection
return "secussfully inserted!"
import os
import logging
import csv
import connect
LOG_FORMAT = "%(levelname)s %(asctime)s - %(message)s"
logging.basicConfig(level=logging.INFO, format=LOG_FORMAT)
path = os.getcwd()
cur, conn = connect.db_connect()
def insert_csv_to_db(file_name):
try:
file = open(f'{path}/csv_files/{file_name}', 'r')
reader = csv.reader(file)
next(reader)
cur.executemany("INSERT INTO forecast VALUES (?, ?, ?, ?, ?)", reader)
conn.commit()
file.close()
logging.info(f'file {file_name} uploaded successfully to database')
except Exception as e:
logging.error(f'{e}')
def file_uploader():
# csv_files dir should contain the csv files for upload
files = os.listdir(f'{path}/csv_files/')
for file in files:
insert_csv_to_db(file)