kzscpeT1

1# filename: insert_customer_data.py
2
3import sqlite3
4import csv
5
6# Files Path
7DB_PATH = 'customers.sqlite'
8CSV_PATH = 'customers.csv'
9
10# SQL queries
11# READ_TABLES_NAME = 'SELECT name FROM sqlite_master WHERE type="table";'
12READ_CUSTOMERS_DATA = 'SELECT * FROM Customer;'
13EMPTY_CUSTOMER_TABLE = 'DELETE FROM Customer;'
14DELETE_CUSTOMER_TABLE = 'DROP TABLE IF EXISTS Customer;'
15CREATE_CUSTOMER_TABLE = """CREATE TABLE IF NOT EXISTS Customer(
16                        customerID  INTEGER PRIMARY KEY     NOT NULL,
17                        firstName   TEXT                    NOT NULL,
18                        lastName    TEXT                    NOT NULL,
19                        companyName TEXT                    NULL,
20                        address     TEXT                    NULL,
21                        city        TEXT                    NULL,
22                        state       TEXT                    NULL,
23                        zip         TEXT                    NULL
24                        );"""
25INSERT_CUSTOMER_DATA = """INSERT INTO Customer
26                        (firstName, lastName, companyName, address, city, state, zip)
27                        VALUES
28                        ("{firstName}", "{lastName}", "{companyName}", "{address}", "{city}", "{state}", "{zip}");"""
29                        # HERE we are using string template so all the {abc} will replaced with actual value
30
31# Read customers.csv using csv.DictReader and return record one by one
32def customers_from_csv(csv_file_path):
33    # open csv
34    #### IMPORTANT CHOOSE LINE BASED ON YOUR PYTHON VERSION ####
35    with open(csv_file_path, encoding='utf-8') as fp: # FOR PYTHON3
36    #with open(csv_file_path) as fp: # FOR PYTHON2 
37        reader = csv.DictReader(fp)
38        for customer in reader:
39            yield customer
40
41
42# make database connection
43con = sqlite3.connect(DB_PATH)
44
45# Empty Customer Table
46con.execute(EMPTY_CUSTOMER_TABLE)
47con.commit()
48print('All old rows deletedd from Customer table.')
49
50# INSERT Customer data from csv to database
51customer_data = customers_from_csv(CSV_PATH)
52for i, customer in enumerate(customer_data, start=1):
53    con.execute(INSERT_CUSTOMER_DATA.format(
54        firstName = customer['first_name'],
55        lastName = customer['last_name'],
56        companyName = customer['company_name'],
57        address = customer['address'],
58        city = customer['city'],
59        state = customer['state'], 
60        zip = customer['zip'])
61    ) # if replace the first line of csv file (or headers name) with the Customer table field name 
62    # then you can use replce this line by # con.execute(INSERT_CUSTOMER_DATA.format(**customer))
63con.commit()
64print('{} row(s) inserted into Customer table.'.format(i))
65
66"""
67# Read customer data from Customer table
68customer_data = con.execute(READ_CUSTOMERS_DATA)
69for customer in customer_data:
70    print(customer)
71"""
72
73con.close()