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 _______________________________________________ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor