SQLite3 basics and interaction with Python

SQLite3 basics and interaction with Python

Record of basic commands for sqlite3, as well as database operations in Python.

SQLite3

Create a new database or open the existing database (on the command line)

> sqlite3 FirstDB.db

Then you will go to the sqlite3 with database opened (indicated by “sqlite>”)

Create a table:

sqlite> CREATE TABLE Person (
   ...> firstName TEXT(30) NOT NULL,  ## MySQL uses VARCHAR instead of TEXT
   ...> lastName TEXT(30) NOT NULL,
   ...> birthYear INTEGER,
   ...> PRIMARY KEY (firstName, lastName)
   ...> );

Add record to the table:

INSERT INTO Person (firstName, lastName, birthYear) VALUES ("See", "Ferry", "1990");
INSERT INTO Person (firstName, lastName, birthYear) VALUES ("Mountain", "Bike", "1966");

Update record:

UPDATE Person SET birthYear=1980 WHERE lastName='Bike';

Query the table:

SELECT firstName from Person WHERE birthYear > 1988;

Add new column to table:

sqlite> ALTER TABLE Person
   ...> ADD height INT;

Delete a record or all records:

DELETE FROM Person WHERE lastName='Ferry';
DELETE FROM Person;

Show headers for tables and show as column:

sqlite> .headers ON
sqlite> .mode column

Show all records in a table:

SELECT * FROM Person WHERE 1;

Remove duplicate rows (identical in firstName, lastName, birthYear):

DELETE FROM Person WHERE rowid NOT IN (SELECT min(rowid) FROM Person GROUP BY firstName, lastName, birthYear);

List all tables or databases:

sqlite> .tables
sqlite> .databases

Remove a table:

DROP TABLE Person;

Quit sqlite3

sqlite> .quit

Run commands from a script (i.e. sql):

sqlite3 Person < Operations.sql

Create database in Python

See the turorial here.

# Create a connection
def create_connection(db_file):
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)

    return None

# Cursor and execute the command
def create_table(conn, create_table_sql)
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)

# Create a main() function to CREATE TABLE
def main():
    database = "/Users/zl3/Documents/FirstDB.db"
    create_table = """CREATE TABLE IF NOT EXISTS Person (
                                    id integer PRIMARY KEY,
                                    firstName text NOT NULL,
				    lastName text NOT NULL,
				    birthYear ineger
                                );"""
 
    # create a database connection
    conn = create_connection(database)
    if conn is not None:
        # create table
        create_table(conn, create_table)
    else:
        print("Error! Cannot create the database connection.")

# Execute the main() function
if __name__ == '__main__':
    main()
#! /usr/bin/env python
# -*- coding: utf-8 -*-  # To accept special characters

import sqlite3

# establish a connection
connection = sqlite3.connect("/Users/zl3/Documents/FirstDB.db")

# hold a cursor
cursor = connection.cursor()

## new data in a list:
new_person = [
    ("Double-decker", "Bus", 2008),
    ("Electric", "Bike", 2006),
]

## insert into the table
for p in new_person:
    format_str = """INSERT INTO Person (firstName, lastName, birthYear)
        VALUES ("{first}", "{last}", "{birthyear}");"""
    sql_command = format_str.format(first=p[0], last=p[1], birthyear=p[2])
    cursor.execute(sql_command)

## Fetch all remaining records
cursor.execute("SELECT * FROM Person")
print("Fetchall:")
all_records = cursor.fetchall()
for r in all_records:
    print(r)

## Fetch the next record
cursor.execute("SELECT * FROM Person")
print("\nFetch one:")
one_record = cursor.fetchone()
print(one_record)

# close the cursor
cursor.close()

# Discard changes
# connection.rollback()

# Save the changes and close the connection:
connection.commit()
connection.close()
Z. Lu avatar
Z. Lu
Data scientist, bioinformatician, retro fan and web lover.
comments powered by Disqus