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()
