sqlite.sql

sqlite3 test.sqlite3

sqlite> create table awesome_video_games(name text, type text);
sqlite> insert into awesome_video_games values('fallout', 'rpg');
sqlite> insert into awesome_video_games values('final fantasy', 'rpg');
sqlite> insert into awesome_video_games values('deus ex', 'rpg');
sqlite> insert into awesome_video_games values('hitman', 'stealth');
sqlite> select * from awesome_video_games where type='rpg';
fallout|rpg
final fantasy|rpg
deus ex|rpg


sqlite> .tables
awesome_video_games
sqlite> .schema awesome_video_games
CREATE TABLE awesome_video_games(name text, type text);

导入数据
sqlite> create table failed_banks(name text, city text, state text, 
    zip integer, close_date, updated_date text)
sqlite> .separator ","
sqlite> .import banklist.csv failed_banks

导入数据 via Python
import sqlite3;
from datetime import datetime, date;
 
conn = sqlite3.connect('banklist.sqlite3')
c = conn.cursor()
c.execute('drop table if exists failed_banks')
c.execute('create table failed_banks(id integer primary key autoincrement, 
    name text, city text, state text, zip integer, close_date text, updated_date text)')

a = "Unity National Bank,Cartersville,GA,34678,26-Mar-10,26-Mar-10"
a.split(",")
['Unity National Bank', 'Cartersville', 'GA', '34678', '26-Mar-10', '26-Mar-10']

def mysplit (string):
    quote = False
    retval = []
    current = ""
    for char in string:
        if char == '"':
            quote = not quote
        elif char == ',' and not quote:
            retval.append(current)
            current = ""
        else:
            current += char
    retval.append(current)
    return retval

# Read lines from file, skipping first line
data = open("banklist.csv", "r").readlines()[1:]
for entry in data:
    # Parse values
    vals = mysplit(entry.strip())
    # Convert dates to sqlite3 standard format
    vals[4] = datetime.strptime(vals[4], "%d-%b-%y")
    vals[5] = datetime.strptime(vals[5], "%d-%b-%y")
    # Insert the row!
    print "Inserting %s..." % (vals[0])
    sql = "insert into failed_banks values(NULL, ?, ?, ?, ?, ?, ?)"
    c.execute(sql, vals)

# Done!
conn.commit()