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?
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] New Item
On 28/09/2017 11:46, Peter Otten wrote:
larrystale...@comcast.net wrote:
I am very new to Python and appreciate the input as I was able to fully
install Python with all needed libraries (i.e., numpy, pandas, etc.).
However, I now have an application question in needed to construct a 2D
Histogram.
Basically, I have an Excel file that includes three columns:
Column 1 - Gender (Male or Female)
Column 2 - Height (in inches)
Column 3 - Hand Span (in inches)
I have yet to grok your code samples, but my feeling is that your approach
is too low-level. Do you mean something like
http://matplotlib.org/examples/pylab_examples/hist2d_demo.html
by "2d histograms"? That would require very little code written by yourself:
import pandas as pd
from matplotlib import pyplot
filename = "size.xls"
sheetname = "first"
data = pd.read_excel(filename, sheetname)
for index, sex in enumerate(["female", "male"], 1):
pyplot.figure(index)
subset = data[data["Gender"] == sex]
pyplot.hist2d(subset["Height"].values, subset["Hand Span"].values)
pyplot.show()
data=readExcel(excelfile)
X=np.array(data[:,1],dtype=float);
S=np.array(data[:,2],dtype=float);
T=np.array(data[:,0],dtype=str);
Finally, is my intended coding for the actual 2D histogram. I will get min
and max from the height and hand span arrays. Note I am still learning
Python and looping is new to me:
# Define histogram classifier to build histogram using two variables
def Build1DHistogramClassifier(X, S, smin, smax,T,B,xmin,xmax):
HF=np.zeros(B).astype('int32');
HM=np.zeros(B).astype('int32');
binindices1=(np.round(((B-1)*(X-xmin)/(xmax-xmin.astype('int32');
binindices2=(np.round(((B-1)*(S-smin)/(smax-smin.astype('int32');
for i,b in enumerate(binindices1):
for i,c in enumerate(bindindices2):
if T[i]=='Female':
HF[b,c]+=1;
else:
HM[b,c]+=1;
return [HF, HM]
Hi,
I have a similar problem, but my data is not in excel but is in
OpenOffice "Spreadsheet', but not in "Database".
My question is can I use a similar simple procedure as that given by
Peter Otten.
Ta muchly.
--
Sydney
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] New Item
Sydney Shall wrote: > On 28/09/2017 11:46, Peter Otten wrote: >> larrystale...@comcast.net wrote: >> >>> I am very new to Python and appreciate the input as I was able to fully >>> install Python with all needed libraries (i.e., numpy, pandas, etc.). >>> However, I now have an application question in needed to construct a 2D >>> Histogram. >>> >>> Basically, I have an Excel file that includes three columns: >>> Column 1 - Gender (Male or Female) >>> Column 2 - Height (in inches) >>> Column 3 - Hand Span (in inches) >> >> I have yet to grok your code samples, but my feeling is that your >> approach is too low-level. Do you mean something like >> >> http://matplotlib.org/examples/pylab_examples/hist2d_demo.html >> >> by "2d histograms"? That would require very little code written by >> yourself: >> >> import pandas as pd >> from matplotlib import pyplot >> >> filename = "size.xls" >> sheetname = "first" >> >> data = pd.read_excel(filename, sheetname) >> >> for index, sex in enumerate(["female", "male"], 1): >> pyplot.figure(index) >> subset = data[data["Gender"] == sex] >> pyplot.hist2d(subset["Height"].values, subset["Hand Span"].values) >> >> pyplot.show() > I have a similar problem, but my data is not in excel but is in > OpenOffice "Spreadsheet', but not in "Database". > > My question is can I use a similar simple procedure as that given by > Peter Otten. There doesn't seem to be direct support for the ods file format in pandas. Your easiest option is to open the file in OpenOffice and save as xls or csv. If you don't want to go that route you can install a library that can read ods files. With https://pypi.python.org/pypi/pyexcel-ods/0.3.1 the above example should work after the following modifications: import pandas as pd from matplotlib import pyplot import pyexcel_ods def read_ods(filename, sheetname): table = pyexcel_ods.read_data(filename)[sheetname] return pd.DataFrame(table[1:], columns=table[0]) filename = "size.ods" sheetname = "first" data = read_ods(filename, sheetname) for index, sex in enumerate(["female", "male"], 1): pyplot.figure(index) subset = data[data["Gender"] == sex] pyplot.hist2d(subset["Height"].values, subset["Hand Span"].values) pyplot.show() ___ 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?
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?
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?
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
