create sqlite database python

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