1import sqlite3
2
3conn = sqlite3.connect('TestDB.db') # You can create a new database by changing the name within the quotes
4c = conn.cursor() # The database will be saved in the location where your 'py' file is saved
5
6# Create table - CLIENTS
7c.execute('''CREATE TABLE CLIENTS
8 ([generated_id] INTEGER PRIMARY KEY,[Client_Name] text, [Country_ID] integer, [Date] date)''')
9
10# Create table - COUNTRY
11c.execute('''CREATE TABLE COUNTRY
12 ([generated_id] INTEGER PRIMARY KEY,[Country_ID] integer, [Country_Name] text)''')
13
14# Create table - DAILY_STATUS
15c.execute('''CREATE TABLE DAILY_STATUS
16 ([Client_Name] text, [Country_Name] text, [Date] date)''')
17
18conn.commit()
19
20# Note that the syntax to create new tables should only be used once in the code (unless you dropped the table/s at the end of the code).
21# The [generated_id] column is used to set an auto-increment ID for each record
22# When creating a new table, you can add both the field names as well as the field formats (e.g., Text)
23
1
2
3
4
5 import sqlite3
6from sqlite3 import Error
7
8
9def create_connection(db_file):
10 """ create a database connection to the SQLite database
11 specified by db_file
12 :param db_file: database file
13 :return: Connection object or None
14 """
15 conn = None
16 try:
17 conn = sqlite3.connect(db_file)
18 return conn
19 except Error as e:
20 print(e)
21
22 return conn
23
24
25def create_table(conn, create_table_sql):
26 """ create a table from the create_table_sql statement
27 :param conn: Connection object
28 :param create_table_sql: a CREATE TABLE statement
29 :return:
30 """
31 try:
32 c = conn.cursor()
33 c.execute(create_table_sql)
34 except Error as e:
35 print(e)
36
37
38def main():
39 database = r"C:\sqlite\db\pythonsqlite.db"
40
41 sql_create_projects_table = """ CREATE TABLE IF NOT EXISTS projects (
42 id integer PRIMARY KEY,
43 name text NOT NULL,
44 begin_date text,
45 end_date text
46 ); """
47
48 sql_create_tasks_table = """CREATE TABLE IF NOT EXISTS tasks (
49 id integer PRIMARY KEY,
50 name text NOT NULL,
51 priority integer,
52 status_id integer NOT NULL,
53 project_id integer NOT NULL,
54 begin_date text NOT NULL,
55 end_date text NOT NULL,
56 FOREIGN KEY (project_id) REFERENCES projects (id)
57 );"""
58
59 # create a database connection
60 conn = create_connection(database)
61
62 # create tables
63 if conn is not None:
64 # create projects table
65 create_table(conn, sql_create_projects_table)
66
67 # create tasks table
68 create_table(conn, sql_create_tasks_table)
69 else:
70 print("Error! cannot create the database connection.")
71
72
73if __name__ == '__main__':
74 main()Code language: Python (python)
1import sqlite3
2import pandas as pd
3from pandas import DataFrame
4
5conn = sqlite3.connect('TestDB.db')
6c = conn.cursor()
7
8read_clients = pd.read_csv (r'C:\Users\Ron\Desktop\Client\Client_14-JAN-2019.csv')
9read_clients.to_sql('CLIENTS', conn, if_exists='append', index = False) # Insert the values from the csv file into the table 'CLIENTS'
10
11read_country = pd.read_csv (r'C:\Users\Ron\Desktop\Client\Country_14-JAN-2019.csv')
12read_country.to_sql('COUNTRY', conn, if_exists='replace', index = False) # Replace the values from the csv file into the table 'COUNTRY'
13
14# When reading the csv:
15# - Place 'r' before the path string to read any special characters, such as '\'
16# - Don't forget to put the file name at the end of the path + '.csv'
17# - Before running the code, make sure that the column names in the CSV files match with the column names in the tables created and in the query below
18# - If needed make sure that all the columns are in a TEXT format
19
20c.execute('''
21INSERT INTO DAILY_STATUS (Client_Name,Country_Name,Date)
22SELECT DISTINCT clt.Client_Name, ctr.Country_Name, clt.Date
23FROM CLIENTS clt
24LEFT JOIN COUNTRY ctr ON clt.Country_ID = ctr.Country_ID
25 ''')
26
27c.execute('''
28SELECT DISTINCT *
29FROM DAILY_STATUS
30WHERE Date = (SELECT max(Date) FROM DAILY_STATUS)
31 ''')
32
33#print(c.fetchall())
34
35df = DataFrame(c.fetchall(), columns=['Client_Name','Country_Name','Date'])
36print (df) # To display the results after an insert query, you'll need to add this type of syntax above: 'c.execute(''' SELECT * from latest table ''')
37
38df.to_sql('DAILY_STATUS', conn, if_exists='append', index = False) # Insert the values from the INSERT QUERY into the table 'DAILY_STATUS'
39
40# export_csv = df.to_csv (r'C:\Users\Ron\Desktop\Client\export_list.csv', index = None, header=True) # Uncomment this syntax if you wish to export the results to CSV. Make sure to adjust the path name
41# Don't forget to add '.csv' at the end of the path (as well as r at the beg to address special characters)
42