Sign in Get Started

create sqlite database python

Krish 
import sqlite3

conn = sqlite3.connect('TestDB.db')  # You can create a new database by changing the name within the quotes
c = conn.cursor() # The database will be saved in the location where your 'py' file is saved

# Create table - CLIENTS
c.execute('''CREATE TABLE CLIENTS
             ([generated_id] INTEGER PRIMARY KEY,[Client_Name] text, [Country_ID] integer, [Date] date)''')
          
# Create table - COUNTRY
c.execute('''CREATE TABLE COUNTRY
             ([generated_id] INTEGER PRIMARY KEY,[Country_ID] integer, [Country_Name] text)''')
        
# Create table - DAILY_STATUS
c.execute('''CREATE TABLE DAILY_STATUS
             ([Client_Name] text, [Country_Name] text, [Date] date)''')
                 
conn.commit()

# 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). 
# The [generated_id] column is used to set an auto-increment ID for each record
# When creating a new table, you can add both the field names as well as the field formats (e.g., Text)
View another examples Add Own solution
Log in, to leave a comment

4.11
9
Awgiedawgie 440215 points 

                                    import sqlite3
import pandas as pd
from pandas import DataFrame

conn = sqlite3.connect('TestDB.db')  
c = conn.cursor()

read_clients = pd.read_csv (r'C:\Users\Ron\Desktop\Client\Client_14-JAN-2019.csv')
read_clients.to_sql('CLIENTS', conn, if_exists='append', index = False) # Insert the values from the csv file into the table 'CLIENTS' 

read_country = pd.read_csv (r'C:\Users\Ron\Desktop\Client\Country_14-JAN-2019.csv')
read_country.to_sql('COUNTRY', conn, if_exists='replace', index = False) # Replace the values from the csv file into the table 'COUNTRY'

# When reading the csv:
# - Place 'r' before the path string to read any special characters, such as '\'
# - Don't forget to put the file name at the end of the path + '.csv'
# - 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
# - If needed make sure that all the columns are in a TEXT format

c.execute('''
INSERT INTO DAILY_STATUS (Client_Name,Country_Name,Date)
SELECT DISTINCT clt.Client_Name, ctr.Country_Name, clt.Date
FROM CLIENTS clt
LEFT JOIN COUNTRY ctr ON clt.Country_ID = ctr.Country_ID
          ''')

c.execute('''
SELECT DISTINCT *
FROM DAILY_STATUS
WHERE Date = (SELECT max(Date) FROM DAILY_STATUS)
          ''')
   
#print(c.fetchall())

df = DataFrame(c.fetchall(), columns=['Client_Name','Country_Name','Date'])
print (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 ''')

df.to_sql('DAILY_STATUS', conn, if_exists='append', index = False) # Insert the values from the INSERT QUERY into the table 'DAILY_STATUS'

# 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
# Don't forget to add '.csv' at the end of the path (as well as r at the beg to address special characters)

4.11 (9 Votes)
0
3.75
4
Awgiedawgie 440215 points 

                                    
            
                
            
         import sqlite3
from sqlite3 import Error


def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)

    return conn


def create_table(conn, create_table_sql):
    """ create a table from the create_table_sql statement
    :param conn: Connection object
    :param create_table_sql: a CREATE TABLE statement
    :return:
    """
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)


def main():
    database = r"C:\sqlite\db\pythonsqlite.db"

    sql_create_projects_table = """ CREATE TABLE IF NOT EXISTS projects (
                                        id integer PRIMARY KEY,
                                        name text NOT NULL,
                                        begin_date text,
                                        end_date text
                                    ); """

    sql_create_tasks_table = """CREATE TABLE IF NOT EXISTS tasks (
                                    id integer PRIMARY KEY,
                                    name text NOT NULL,
                                    priority integer,
                                    status_id integer NOT NULL,
                                    project_id integer NOT NULL,
                                    begin_date text NOT NULL,
                                    end_date text NOT NULL,
                                    FOREIGN KEY (project_id) REFERENCES projects (id)
                                );"""

    # create a database connection
    conn = create_connection(database)

    # create tables
    if conn is not None:
        # create projects table
        create_table(conn, sql_create_projects_table)

        # create tasks table
        create_table(conn, sql_create_tasks_table)
    else:
        print("Error! cannot create the database connection.")


if __name__ == '__main__':
    main()Code language: Python (python)

3.75 (4 Votes)
0
Are there any code examples left?
Find Add Code snippet
New code examples in category Python
SHOW MORE
Create a Free Account
Unlock the power of data and AI by diving into Python, ChatGPT, SQL, Power BI, and beyond.
Sign up
Develop soft skills on BrainApps
Complete the IQ Test
Relative searches
create database in python with sqlite how to create a local sqlite database pythn how to create database in python using db browser for sqlite how to create database in python using sqlite3 python sqlite make table sqlite make new database python how to run create a sqlite3 database using python python create a sqlite database store dataframe as a table sqlite create database pythin sqlite3 create db from script python python sqlite create database sqlite create database python create sqlite in python create new table sqlite python how to create a table in sqlite using python make tables sqlite python create a sqlite db python interactive create a sqlite db from python shell windows create a sqlite db python create local sqlite database python python sqlite create table python Sqlite create create database python sqlite sqlalchemy creating database with sqlite3 with python using sqlite database in python how to create db file in sqlite3 python create a sqlite db in python sqlite database using python python sqlite3 create database python create Sqlite database with tables how to create sqlite database in python python sqlite database create a table in sqlite3 python create a new sqlite database file python sqlite3 create table python sqlite3 create db python sqlite create table python create sqlite 3 database python create sqlite table pytohn python create db.sqlite3 file creating a new databsd sqlite python How to add a sqlite3 database to a python framework create db file sqlite3 python generate sqlite database python python sqlite3 create table create sqlite3 table python how to create a sqlite database in python create new database sqlite python sqlite3 create new database python python create sqlite file sqlite3 create database python making a new database sqlite pyhton making a new database sqlite3 pyhton how to create table in sqlite3 python create sqlite3 database python python sqlite3 database create python initialize sqlite database How to create a database in sqlite3 using Python how to make a sqlite database in python create sqlite3 database in python HOW TO CREATE SQLLITE DB IN PYTHON create sqlite connection python sqlite3 python create table python managing database with sqlite3 python sqlite3 create database from sql file sqlite python create table python code to create sqlite database create database sqlite pyhton create a sqlite3 database python use sqlite database python create db sqlite python create sqlite database python create a database sqlite3 python linux create sqlite db from pytho sheme create table sqlite python create sqlite file pytho create table in sqlite3 python setup sqlite database python create an sqlite database python sqlite create db python how to create sql table in python sqlite3 create sqlite database in python mysql python create database sqlite python create a new database from sqlite3 structure how to create sqlite database python sqlite initialize db python initialize sqlite database python how to create database in sqlite python python create sqlite database making a database is python sqlite 3 create database sqlite python create dtabase sqlite3 python sqlite database in python python database sqlite sqlite database python sqlite3 python create database create sqlite table python
Made with love
This website uses cookies to make IQCode work for you. By using this site, you agree to our cookie policy

Welcome Back!

Sign up to unlock all of IQCode features:
  • Test your skills and track progress
  • Engage in comprehensive interactive courses
  • Commit to daily skill-enhancing challenges
  • Solve practical, real-world issues
  • Share your insights and learnings
Create an account
Sign in
Recover lost password
Or log in with
Not registered?
Sign up takes less than a minute

Create a Free Account

Sign up to unlock all of IQCode features:
  • Test your skills and track progress
  • Engage in comprehensive interactive courses
  • Commit to daily skill-enhancing challenges
  • Solve practical, real-world issues
  • Share your insights and learnings
Create an account
Sign up
Or sign up with
By signing up, you agree to the Terms and Conditions and Privacy Policy. You also agree to receive product-related marketing emails from IQCode, which you can unsubscribe from at any time.
Creating a new code example
Code snippet title
Source