· 4 years ago · Jul 27, 2021, 02:44 PM
1import pandas as pd
2import sqlite3
3
4"""
5 Create a database connection to the SQLite database
6 specified by db_file
7
8 Input:
9 db_file: database file
10
11 Output:
12 Connection object or None
13"""
14def create_connection(db_file):
15
16 conn = None
17 try:
18 conn = sqlite3.connect(db_file)
19 return conn
20 except Error as e:
21 print(f"An error occurred. No such file: {db_file}.")
22
23"""
24 Create a database connection to the SQLite database
25 specified by db_file
26
27 Input:
28 conn : Connection object
29 create_table_query: Create Table statement
30
31 Output:
32 None
33"""
34def create_table(conn, create_table_sql):
35
36 conn.cursor()
37 conn.execute(create_table_sql)
38
39"""
40 Upsert records into the database (dependent on SQLite's syntax)
41
42 Input:
43 conn : Connection object
44 values: Values to insert into the table
45
46 Output:
47 None
48"""
49def upsert_record(conn, values):
50
51 conn.cursor()
52 conn.execute(f"""INSERT INTO suppliers
53 VALUES({values[0]}, ..., {values[11]}) # if this is unattractive, then simply use a for loop to write the query
54 ON CONFLICT(supplierID) DO UPDATE SET
55 companyName = {values[1]}, # same advice for here
56 contactName = {values[2]},
57 ...,
58 homePage = {values[11]};""")
59
60"""
61 Main function: carry out the task
62
63 Input:
64 None
65
66 Output:
67 None
68"""
69def main():
70
71 # Read in data from git website
72 url = "https://raw.githubusercontent.com/graphql-compose/graphql-compose-examples/master/examples/northwind/data/csv/suppliers.csv"
73
74 # Open CSV file and read row by row and insert into the table
75 with open(url, 'r') as csv_file:
76
77 data_reader = csv.reader(csv_file)
78 # This skips the first row of the CSV file.
79 next(data_reader)
80
81 for record_values in datareader:
82
83 # create database connection
84 database_path = r"C:\path\to\database.db"
85 conn = create_connection(database_path)
86
87 # create the table within the database [if it doesn't exist]
88 create_supplier_statement = """ CREATE TABLE IF NOT EXISTS suppliers (
89 supplierID INTEGER PRIMARY KEY,
90 companyName TEXT NOT NULL,
91 contactName TEXT NOT NULL,
92 contactTitle TEXT NOT NULL,
93 address TEXT NOT NULL,
94 city TEXT NOT NULL,
95 region TEXT,
96 postalCode INTEGER NOT NULL,
97 country TEXT NOT NULL,
98 phone TEXT NOT NULL,
99 fax TEXT,
100 homePage TEXT
101 ); """
102 create_table(conn, create_supplier_statement)
103
104 # insert records into the DB table
105 upsert_record(conn, record_values)