Скрипт для создания таблицы в базе данных на основе csv файла с заголовками.
#!/usr/bin/env python
# coding: utf-8
# WHERE TO CREATE SQL DB
DB_PATH = r"C:/DB_PATH/db.db"
# INPUT CSV FILE
csv_in = r"C:/in.csv"
SQL_table_name = "mynewsqltable"
import sqlite3 as sl
import csv
def db_cur_con(DB_PATH):
con = sl.connect(DB_PATH)
cur = con.cursor()
return cur, con
def execute_SQL(DB_PATH, STATEMENT,VARIABLES):
cur,con = db_cur_con(DB_PATH)
try:
if VARIABLES != None:
cur.execute(STATEMENT, VARIABLES)
else:
cur.execute(STATEMENT)
msg = "SQL statement executed sucessfully"
except Exception as e:
msg = "SQL execution ERROR: ", e
finally:
con.commit()
con.close()
print(msg)
# CREATING DB AND TABLE
with open(csv_in, newline='', encoding='utf-8') as csvfile:
d_reader = csv.DictReader(csvfile)
headers = d_reader.fieldnames
COLUMNS = " TEXT,".join(headers) # Всё будет импортировано как текст
STATEMENT = """ CREATE TABLE IF NOT EXISTS """ + SQL_table_name + """(""" + COLUMNS + """);"""
VARIABLES = None
print("Creating Table")
execute_SQL(DB_PATH, STATEMENT,VARIABLES)
print("Table has been created")
# IMPORTING VALUES (minimum 2 values!!!)
z=0
with open(csv_in, newline='', encoding='utf-8') as csvfile:
d_reader = csv.DictReader(csvfile)
headers = d_reader.fieldnames
COLUMNS = " ,".join(headers)
for line in d_reader:
VARIABLES = tuple(x[1] for x in line.items())
STATEMENT = "INSERT INTO " SQL_table_name " (" + COLUMNS + ") VALUES ( " + "?, "*(len(VARIABLES)-1) + "?)" #DO NOT USE FOR USER INPUT. VULNURABLE FOR SQL INJECTION
execute_SQL(DB_PATH, STATEMENT,VARIABLES)
z+=1
print("Line ",str(z))