- About SQLite
- Basic Commands
- Creating Tables
- Deleting Tables
- Interfacing with Python
Most SQL databases work with a client/server model. In contrast, SQLite databases operate from single, cross-platform portable files. They can be stored on various storage devices and can be transferred across different computers.
Creating a database from scratch and reading a pre-existing database file works the same way:
Meta Commands in SQLite always start with a dot (
.), and don’t require a closing semicolon (
|Display table schema||
|Set mode for output table||
|Import data to a table||
-- Select everything SELECT * FROM my_table; -- Select columns SELECT column_a, column_b, column_d FROM my_table; -- Select rows SELECT * FROM my_table WHERE column_a = x; -- Select top 5 rows SELECT * FROM my_table LIMIT 5;
Delete data from a table:
DELETE FROM my_table WHERE column_a = x;
SQLite uses Manifest Typing. Manifest Typing releases many restrictions on the type of value that can be entered for a particular field. This allows you to enter any value of any datatype into a column, irrespective of the declared type of the column (except for INTEGER PRIMARY KEY).
CREATE TABLE comments ( post_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT NOT NULL, website_url TEXT NULL, comment TEXT NOT NULL );
You can also create a table from a csv file. If the table that you are importing into already exists, then the first row of the csv is interpreted as data. If it does not exist, it is created, and the first row of the csv is interpreted as a header.
-- Set import mode to csv .mode csv .import my_file.csv my_table
There are five datatypes in SQLite3:
SQLite3 does not have Boolean or Date and Time datatypes. Booleans are commonly stored as integers (1 or 0) and Date/time is stored as either text (ISO8601 strings), real numbers (Julian day numbers), or integers (UNIX time), using the built-in date and time functions.
Delete a table if it exists:
DROP TABLE IF EXISTS my_table
Interfacing with Python
We can use the sqlite3 Python module to connect to a database, and read a table into a Pandas dataframe.
# Connect to SQLite database conn = sqlite3.connect("my_database.db") c = conn.cursor() # Read table into Pandas df = pd.read_sql("select * from my_table", conn)