Python comes with module sqlite3 to create and process databases that can be queried with its common query language. First, let's create a database file ...
# explore Python module sqlite3
# create a database file
# the query language is in optional upper case
# using unique id numbers prevents appending an existing db file
# tested with Python27 and Python32
import sqlite3
# create/connect to a permanent database file
file_name = "stock_portfolio2.db3"
con = sqlite3.connect(file_name)
# establish the cursor
cur = con.cursor()
# if it doesn't exist yet, create the table named stocks
# give each transaction a unique id number
cur.execute('''CREATE TABLE IF NOT EXISTS stocks
(id INT PRIMARY KEY, date TEXT, trans TEXT,
symbol TEXT, qty REAL, price REAL)''')
# insert several lines at once using a
# list of (id, date, trans, symbol, qty, price) tuples
# each tranaction is given a unique id for data security
# the unique id primary key prevents any existing database file
# from being appended with a potentially conflicting data id
try:
stocks = [
(100, '2011-05-07', 'buy', 'AZN', 500, 55.05),
(101, '2011-05-07', 'buy', 'CAT', 200, 110.34),
(102, '2011-05-07', 'sell', 'IBM', 100, 168.89),
(103, '2011-05-07', 'buy', 'GE', 2000, 20.01)
]
# the ? placeholders match tuple items
cur.executemany("""INSERT INTO stocks
VALUES (?, ?, ?, ?, ?, ?)""", stocks)
except:
pass
# commit current data to the db file
con.commit()
# quickly test the database, in sorted order by symbol
cur.execute('SELECT * FROM stocks ORDER BY symbol')
# fetch all data rows
for row in cur.fetchall():
print(row)
'''
(100, u'2011-05-07', u'buy', u'AZN', 500.0, 55.05)
(101, u'2011-05-07', u'buy', u'CAT', 200.0, 110.34)
(103, u'2011-05-07', u'buy', u'GE', 2000.0, 20.01)
(102, u'2011-05-07', u'sell', u'IBM', 100.0, 168.89)
'''
# done
con.close()
Now let's use the database file we just created ...
# explore Python module sqlite3
# read an existing database file
# query language in upper case (optional)
# tested with Python27 and Python32
import sqlite3
# connect to the permanent database file
file_name = "stock_portfolio2.db3"
con = sqlite3.connect(file_name)
# establish the cursor
cur = con.cursor()
# use cursor method execute() to test the database
cur.execute('SELECT * FROM stocks')
col_name_list = [tup[0] for tup in cur.description]
print("Table header:")
print(col_name_list)
'''
Table header:
['id', 'date', 'trans', 'symbol', 'qty', 'price']
'''
print('-'*50)
cur.execute('SELECT * FROM stocks')
# fetch first data row
row = cur.fetchone()
print(row)
'''result Python32 ...
(100, '2011-05-07', 'buy', 'AZN', 500.0, 55.05)
'''
print('-'*50)
cur.execute('SELECT id FROM stocks')
# fetch id numbers of all data rows
for row in cur.fetchall():
print(row)
'''
(100,)
(101,)
(102,)
(103,)
'''
print('-'*50)
cur.execute('SELECT trans, qty, symbol, date FROM stocks')
# fetch selected items of all data rows
for row in cur.fetchall():
print("%s %s %s on %s" % row)
'''
buy 500.0 AZN on 2011-05-07
buy 200.0 CAT on 2011-05-07
sell 100.0 IBM on 2011-05-07
buy 2000.0 GE on 2011-05-07
'''
# done
con.close()
I hope you can dream up a better example of a database.