In this Python tutorial, I will be covering how to use SQLite3 database in Python.

SQLite database is a light-weighted open-source file-based relational #database system that can be used with just about all programming languages, such as C#, C++, Java, and Python. And since SQLite library is already included as one of the Python standard modules, there is no 3rd party library installation required.

I will cover all the topics I think are important to get you to hit the ground running; I hope by the end of this video, you will be able to successfully manage your own SQLite database using Python.

Python SQLite3 Documentation: https://docs.python.org/3/library/sqlite3.html

Download DB Browser: https://sqlitebrowser.org/

Buy Me a Coffee? Your support is much appreciated!
PayPal Me: https://www.paypal.me/jiejenn/5
Venmo: @Jie-Jenn





import sqlite3

conn = sqlite3.connect('demo.db')
cursor = conn.cursor()

sql = {}

"""
Customers Table Creation
"""
sql['DropCustomerTable'] = \
    """
        DROP TABLE IF EXISTS Customers;
    """

sql['CreateCustomerTable'] = \
    """
    CREATE TABLE Customers (
            Customer_Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            Customer_Name TEXT NOT NULL,
            Credit_Rating INTEGER NOT NULL,
            Birthday DATE NULL
        );
    """

cursor.execute(sql['DropCustomerTable'])
cursor.execute(sql['CreateCustomerTable'])
conn.commit()

"""
Records Creation
"""
sql['CustomerRecords'] = \
    """
    INSERT INTO Customers (Customer_Name, Credit_Rating, Birthday)
    VALUES
        ('Jay', 830, '2000-4-10'),
        ('Mary', 730, '2000-05-10')
    """
cursor.execute(sql['CustomerRecords'])
conn.commit()

"""
Insert Date Values
"""
sql['sql_insert'] = \
    """
    INSERT INTO Customers (Customer_Name, Credit_Rating, Birthday)
        VALUES (?, ?, ?)
    """

import datetime
cursor.execute(sql['sql_insert'], ('Steve', 700, datetime.date(1945, 4, 10)))
conn.commit()

"""
Update Records
"""
sql['UpdateCustomerCreditRating'] = \
    """
    UPDATE Customers
    SET 
        Credit_Rating = ?
    WHERE Customer_Name = ?
    """

cursor.execute(sql['UpdateCustomerCreditRating'], (800, 'Mary'))
conn.commit()


"""
SELECT Statement
"""
cursor.execute("SELECT * FROM Customers")
cursor.fetchone()

cursor.execute("SELECT * FROM Customers")
cursor.fetchall()

cursor.execute("SELECT * FROM Customers")
cursor.fetchmany(2)

"""
Resultset Count
"""
cursor.rowcount


sql['UpdateCustomerCreditRating'] = \
    """
    UPDATE Customers
    SET 
        Credit_Rating = 999
    """

cursor.execute(sql['UpdateCustomerCreditRating'])
conn.commit()

cursor.rowcount

"""
BULK INSERT/UPDATE/DELETE
"""
records = (
    ('Steve Jobs', 999, '1955-02-24'),
    ('Bill Gates', 999, '1955-10-28'),
    ('Larry Page', 999, '1973-03-26')
)

sql_insert = \
    """
    INSERT INTO Customers (Customer_Name, Credit_Rating, Birthday)
        VALUES (?, ?, ?)
    """
cursor.executemany(sql_insert, records)
conn.commit()


"""
System Tables
"""
cursor.execute("SELECT * FROM sqlite_master")
cursor.fetchall()

fields = [desc[0] for desc in cursor.description]
print(fields)

conn.close()