pydata

Keep Looking, Don't Settle

python sqlite3 introduction

introduction

A quick introduction to python sqlite: how to create db, create table, insert values, update table and select from the table.

1. create db, create table and insert values into the table

import sqlite3

# 1. create db in the memory
createDB = sqlite3.connect(':memory:')

# or create db in the hard drive
# createDB = sqlite3.connect('D:\sqlite.db')

# 2. define cursor
c = createDB.cursor()

# 3. create table
def createTable():
    c.execute('''CREATE TABLE customer
    (id INTEGER PRIMARY KEY, name TEXT, street TEXT, city TEXT,
    state TEXT, balance REAL)''')

# 4. add data into the table
def addCust(name, street, city, state, balance):
    c.execute('''INSERT INTO customer (name, street, city, state, balance)
    VALUES (?, ?, ?, ?, ?)''', (name, street, city, state, balance))

def main():
    createTable()

    addCust("H Song", '302 Kentucky St', 'RiverC', 'CA', 188.10)
    addCust("A Song", '202 Kentucky St', 'RiverC', 'CA', 100.30)
    addCust("B Song", '102 Kentucky St', 'RiverC', 'CA', 10.83)
    addCust("C Song", '101 Kentucky St', 'RiverC', 'CA', 100.01)
    addCust("D Song", '301 Kentucky St', 'RiverC', 'CA', 90.10)
    addCust("E Song", '303 Kentucky St', 'RiverC', 'CA', 60.10)

    createDB.commit()

    c.execute('SELECT * FROM customer')

    for i in c:
        print '\n'
        for j in i:
            print j


main()

1
H Song
302 Kentucky St
RiverC
CA
188.1


2
A Song
202 Kentucky St
RiverC
CA
100.3


3
B Song
102 Kentucky St
RiverC
CA
10.83


4
C Song
101 Kentucky St
RiverC
CA
100.01


5
D Song
301 Kentucky St
RiverC
CA
90.1


6
E Song
303 Kentucky St
RiverC
CA
60.1

2. alter table, add new column and update the value

import sqlite3

createDB = sqlite3.connect(':memory:')

c = createDB.cursor()

def createTable():
    c.execute('''CREATE TABLE customer
    (id INTEGER PRIMARY KEY, name TEXT, street TEXT, city TEXT,
    state TEXT, balance REAL)''')

def addCust(name, street, city, state, balance):
    c.execute('''INSERT INTO customer (name, street, city, state, balance)
    VALUES (?, ?, ?, ?, ?)''', (name, street, city, state, balance))

def main():
    createTable()

    addCust("H Song", '302 Kentucky St', 'RiverC', 'CA', 188.10)
    addCust("A Song", '202 Kentucky St', 'RiverC', 'CA', 100.30)
    addCust("B Song", '102 Kentucky St', 'RiverC', 'CA', 10.83)
    addCust("C Song", '101 Kentucky St', 'RiverC', 'CA', 100.01)
    addCust("D Song", '301 Kentucky St', 'RiverC', 'CA', 90.10)
    addCust("E Song", '303 Kentucky St', 'RiverC', 'CA', 60.10)

    createDB.commit()

    c.execute('ALTER TABLE customer ADD COLUMN email TEXT')
    c.execute('UPDATE customer SET email = "hs@gm.me" where name = "H Song"')
    c.execute('DELETE FROM customer where id = 4')
    c.execute('SELECT * FROM customer ORDER BY balance DESC')


    for i in c:
        print '\n'
        for j in i:
            print j

main()

1
H Song
302 Kentucky St
RiverC
CA
188.1
hs@gm.me


2
A Song
202 Kentucky St
RiverC
CA
100.3
None


5
D Song
301 Kentucky St
RiverC
CA
90.1
None


6
E Song
303 Kentucky St
RiverC
CA
60.1
None


3
B Song
102 Kentucky St
RiverC
CA
10.83
None