[Tutor] How is database creation normally handled?

Alan Gauld alan.gauld at yahoo.co.uk
Sun Sep 10 04:08:03 EDT 2017


On 10/09/17 01:29, 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, (2) create the database separately and include it with the
> program. 

Almost always number 2.

The slight variant on that is to put the SQL to create the
tables etc into a sql file and then if a database file does
not exist create the database by executing the sql script
(which you can do via the executescript() function)

One reason for doing it this way is that while SQL is
standardised the standardisation is mainly in the query
part of the language. Each database is quite different
in its data definition language (different data types,
features like triggers and stored procedures etc).

So by putting the DDL into a sql file that your program
just executes the non portable bit gets removed from your
Python code. You can even have multiple different
versions of the DDL file, one per database, and  your
Python code can remain pretty much oblivious to the
changes (not quite true, but its much easier).

Populating the data is another matter. I tend to do
that via a Spreadsheet/csv file and write a loader
in Python to loop over the data loading it into the
various tables then setting the table properties/constraints
as needed. (if you set all constraints before loading the
data you can get into a kind of data deadlock where
you can't get any initial data loaded!).

Other people will use a dedicated database GUI to do
initial data load. SQLiteManager is one such option
for Sqlite.

The last option is to just start with an empty database
and allow the code to populate it, but as mentioned,
if you have a lot of constraints in your database
design you can get to the point of not being able
to insert any data. So you usually need to populate
some standing data first. But in this case it can
be part of the creation script.

If you haven't come across database constraints yet,
its where you define criteria that must be true.
At a simple level its things like NOT NULL or UNIQUE.
But they can get quite complex like being a foreign
key, in which case the foreign object must exist
before you can create the dependant one. And when
you get combinations of constraints coupled to
triggers(which automatically execute SQL when
a database event occurs) it all gets very messy.

Done properly constraints are a powerful tool
to prevent data corruption. They are seductive in
their power and can save a lot of defensive
programming in the host application. But...
One common database beginners gotcha is to go mad
with constraints to the point that any database
action triggers a snowball of other actions and
performance slows to a crawl or even locks up.

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos




More information about the Tutor mailing list