Python's SQLITE3 Creating Table (and Database) and Inserting Data

Published: 25 March 2021
on channel: ZeroLife

121

import sqlite3

DFLT_path = 'C:/Desktop/mydatabase.db'
DFLT_table = 'mytable'
DFLT_data = {'name': "bob",'age':20,'address':'3 something st.'}

def db_give(db_name=DFLT_path, table=DFLT_table, data=DFLT_data):
table_data = []
for data_name in data:
if isinstance(data[data_name], str):
table_data.append(('TEXT',data_name,data[data_name]))
elif isinstance(data[data_name], int):
table_data.append(('INTERGER',data_name,data[data_name]))
elif isinstance(data[data_name], float):
table_data.append(('REAL',data_name,data[data_name]))
elif isinstance(data[data_name], (list,dict,set)):
table_data.append(('NUMERIC',data_name,str(data[data_name])))
else:
table_data.append(('BLOB',data_name,data[data_name]))

conn = sqlite3.connect(db_name)
cursor = conn.cursor()

with conn:
table = 'db' + str(table)
temp_str1 = []
for data_t, data_n, _ in table_data:
temp_str1.append(f'{data_n} {data_t} NOT NULL')

columns = f'id INTEGER PRIMARY KEY, ' + ', '.join(temp_str1)
create_table = f'CREATE TABLE IF NOT EXISTS {table} ({columns})'
cursor.execute(create_table)

category = ','.join(data.keys())

values = []
for val in list(data.value()):
if isinstance(val,(list,dict,set)):
values.append(str(val))
else:
values.append(val)

q_marks = []
for _ in range(len(data)):
q_marks.append('?')
q_marks = ', '.join(q_marks)

string_execute = f'INSERT INTO {table}({category}) VALUES({q_marks})'
cursor.execute(string_execute,values)

return string_execute + " "+ str(values)

###BELOW IS THE COMPRESSED VERSION OF THIS CODE (WHICH I USE)

def db_give(db_name=DFLT_path, table=DFLT_table, data=DFLT_data):

data['date'] = strftime("%D %T", localtime())
data['date_sec'] = time()

table_data = []
for data_name in data:
if isinstance(data[data_name], str):
table_data.append(('TEXT', data_name, data[data_name]))
elif isinstance(data[data_name], int):
table_data.append(('INTEGER', data_name, data[data_name]))
elif isinstance(data[data_name], float):
table_data.append(('REAL', data_name, data[data_name]))
elif isinstance(data[data_name], (list,dict,set)):
table_data.append(('NUMERIC', data_name, str(data[data_name])))
else: table_data.append(('BLOB', data_name, data[data_name]))

conn = sqlite3.connect(db_name)
cursor = conn.cursor()

with conn:
table = 'db' + str(table)
columns = 'id INTEGER PRIMARY KEY, ' + ', '.join([f'{data_n} {data_t} NOT NULL' for data_t, data_n, _ in table_data])
create_table = f'CREATE TABLE IF NOT EXISTS {table}({columns})'
cursor.execute(create_table)

category = ','.join(list(data.keys()))
cat_data = [str(i) if isinstance(i, (list,dict,set)) else i for i in list(data.values())]
q_marks = ','.join(["?" for _ in range(len(data))])

string_execute = f'INSERT INTO {table}({category}) VALUES({q_marks})'
cursor.execute(string_execute, cat_data)

return string_execute+f', {cat_data}'

PAT: / rezatahirkheli
PP: https://www.paypal.com/paypalme/rezat...
BTC: 3EUQBWZKX9Vcwdffd3cUGATQopxrAQQxJ9
LTC: MBXE6hJgxxFYSD8SjmR7sHCHGTqFXmNGoU
GFM: https://gofund.me/7d6500bd

On this page of the site you can watch the video online Python's SQLITE3 Creating Table (and Database) and Inserting Data with a duration of hours minute second in good quality, which was uploaded by the user ZeroLife 25 March 2021, share the link with friends and acquaintances, this video has already been watched 121 times on youtube and it was liked by 2 viewers. Enjoy your viewing!

994