create sqlite database python

Solutions on MaxInterview for create sqlite database python by the best coders in the world

showing results for - "create sqlite database python"
Frieda
15 Jul 2017
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
Youcef
23 Jun 2016
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)
January
22 Nov 2018
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
queries leading to this page
how to create a database in python using sqlite3create db sqlite pythonhow to create sqlite database pythonmaking a new database sqlite pyhtoncreate a database sqlite3 pythoncreate sql database table in sqlite pythonsqlite create db pythoninitialize sqlite database pythoncreate new database from python sqlite command how to create sqlite3 database in pythonpython create sqlite databasecreate sqlite db pythonhow to create sqllite db in pythonuse sqlite database pythonsqlite3 create new database pythoncreate sqlite3 database in pythonpython create a sqlite database store dataframe as a tablepython create table sqlitesqlite3 create db from script pythonpython create sql databasemaking a database is python sqlite 3 create sqlite in pythoncreate table sqlite3 pythoncreate database sqlite pyhtoncreate new sqlite database pythonhow to create table in sqlite3 pythonpython sqlite create databasehow to make a sqlite database in pythonpython make sqlite dbsqlite3 create database pythonpython sqlte create database with tablehow to run create a sqlite3 database using pythonpython sqlite create local databasecreating sqlite database pythonsqlite3 python create tablehow to create table with sqlite python create table in sql sqlite3 pythontutorial de sqlite with pythoncreate sqlite pythonpython code to create sqlite databasecreating a new databsd sqlite pythongenerate sqlite database pythonsqlite database in pythoncreate table in sqlite3 pythoncreate database python sqlite sqlalchemycreate a database in python sqlitelinux create sqlite db from pytho shemecreate sqlite pythonhow to create database in python sqlite3python sqlite3 create database from sql filepython create new sqllite databasesqlite3 python create databasecreating database with sqlite3 with pythonpython sqlite3 create tablehow to create sqlite database in pythonhow to create a database and table in python with sqlite3 python sqlite3 series part 2making a new database sqlite3 pyhtoncreate sqlite file pythomysql python create database sqlitepython sqlte create databasepython managing database with sqlite3how to create sql table in python sqlite3create dtabase sqlite3 pythonsqlite in python3 create tablecreate a new sqlite database file pythonsqlite3 create table pythoncreate new database from python sqlite command linesqlite database pythoncreate a sqlite db from python shellhow to create table through sqlite in pythoncreate a sqlite3 database pythonpython database sqlitecreate simple database python sqlite3python sqlite3 create databasesqlite3 create db pythoncreate table sqlite pythoncreate sql database pythoncreate db file sqlite3 pythoncreate a table in sqlite in pythoncreate sqlite database pythonpython sqlite create tablecreate a table in sqlite3 pythonhow to create a local sqlite database pythn how to create database in python using db browser for sqlitehow to create database in python using sqlite3create an sqlite database pythoncreate table in sqlite pythonsqlite initialize db pythonsqlite create table pythoncreate a sqlite db in pythonhow to add a sqlite3 database to a python frameworksqlite python create dbcreate sqlite database in pythoncreate db python sqliteusing sqlite database in pythonpython create a new database from sqlite3 structurehow to make a table in sqlite pythonsqlite3 create db and table pythoncreate sqlite connection pythonsqlite3 create table and database pythonpython script to create sqlite databasecreate database sqlite pythonpython create sqlite database with tablescreate a sqlite database pythonhow to create table with python in sqlitepython established sqlite databasepython create sqlite filesqlite3 python create an databasecreate new database from python sqlitepython create new sqlite3 database filepython sqlite3 database createcreate new database sqlite pythonpython sqlite make tablecreate sqlite table pytohnpython sqlite3 create database 5ccreate a sqlite db python interactivehow to create a database with sqllite with pythoncreate sqlite 3 database pythonpython create db sqlite3 filehow to create table in sqlite using pythonpython create new sqlite database sqlalchemycreate sqlite table pythoncreate table sqlite python sqlalchemyhow make new database sqlite3 pythonsqlite create database pythinsqlite python creat tablepython sqlite createhow to create a sqlite database in pythonsqlite database using pythonhow to programatically create a new sqlite db in pythonpython initialize sqlite databasecreate sqlite3 database pythoncreate local sqlite database pythonsetup sqlite database pythonmake sqlite database pythoncreate an sqlite database with pythoncreate new table sqlite pythonhow to create db file in sqlite3 pythonhow to create database in sqlite pythonmake a new database sqllite pythonsqlite make new database pythoncreate database in python with sqlitehow to create a database in sqlite3 using pythoncreating a python file that creates an sqlite database and all the tablesdatabase python sqlitehow to create a table in sqlite using pythonwindows create a sqlite db pythoncreate sqlite3 table pythonsqlite create database pythonpython sqlite databasemake tables sqlite pythonpython db sqlite3 createhow to create table in sqlite pythonsqlite python create tablecreate sqlite database python