Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
On 2017-10-19, Alan Gauld via Tutor wrote: > On 18/10/17 21:09, Albert-Jan Roskam wrote: >> >> On Oct 16, 2017 15:12, Neil Cerutti wrote: > >>> sqlite3 module. In sqlite modifying table definitions is limited >>> to the simple addition of a new row of data. > >> >> Really? How so? I used ALTER TABLE ADD COLUMN the other day, > > I think Neil meant that Sqlite ALTER is limited to adding > *columns*. Adding rows would be an INSERT not an ALTER. > > The only other thing SQLite ALTER can do is change the table name. > > But most SQLs allow you to modify existing column definitions > too, such as add defaults or conditions. You can't do that in > SQLite, you need to create a new table (TEMP)and copy the data > across then drop the old table and rename the new one to the > old. All a bit tedious. Yes, I meant sqlite can only add columns. Thanks for the corrections. -- Neil Cerutti ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
On 18/10/17 21:09, Albert-Jan Roskam wrote: > > On Oct 16, 2017 15:12, Neil Cerutti wrote: >> sqlite3 module. In sqlite modifying table definitions is limited >> to the simple addition of a new row of data. > > Really? How so? I used ALTER TABLE ADD COLUMN the other day, I think Neil meant that Sqlite ALTER is limited to adding *columns*. Adding rows would be an INSERT not an ALTER. The only other thing SQLite ALTER can do is change the table name. But most SQLs allow you to modify existing column definitions too, such as add defaults or conditions. You can't do that in SQLite, you need to create a new table (TEMP)and copy the data across then drop the old table and rename the new one to the old. All a bit tedious. -- 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
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
On Oct 16, 2017 15:12, Neil Cerutti wrote: > > On 2017-10-15, boB Stepp wrote: > > Some things I am still pondering: > > > > 1) If I adopt the incremental approach to creating and > > initializing the working db, then it seems that the list, > > "sql_scripts", should not be hard-coded into the program. It > > seems to me it should be off somewhere by itself with perhaps > > other things that might evolve/change over time in its own file > > where it (and its brethren) are easy to locate and update. > > An incremental approach is not recommended if you are using the > sqlite3 module. In sqlite modifying table definitions is limited > to the simple addition of a new row of data. Any other change > requires you to create a new table, copy the old data into it, > and then drop the old table. Really? How so? I used ALTER TABLE ADD COLUMN the other day, which is part of this SQL dialect: https://sqlite.org/lang_altertable.html ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
On 2017-10-15, boB Stepp wrote: > Some things I am still pondering: > > 1) If I adopt the incremental approach to creating and > initializing the working db, then it seems that the list, > "sql_scripts", should not be hard-coded into the program. It > seems to me it should be off somewhere by itself with perhaps > other things that might evolve/change over time in its own file > where it (and its brethren) are easy to locate and update. An incremental approach is not recommended if you are using the sqlite3 module. In sqlite modifying table definitions is limited to the simple addition of a new row of data. Any other change requires you to create a new table, copy the old data into it, and then drop the old table. > 3) I am supposed to be delving into writing classes on this > project. Should the code so far stay as a function or get > incorporated into a class? My original intent was to do a > class for the BloodPressureReadings table, but I am not at the > point of going there yet. I don't recommend cobbling together your own ORM, if that's what you are asking. ;) > 4) I wish there was a PEP 8 for SQL! I have several SQL books > I have consulted, but I have gotten conflicting suggestions for > SQL code style. I have tried to adopt something that seems to > me to be both consistent and reasonable, but is it good enough? As long as you can imagine yourself reading it two years from now, and being able to understand it and make changes, it's probably an OK style. The two big hurdles for me were acquiescing to uppercase all the SQL keywords, and learning the quoting rules. My impression is that SQL is very old, and so lots of different styles are valid and just fine to use. Your main goal, at first, should be to stick with standard SQL as much as you possibly can, to make it easier to switch database engines should you ever wish to do so. -- Neil Cerutti ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
boB Stepp wrote: > I was thinking ahead to a follow-up project, the chess rating db. For > this I had contemplated having separate sqlite3 database files for > each school year instead of adding school year information to a single > database. Then you need to access multiple databases to see the full history of a student and you will end up with a lot of redundant information (name, birthday, contact information); if there's an error in the data it will typically only be corrected for the latest year, your users will come up with creative ways to copy data from one year to the next, introducing errors in the process... In short: it will be a be a mess. ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
boB Stepp wrote:
> On Sun, Oct 15, 2017 at 3:09 AM, Peter Otten <__pete...@web.de> wrote:
>> boB Stepp wrote:
>>
>>> I have not used a "finally" block before. I just had the thought that
>>> maybe it would run even if an uncaught exception might occur. I tried
>>> to test this thought by generating a deliberate NameError in the "try"
>>> block and added a print to the "finally" clause. I got the intended
>>> NameError with no evidence of the added print printing. But I thought
>>> I would ask just to be sure: If an uncaught exception occurs, will
>>> the "finally" clause execute?
>>
>>
>> Yes.
>>
> try:
>> ... 1/0
>> ... except ValueError:
>> ... print("not triggered")
>> ... finally:
>> ... print("ALWAYS TRIGGERED")
>> ...
>> ALWAYS TRIGGERED
>> Traceback (most recent call last):
>> File "", line 2, in
>> ZeroDivisionError: division by zero
>
> That is what I thought after reading about "finally". But look what
> happens if I modify my actual code to generate a NameError:
> def ensure_db(filename):
> """Open the database, "filename", if it exists; otherwise, create a
> database named "filename"."""
>
> db = sqlite3.connect(filename)
> cur = db.cursor()
>
> try:
> sql_cmd = "SELECT VersionNumber FROM CurrentDBVersion"
> a# This should generate a NameError
Note that at this point `current_db_version` is not yet defined.
> # First element of returned tuple will be the db version number:
> current_db_version = int(cur.execute(sql_cmd).fetchone()[0])
>
> except sqlite3.OperationalError:
> # This means that the database and the table, "CurrentDBVersion",
> # has not yet been created, implying "version 0".
> current_db_version = 0
>
> finally:
> sql_scripts = ["../database/create_sqlite3_db.sql"]
> for sql_scriptname in sql_scripts[current_db_version:]:
The finally suite was entered, but now there's another NameError (an
UnboundLocalError, to be precise), for current_db_version, inside it. Code
inside the finally suite is executed like it would be anywhere else, so
anything after the point where the exception was triggered is not run.
> with open(sql_scriptname) as f:
> cur.executescript(f.read())
> print("THIS IS THE FINALLY BLOCK!!!") # And this *should* print
>
> return db
> This results in the following Traceback:
>> py main.py
> Traceback (most recent call last):
> File "main.py", line 16, in ensure_db
> a
> NameError: name 'a' is not defined
>
> During handling of the above exception, another exception occurred:
>
> Traceback (most recent call last):
> File "main.py", line 36, in
> ensure_db(db_filename)
> File "main.py", line 27, in ensure_db
> for sql_scriptname in sql_scripts[current_db_version:]:
> UnboundLocalError: local variable 'current_db_version' referenced
> before assignment
While I tend to find chained exceptions annoying they *do* provide all the
necessary information.
> So what is going on here? Why does "finally" not have its print
> executed? Does the "...another exception occurred:..." interrupt the
> normal flow of the "try/except/finally" structure and prevent the
> "finally" block from executing?
>
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
On Sun, Oct 15, 2017 at 2:56 AM, Peter Otten <__pete...@web.de> wrote: > boB Stepp wrote: >> >> I am puzzled. If one is using version control, then what is the >> advantage of this incremental approach in creating and populating the >> database? Instead, why not just have a single SQL file that generates >> the finished initial state of the db for the current version? One can >> always use the version control system to roll back to an earlier >> stage. > > My idea was presented under the assumption that the there is user data > entered in version 1 that needs to be preserved when version 2 of the > application replaces 1. Ah! I was missing the forest for the trees. Sorry 'bout that! >> I have chickened out and not done a TDD approach yet. I will probably >> pause here, wait for feedback from this list, and try to figure out >> how I should test what I have so far. And how do you test SQL scripts >> anyway? > > Example: Run the routine to enter a row, then check if it's there and > contains what you expected. That should fail before the script is run, and > succeed afterwards. I guess what bothers me here is that it seems I have to write some code in the tests file just to get the test database to the point where I can write the necessary asserts. But I would have to do that anyway, wouldn't I? The whole point of test fixtures, setup and tear down code, etc. It just looks like testing dbs will be somewhat messier than what I have dealt with to date. >> >> Some things I am still pondering: >> >> 1) If I adopt the incremental approach to creating and initializing >> the working db, then it seems that the list, "sql_scripts", should not >> be hard-coded into the program. It seems to me it should be off >> somewhere by itself with perhaps other things that might evolve/change >> over time in its own file where it (and its brethren) are easy to >> locate and update. > > I think it should be hardcoded. You don't want to run arbitrary scripts that > happen to be in a folder, say. Version control can take care of any changes. In some ways I think too much. I struggle here on what should be my best practice, to minimize the amount of hard-coded data (I think usually a good idea.) or, in a particular case like this one, to do the hard-coding. >> >> 2) Likewise, "db_filename", is currently hard-coded in the if block >> to start the program. I have not decided yet what the end result will >> be, but I might want to allow for the possibility of allowing the user >> (me) to create multiple databases. > > If one user needs multiple databases that /may/ be an indication that you > are not storing enough information in the database. I was thinking ahead to a follow-up project, the chess rating db. For this I had contemplated having separate sqlite3 database files for each school year instead of adding school year information to a single database. -- boB ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
On Sun, Oct 15, 2017 at 3:09 AM, Peter Otten <__pete...@web.de> wrote:
> boB Stepp wrote:
>
>> I have not used a "finally" block before. I just had the thought that
>> maybe it would run even if an uncaught exception might occur. I tried
>> to test this thought by generating a deliberate NameError in the "try"
>> block and added a print to the "finally" clause. I got the intended
>> NameError with no evidence of the added print printing. But I thought
>> I would ask just to be sure: If an uncaught exception occurs, will
>> the "finally" clause execute?
>
>
> Yes.
>
try:
> ... 1/0
> ... except ValueError:
> ... print("not triggered")
> ... finally:
> ... print("ALWAYS TRIGGERED")
> ...
> ALWAYS TRIGGERED
> Traceback (most recent call last):
> File "", line 2, in
> ZeroDivisionError: division by zero
That is what I thought after reading about "finally". But look what
happens if I modify my actual code to generate a NameError:
def ensure_db(filename):
"""Open the database, "filename", if it exists; otherwise, create a
database named "filename"."""
db = sqlite3.connect(filename)
cur = db.cursor()
try:
sql_cmd = "SELECT VersionNumber FROM CurrentDBVersion"
a# This should generate a NameError
# First element of returned tuple will be the db version number:
current_db_version = int(cur.execute(sql_cmd).fetchone()[0])
except sqlite3.OperationalError:
# This means that the database and the table, "CurrentDBVersion", has
# not yet been created, implying "version 0".
current_db_version = 0
finally:
sql_scripts = ["../database/create_sqlite3_db.sql"]
for sql_scriptname in sql_scripts[current_db_version:]:
with open(sql_scriptname) as f:
cur.executescript(f.read())
print("THIS IS THE FINALLY BLOCK!!!") # And this *should* print
return db
This results in the following Traceback:
> py main.py
Traceback (most recent call last):
File "main.py", line 16, in ensure_db
a
NameError: name 'a' is not defined
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "main.py", line 36, in
ensure_db(db_filename)
File "main.py", line 27, in ensure_db
for sql_scriptname in sql_scripts[current_db_version:]:
UnboundLocalError: local variable 'current_db_version' referenced
before assignment
So what is going on here? Why does "finally" not have its print
executed? Does the "...another exception occurred:..." interrupt the
normal flow of the "try/except/finally" structure and prevent the
"finally" block from executing?
--
boB
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
boB Stepp wrote:
> I have not used a "finally" block before. I just had the thought that
> maybe it would run even if an uncaught exception might occur. I tried
> to test this thought by generating a deliberate NameError in the "try"
> block and added a print to the "finally" clause. I got the intended
> NameError with no evidence of the added print printing. But I thought
> I would ask just to be sure: If an uncaught exception occurs, will
> the "finally" clause execute?
Yes.
>>> try:
... 1/0
... except ValueError:
... print("not triggered")
... finally:
... print("ALWAYS TRIGGERED")
...
ALWAYS TRIGGERED
Traceback (most recent call last):
File "", line 2, in
ZeroDivisionError: division by zero
If something seems too complex in your real code try to come up with a
similar setup in the interactive interpreter (or use a minimal demo script
if that proves impractical).
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
boB Stepp wrote: > On Sat, Oct 14, 2017 at 4:45 AM, Peter Otten <__pete...@web.de> wrote: > >> If this is a long term project there will be changes in the schema. >> However, I don't think it is necessary to check for individual tables. >> You typically start with a few tables >> >> create table alpha >> create table beta >> create table gamma >> >> and later add a few more or change columns >> >> alter table alpha >> create table delta >> >> In this example you have three versions of the database >> >> version 0: empty >> version 1: three tables >> version 2: four tables, one table modified >> >> If you add a table for your own metadata you ownly need to store that >> version number. The necessary steps when you open the database are then >> >> - read version from bobs_metadata (if that fails you are at version 0) >> >> If you are at version 0 "migrate" to version one: >> >> - execute script that creates alpha, beta, gamma, and bobs_metadata, the >> latter with one row containing version=1 >> >> If you are at version 1 migrate to version two: >> >> - execute migration script from 1 to 2 modifying alpha, creating delta, >> and >> updating to version=2 >> >> If you are at version 2: >> >> - do nothing, database is already in the state required by your >> application. > > I am puzzled. If one is using version control, then what is the > advantage of this incremental approach in creating and populating the > database? Instead, why not just have a single SQL file that generates > the finished initial state of the db for the current version? One can > always use the version control system to roll back to an earlier > stage. My idea was presented under the assumption that the there is user data entered in version 1 that needs to be preserved when version 2 of the application replaces 1. > I have chickened out and not done a TDD approach yet. I will probably > pause here, wait for feedback from this list, and try to figure out > how I should test what I have so far. And how do you test SQL scripts > anyway? Example: Run the routine to enter a row, then check if it's there and contains what you expected. That should fail before the script is run, and succeed afterwards. > > Some things I am still pondering: > > 1) If I adopt the incremental approach to creating and initializing > the working db, then it seems that the list, "sql_scripts", should not > be hard-coded into the program. It seems to me it should be off > somewhere by itself with perhaps other things that might evolve/change > over time in its own file where it (and its brethren) are easy to > locate and update. I think it should be hardcoded. You don't want to run arbitrary scripts that happen to be in a folder, say. Version control can take care of any changes. > > 2) Likewise, "db_filename", is currently hard-coded in the if block > to start the program. I have not decided yet what the end result will > be, but I might want to allow for the possibility of allowing the user > (me) to create multiple databases. If one user needs multiple databases that /may/ be an indication that you are not storing enough information in the database. Bad: one database per patient. You are using the file system as a meta- database. Better: an additional patients table and a column patientid in all tables containing patient data. > Also, when I figure out how to > test this database stuff, I imagine I will be using a test db for the > testing, not the actual one. Again, this argues for not hard-coding > the database name. > > 3) I am supposed to be delving into writing classes on this project. > Should the code so far stay as a function or get incorporated into a > class? My original intent was to do a class for the > BloodPressureReadings table, but I am not at the point of going there > yet. Relax. A function is an instance of a class with no state and a __call__ method ;) > 4) I wish there was a PEP 8 for SQL! I have several SQL books I have > consulted, but I have gotten conflicting suggestions for SQL code > style. I have tried to adopt something that seems to me to be both > consistent and reasonable, but is it good enough? > > I await the arrival of the list's wisdom! > > Cheers! ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
On Sun, Oct 15, 2017 at 12:07 AM, boB Stepp wrote: > > #!/usr/bin/env python3 > > """This file starts the blood pressure readings program.""" > > import sqlite3 > > def ensure_db(filename): > """Open the database, "filename", if it exists; otherwise, create a > database named "filename".""" > > db = sqlite3.connect(filename) > cur = db.cursor() > > try: > sql_cmd = "SELECT VersionNumber FROM CurrentDBVersion" > > # First element of returned tuple will be the db version number: > current_db_version = int(cur.execute(sql_cmd).fetchone()[0]) > > except sqlite3.OperationalError: > # This means that the database and the table, "CurrentDBVersion", has > # not yet been created, implying "version 0". > current_db_version = 0 > > finally: > sql_scripts = ["../database/create_sqlite3_db.sql"] > for sql_scriptname in sql_scripts[current_db_version:]: > with open(sql_scriptname) as f: > cur.executescript(f.read()) > > return db I have not used a "finally" block before. I just had the thought that maybe it would run even if an uncaught exception might occur. I tried to test this thought by generating a deliberate NameError in the "try" block and added a print to the "finally" clause. I got the intended NameError with no evidence of the added print printing. But I thought I would ask just to be sure: If an uncaught exception occurs, will the "finally" clause execute? > > I have chickened out and not done a TDD approach yet. I will probably > pause here, wait for feedback from this list, and try to figure out > how I should test what I have so far. And how do you test SQL scripts > anyway? > > Some things I am still pondering: I forgot to add (5): 5) How should I handle the cursor object? When I should I close it? My function returns "db" which I presume keeps the connection to the database. But what happens to the cursor object as I have written the function, since I never closed it? -- boB ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
On Sat, Oct 14, 2017 at 4:45 AM, Peter Otten <__pete...@web.de> wrote: > If this is a long term project there will be changes in the schema. > However, I don't think it is necessary to check for individual tables. You > typically start with a few tables > > create table alpha > create table beta > create table gamma > > and later add a few more or change columns > > alter table alpha > create table delta > > In this example you have three versions of the database > > version 0: empty > version 1: three tables > version 2: four tables, one table modified > > If you add a table for your own metadata you ownly need to store that > version number. The necessary steps when you open the database are then > > - read version from bobs_metadata (if that fails you are at version 0) > > If you are at version 0 "migrate" to version one: > > - execute script that creates alpha, beta, gamma, and bobs_metadata, the > latter with one row containing version=1 > > If you are at version 1 migrate to version two: > > - execute migration script from 1 to 2 modifying alpha, creating delta, and > updating to version=2 > > If you are at version 2: > > - do nothing, database is already in the state required by your application. I am puzzled. If one is using version control, then what is the advantage of this incremental approach in creating and populating the database? Instead, why not just have a single SQL file that generates the finished initial state of the db for the current version? One can always use the version control system to roll back to an earlier stage. > Pseudo-code: > > scripts = ["0to1.sql", "1to2.sql"] > current_version = get_schema_version() # this has to catch the ># OperationalError > for scriptname in scripts[current_version:]: > with open(scriptname) as f: > script = f.read() > executescript(script) My current project structure for this program: blood_pressures/ |.git/ |blood_pressures/ ||--__init__.py ||--main.py |database/ ||--__init__.py# Is this really needed in this folder? ||--blood_pressure.db ||--create_sqlite3_db.sql |tests/ ||--__init__.py# Oh, no! No tests yet!! .gitignore The current SQL file, create_sqlite3_db.sql: -- This file will create version 1 of the database. CREATE TABLE BloodPressureReadings ( ReadingID INTEGER PRIMARY KEY, Date TEXT, Time TEXT, SystolicBP INTEGER, DiastolicBP INTEGER, Comments TEXT); -- This table is intended to only have one row with one entry: the current -- database version. Whenever the program is started this entry will be checked -- to determine if the database needs to be updated (or not) to the current -- version from the earlier SQL database creation files. CREATE TABLE CurrentDBVersion ( VersionNumber INTEGER); -- Insert starting version number of database: INSERT INTO CurrentDBVersion (VersionNumber) VALUES (1); And my current effort to implement your guidance: #!/usr/bin/env python3 """This file starts the blood pressure readings program.""" import sqlite3 def ensure_db(filename): """Open the database, "filename", if it exists; otherwise, create a database named "filename".""" db = sqlite3.connect(filename) cur = db.cursor() try: sql_cmd = "SELECT VersionNumber FROM CurrentDBVersion" # First element of returned tuple will be the db version number: current_db_version = int(cur.execute(sql_cmd).fetchone()[0]) except sqlite3.OperationalError: # This means that the database and the table, "CurrentDBVersion", has # not yet been created, implying "version 0". current_db_version = 0 finally: sql_scripts = ["../database/create_sqlite3_db.sql"] for sql_scriptname in sql_scripts[current_db_version:]: with open(sql_scriptname) as f: cur.executescript(f.read()) return db if __name__ == "__main__": db_filename = "../database/blood_pressure.db" ensure_db(db_filename)# Not doing anything with returned db yet. I have chickened out and not done a TDD approach yet. I will probably pause here, wait for feedback from this list, and try to figure out how I should test what I have so far. And how do you test SQL scripts anyway? Some things I am still pondering: 1) If I adopt the incremental approach to creating and initializing the working db, then it seems that the list, "sql_scripts", should not be hard-coded into the program. It seems to me it should be o
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
boB Stepp wrote:
> On Sat, Oct 14, 2017 at 2:11 AM, boB Stepp wrote:
>
>> So I get "None" as a result if the target table has not been created
>> yet. But if I *do* create the table I want:
>>
>> py3: with open('create_sqlite3_db.sql') as f:
>> ... sql = f.read()
>> ...
>> py3: c.executescript(sql)
>>
>> py3: tb_exists = "select name from sqlite_master where type='table'
>> and name='BloodPressureReadings'"
>> py3: tb_ck = c.execute(tb_exists).fetchone()
>> py3: print(tb_ck)
>> ('BloodPressureReadings',)
>>
>> So it is looking like I can use this technique to determine if I need
>> to create the BloodPressureReadings table or not. Am I on track here
>> or is there a better technique?
>
> It just occurred to me after sending the above, does something like
> "sqlite_master" exist for other database programs than sqlite3? Can I
> abstract out "sqlite_master" and replace it with a variable so that I
> can handle any kind of database?
If this is a long term project there will be changes in the schema.
However, I don't think it is necessary to check for individual tables. You
typically start with a few tables
create table alpha
create table beta
create table gamma
and later add a few more or change columns
alter table alpha
create table delta
In this example you have three versions of the database
version 0: empty
version 1: three tables
version 2: four tables, one table modified
If you add a table for your own metadata you ownly need to store that
version number. The necessary steps when you open the database are then
- read version from bobs_metadata (if that fails you are at version 0)
If you are at version 0 "migrate" to version one:
- execute script that creates alpha, beta, gamma, and bobs_metadata, the
latter with one row containing version=1
If you are at version 1 migrate to version two:
- execute migration script from 1 to 2 modifying alpha, creating delta, and
updating to version=2
If you are at version 2:
- do nothing, database is already in the state required by your application.
Pseudo-code:
scripts = ["0to1.sql", "1to2.sql"]
current_version = get_schema_version() # this has to catch the
# OperationalError
for scriptname in scripts[current_version:]:
with open(scriptname) as f:
script = f.read()
executescript(script)
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
Yep - oracle for example has hundreds of tables which hold metadata.
for example (this will look terrible due to the length of it but if you assume
it's 3 lines; a title line, some underlining for each column and the data
itself):
SQL> select * from all_tables where owner = 'FLYNNS' and table_name = 'QC11483';
OWNER TABLE_NAME TABLESPACE_NAME
CLUSTER_NAME IOT_NAME
STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT
MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING
BACKED_UP NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE
TABLE_LOCK SAMPLE_SIZE LAST_ANALYZED PARTITIONED IOT_TYPE TEMPORARY
SECONDARY NESTED BUFFER_POOL ROW_MOVEMENT GLOBAL_STATS USER_STATS DURATION
SKIP_CORRUPT MONITORING CLUSTER_OWNER DEPENDENCIES
COMPRESSION DROPPED
-- --
-- --
-- -- -- --
-- -- --- --- ---
-- --- --- - -- --
-- -- --- - ---
-- -- - -- --- - ---
- - -- ---
-- --- --
-- --- ---
FLYNNS QC11483USERS
VALID10 1255 65536
1 2147483645 NO N
61517 10000 0 0 108
0 0 1 1 N ENABLED
61517 30/08/2017 16 NO N N NO
DEFAULT DISABLED YES NO DISABLED
YES DISABLED DISABLEDNO
Executed in 0.141 seconds
There's something similar for SQL Server, DB2, ADDABAS, PostGres,, and so forth
S.
From: Tutor on behalf of
boB Stepp
Sent: 14 October 2017 08:17
To: tutor
Subject: Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
On Sat, Oct 14, 2017 at 2:11 AM, boB Stepp wrote:
> So I get "None" as a result if the target table has not been created
> yet. But if I *do* create the table I want:
>
> py3: with open('create_sqlite3_db.sql') as f:
> ... sql = f.read()
> ...
> py3: c.executescript(sql)
>
> py3: tb_exists = "select name from sqlite_master where type='table'
> and name='BloodPressureReadings'"
> py3: tb_ck = c.execute(tb_exists).fetchone()
> py3: print(tb_ck)
> ('BloodPressureReadings',)
>
> So it is looking like I can use this technique to determine if I need
> to create the BloodPressureReadings table or not. Am I on track here
> or is there a better technique?
It just occurred to me after sending the above, does something like
"sqlite_master" exist for other database programs than sqlite3? Can I
abstract out "sqlite_master" and replace it with a variable so that I
can handle any kind of database?
--
boB
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
This email is security checked and subject to the disclaimer on web-page:
http://www.capita.co.uk/email-disclaimer.aspx
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
On 14/10/17 06:43, boB Stepp wrote: > table if that table does not exist. Alan suggested using > executescript() to do this. I misunderstood Alan and thought that > this would take a filename and execute it. c.executescript(sqlFile.read()) -- 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
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
On Sat, Oct 14, 2017 at 12:41 PM, boB Stepp wrote:
> py3: c.executescript(sql)
>
> py3: tb_exists = "select name from sqlite_master where type='table'
> and name='BloodPressureReadings'"
> py3: tb_ck = c.execute(tb_exists).fetchone()
> py3: print(tb_ck)
> ('BloodPressureReadings',)
>
> So it is looking like I can use this technique to determine if I need
> to create the BloodPressureReadings table or not. Am I on track here
> or is there a better technique?
>
awesome trick. but most of the time applications had to exported out
of sqlite to either mysql or postgresql at which time it would be a
lot of refactoring as this trick uses sqlite internals to check if a
table exists.
fetchone would always return a tuple as we did count in the sql
instruction. so it would be easier to embed in an if statement.
Regards
Srinivas Devaki
Software Developer at Zomato, New Delhi
Phone: +91 9491 383 249
Telegram: @eightnoteight
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
SQL has "CREATE TABLE IF NOT EXISTS"
so you change your instructions to
```python
import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute("""
CREATE TABLE IF NOT EXISTS BloodPressureReadings (
ReadingID INTEGER PRIMARY KEY,
Date TEXT,
Time TEXT,
SystolicBP INTEGER,
DiastolicBP INTEGER,
Comments TEXT);
""")
c.execute("SELECT * FROM BloodPressureReadings")
```
On Sat, Oct 14, 2017 at 11:13 AM boB Stepp wrote:
>
> I want to use Alan's (and others') idea to run a SQL file to create a
> table if that table does not exist. Alan suggested using
> executescript() to do this. I misunderstood Alan and thought that
> this would take a filename and execute it. Instead, it appears that I
> must pass to it a string which is a SQL script. So after lots of
> fooling around in the interpreter I arrived at:
>
> py3: import sqlite3
> py3: conn = sqlite3.connect(':memory:')
> py3: c = conn.cursor()
> py3: try:
> ... c.execute('select * from BloodPressureReadings')
> ... except sqlite3.OperationalError:
> ... with open('create_sqlite3_db.sql') as f:
> ... sql = f.read()
> ... c.executescript(sql)
> ...
>
>
> The file 'create_sqlite3_db.sql' contains:
>
> CREATE TABLE BloodPressureReadings (
> ReadingID INTEGER PRIMARY KEY,
> Date TEXT,
> Time TEXT,
> SystolicBP INTEGER,
> DiastolicBP INTEGER,
> Comments TEXT);
>
> So at this point I am only creating an empty table.
>
> The above "works", but my "try" check is awful! What can I replace it
> with to just see if there is *any* table in the chosen database? In
> the code Peter supplied in the thread, "How is database creation
> normally handled?", he used in his function, "ensure_db(filename)":
>
> cursor.execute("create table if not exists addresses (name, email);")
>
> which is sweet, but I don't see how I can apply this idea if I insist
> on using a SQL file to create my table(s).
>
> BTW, in the docs at https://docs.python.org/3/library/sqlite3.html I
> found no mention of the actual exception I caught, "OperationalError".
> Should not this be in the docs?
>
> --
> boB
> ___
> Tutor maillist - Tutor@python.org
> To unsubscribe or change subscription options:
> https://mail.python.org/mailman/listinfo/tutor
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
On Sat, Oct 14, 2017 at 2:11 AM, boB Stepp wrote:
> So I get "None" as a result if the target table has not been created
> yet. But if I *do* create the table I want:
>
> py3: with open('create_sqlite3_db.sql') as f:
> ... sql = f.read()
> ...
> py3: c.executescript(sql)
>
> py3: tb_exists = "select name from sqlite_master where type='table'
> and name='BloodPressureReadings'"
> py3: tb_ck = c.execute(tb_exists).fetchone()
> py3: print(tb_ck)
> ('BloodPressureReadings',)
>
> So it is looking like I can use this technique to determine if I need
> to create the BloodPressureReadings table or not. Am I on track here
> or is there a better technique?
It just occurred to me after sending the above, does something like
"sqlite_master" exist for other database programs than sqlite3? Can I
abstract out "sqlite_master" and replace it with a variable so that I
can handle any kind of database?
--
boB
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
On Sat, Oct 14, 2017 at 1:43 AM, boB Stepp wrote:
> I am currently trying to make the following work:
>
> py3: tb_exists = c.execute('select name from sqlite_master where
> type="table"')
>
> so that I can embed this in an if statement. But it is not behaving
> quite like I hope yet. So I am still experimenting and Googling ...
I'm tired and not thinking clearly anymore, but I think I have
something to give me the basis of what I want to do. First, what
happens if the table does *not* exist:
py3: import sqlite3
py3: conn = sqlite3.connect(':memory:')
py3: c = conn.cursor()
py3: tb_exists = "select name from sqlite_master where type='table'
and name='test'"
py3: tb_ck = c.execute(tb_exists).fetchone()
py3: tb_ck
py3: print(tb_ck)
None
So I get "None" as a result if the target table has not been created
yet. But if I *do* create the table I want:
py3: with open('create_sqlite3_db.sql') as f:
... sql = f.read()
...
py3: c.executescript(sql)
py3: tb_exists = "select name from sqlite_master where type='table'
and name='BloodPressureReadings'"
py3: tb_ck = c.execute(tb_exists).fetchone()
py3: print(tb_ck)
('BloodPressureReadings',)
So it is looking like I can use this technique to determine if I need
to create the BloodPressureReadings table or not. Am I on track here
or is there a better technique?
--
boB
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to test for the existence of a table in a sqlite3 db?
On Sat, Oct 14, 2017 at 1:27 AM, srinivas devaki
wrote:
> SQL has "CREATE TABLE IF NOT EXISTS"
>
> so you change your instructions to
> ```python
> import sqlite3
> conn = sqlite3.connect(':memory:')
> c = conn.cursor()
> c.execute("""
> CREATE TABLE IF NOT EXISTS BloodPressureReadings (
> ReadingID INTEGER PRIMARY KEY,
> Date TEXT,
> Time TEXT,
> SystolicBP INTEGER,
> DiastolicBP INTEGER,
> Comments TEXT);
> """)
> c.execute("SELECT * FROM BloodPressureReadings")
But I do *not* want to create this table from within my python code;
instead, I want to run the table creation commands from a file if the
table does not already exist. This way I have more flexibility if I
later change to different database products, for testing purposes,
etc.
I am currently trying to make the following work:
py3: tb_exists = c.execute('select name from sqlite_master where type="table"')
so that I can embed this in an if statement. But it is not behaving
quite like I hope yet. So I am still experimenting and Googling ...
boB
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
[Tutor] How to test for the existence of a table in a sqlite3 db?
I want to use Alan's (and others') idea to run a SQL file to create a
table if that table does not exist. Alan suggested using
executescript() to do this. I misunderstood Alan and thought that
this would take a filename and execute it. Instead, it appears that I
must pass to it a string which is a SQL script. So after lots of
fooling around in the interpreter I arrived at:
py3: import sqlite3
py3: conn = sqlite3.connect(':memory:')
py3: c = conn.cursor()
py3: try:
... c.execute('select * from BloodPressureReadings')
... except sqlite3.OperationalError:
... with open('create_sqlite3_db.sql') as f:
... sql = f.read()
... c.executescript(sql)
...
The file 'create_sqlite3_db.sql' contains:
CREATE TABLE BloodPressureReadings (
ReadingID INTEGER PRIMARY KEY,
Date TEXT,
Time TEXT,
SystolicBP INTEGER,
DiastolicBP INTEGER,
Comments TEXT);
So at this point I am only creating an empty table.
The above "works", but my "try" check is awful! What can I replace it
with to just see if there is *any* table in the chosen database? In
the code Peter supplied in the thread, "How is database creation
normally handled?", he used in his function, "ensure_db(filename)":
cursor.execute("create table if not exists addresses (name, email);")
which is sweet, but I don't see how I can apply this idea if I insist
on using a SQL file to create my table(s).
BTW, in the docs at https://docs.python.org/3/library/sqlite3.html I
found no mention of the actual exception I caught, "OperationalError".
Should not this be in the docs?
--
boB
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
