boB Stepp wrote:
> While reading about SQL, SQLite and the Python module sqlite3, it
> appears that I could (1) have a program check for the existence of the
> program's database, and if not found, create it, make the tables,
> etc.; or,
I'm only a casual user of sqlite, and therefore I prefer to have the table
definitions in the script. It's as easy writing
create table if not exists foo ...
instead of
create table foo ...
This also allows you to easily add tables later on.
> (2) create the database separately and include it with the
> program. What are the pros and cons of each approach? (1) to me
> looks only helpful if I wish to write a program that might want to
> allow the user to have multiple databases.
Even if the user needs only a single db you should make that configurable to
allow for testing.
> But this sounds like a lot
> of extra coding and checking to make it work well.
I don't think so. You need the database schema, and once you have that it
doesn't matter if you build one or 1000 databases from it.
> But if I only wish
> to have a single database, then (2) sounds like the approach to use.
> I would create the database, populate it with the needed empty tables
> with the desired fields, making it ready to use by the program's user.
>
> Not having any experience in the database arena, I'm not even sure I
> know how to properly think about this.
Here's a little demo script to get you going. There's only a single table,
but the principle works with any number of tables and indices.
$ ls
sqlitedemo.py
$ cat sqlitedemo.py
#!/usr/bin/env python3
import sqlite3
from itertools import chain
from contextlib import closing
def ensure_db(filename):
db = sqlite3.connect(filename)
with closing(db.cursor()) as cursor:
cursor.execute(
"create table if not exists addresses (name, email);"
)
return db
def insert_action(args, parser):
with ensure_db(args.database) as db:
with closing(db.cursor()) as cursor:
cursor.execute(
"insert into addresses values (?, ?);",
(args.name, args.email)
)
def fix_none(row):
return [
"NULL"if value is None else value
for value in row
]
def show_action(args, parser):
with ensure_db(args.database) as db:
with closing(db.cursor()) as cursor:
cursor.execute("select * from addresses order by name")
fieldnames = [
column_desc[0]
for column_desc in cursor.description
]
template = " | ".join(("{:20}",)*len(fieldnames))
separator = ["-" * 20] * len(fieldnames)
for row in chain([fieldnames, separator], cursor):
print(template.format(*fix_none(row)))
def main():
import argparse
parser = argparse.ArgumentParser()
parser.add_argument("-d", "--database", default="default.sqlite")
sub = parser.add_subparsers()
insert = sub.add_parser("insert")
insert.add_argument("name")
insert.add_argument("email")
insert.set_defaults(func=insert_action)
show = sub.add_parser("show")
show.set_defaults(func=show_action)
args = parser.parse_args()
args.func(args, parser)
if __name__ == "__main__":
main()
$ ./sqlitedemo.py show
name | email
-------------------- | --------------------
$ ls
default.sqlite sqlitedemo.py
$ ./sqlitedemo.py insert jim j...@example.com
$ ./sqlitedemo.py insert sue s...@elsewhere.org
$ ./sqlitedemo.py show
name | email
-------------------- | --------------------
jim | j...@example.com
sue | s...@elsewhere.org
$ ./sqlitedemo.py -d otherdb insert peter peter@uknowwhere
$ ls
default.sqlite otherdb sqlitedemo.py
$ ./sqlitedemo.py -d otherdb show
name | email
-------------------- | --------------------
peter | peter@uknowwhere
PS: I don't know if it makes sense to close the cursors, I just thought it
looked clean when I added it to ensure_db() and ended up adding it
everywhere.
_______________________________________________
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
