Re: [Tutor] How is database creation normally handled?

[Alan Gauld via Tutor]

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

Re: [Tutor] Standard module sqlite3 or APSW?

[Alan Gauld via Tutor]

On 10/09/17 01:56, boB Stepp wrote:

> So if all of this is correct, does the documentation at
> https://rogerbinns.github.io/apsw/pysqlite.html#pysqlitediffs give an
> objective assessment of the differences between pysqlite and APSW?

I believe so.
I've never used APSW, only read a single web article on it
so I'm no expert. But for me it would only be of interest
if I was totally sure I'd never want to move my data to
anything else. And that's pretty rare since for small
data I might change to, say, a JSON file. And for
large volumes (or shared access) I'd move to a server
database like MySQL. In either case the investment in
learning a non-standard data layer is not worth it.

And of course it adds another external dependency to my
project, and I hate external dependencies!

> (1) If my database needs are simple, go with the standard library
> module, sqlite3.

Usually this is true if I'm using SQLite. If my needs
were not simple I'd probably be using MySQL or FireBird
or similar.

> (2) If I wish to use all of the latest, greatest features of SQLite3,
> and I don't care about some of its non-standard SQL implementations,
> then use APSW if SQLite3 meets my database needs and I don't
> anticipate having to upgrade to one of the big database projects.

This is very rare for me and pretty much why I don;t use APSW

> (3) If I am prototyping a database solution, anticipating
> transitioning to one of the big database projects, then use the
> standard library sqlite3 as it will make it easier to make the
> transition, due to better SQL standardization.

Again, this is usually my scenario.

> A further question.  Is APSW being maintained solely by a single
> person?  That is, is it likely to be around for years to come?

I'm not sure on that one.

-- 
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 is database creation normally handled?

[Peter Otten]

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

[Tutor] beginning to code

[Elmar Klein]

Hi there,

im starting to learn how to code (bougt me the "automate the boring 
stuff with phyton" book).


And im not going anywhere with a code sample using the "continue" statement.

The code i should try is as following:

while True:
  print ('who are you')
  name = input ()
  if name != 'bert':
  continue
    print ('hi, joe. pwd?')
    pwd = input ()
    if pwd == 'sword':
    break
    print ('access granted')

But everytime this produces an error (unindent does not match any outer 
indention level) in the "print ('hi, joe. Pwd?')" sentence.


What am i doing wrong?

Greetings

kerbi



___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

Re: [Tutor] beginning to code

[Senthil Kumaran]

> unindent does not match any outer indention level

Means that your Intendentaion is not proper. You should align your print
statement and rest to the same level as if statement.

Use a proper editor that supports Python Syntax.

On Sun, Sep 10, 2017 at 4:32 AM, Elmar Klein  wrote:

> Hi there,
>
> im starting to learn how to code (bougt me the "automate the boring stuff
> with phyton" book).
>
> And im not going anywhere with a code sample using the "continue"
> statement.
>
> The code i should try is as following:
>
> while True:
>   print ('who are you')
>   name = input ()
>   if name != 'bert':
>   continue
> print ('hi, joe. pwd?')
> pwd = input ()
> if pwd == 'sword':
> break
> print ('access granted')
>
> But everytime this produces an error (unindent does not match any outer
> indention level) in the "print ('hi, joe. Pwd?')" sentence.
>
> What am i doing wrong?
>
> Greetings
>
> kerbi
>
>
>
> ___
> 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] beginning to code

[Bob Gailer]

On Sep 10, 2017 8:40 AM, "Senthil Kumaran"  wrote:
>
> > unindent does not match any outer indention level
>
> Means that your Intendentaion is not proper. You should align your print
> statement and rest to the same level as if statement.
>
> Use a proper editor that supports Python Syntax.
>
Also the last line will print access granted if the password does not
match. Probably not what you wanted. Either indent it and put it before
break or unindent it so it lines up with while.

For what it's worth if the user never gets the password he is stuck forever
in the loop.

In the future please include the entire traceback in your email. That makes
it a lot easier for us to relate it to your code.

> On Sun, Sep 10, 2017 at 4:32 AM, Elmar Klein  wrote:
>
> > Hi there,
> >
> > im starting to learn how to code (bougt me the "automate the boring
stuff
> > with phyton" book).
> >
> > And im not going anywhere with a code sample using the "continue"
> > statement.
> >
> > The code i should try is as following:
> >
> > while True:
> >   print ('who are you')
> >   name = input ()
> >   if name != 'bert':
> >   continue
> > print ('hi, joe. pwd?')
> > pwd = input ()
> > if pwd == 'sword':
> > break
> > print ('access granted')
> >
> > But everytime this produces an error (unindent does not match any outer
> > indention level) in the "print ('hi, joe. Pwd?')" sentence.
> >
> > What am i doing wrong?
> >
> > Greetings
> >
> > kerbi
> >
> >
> >
> > ___
> > 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
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

Re: [Tutor] beginning to code

[boB Stepp]

Hello kerbi!

On Sun, Sep 10, 2017 at 6:32 AM, Elmar Klein  wrote:
> Hi there,
>
> im starting to learn how to code (bougt me the "automate the boring stuff
> with phyton" book).
>
> And im not going anywhere with a code sample using the "continue" statement.
>
> The code i should try is as following:
>
> while True:
>   print ('who are you')
>   name = input ()
>   if name != 'bert':
>   continue
> print ('hi, joe. pwd?')
> pwd = input ()
> if pwd == 'sword':
> break
> print ('access granted')
>
> But everytime this produces an error (unindent does not match any outer
> indention level) in the "print ('hi, joe. Pwd?')" sentence.

As the others have said your indentation is inconsistent.  "print
('who are you')" is indented 6 spaces under "while True:".  But
"continue" is indented 8 spaces under "if name != 'bert':".  And
"print ('hi, joe. pwd?')" is indented 2 spaces under "if name !=
'bert':" when it should not be indented at all!  You might want to
review pages 37-38 where the author talks about "Blocks of Code" and
make sure you are *getting it*.  If not, come back with specific
questions.

Senthil mentioned using a proper programmer's editor that supports
Python syntax.  This can make things much easier.  Generally speaking,
you wish a single level of indentation to always be the same number of
spaces.  The commonly used number is one indentation level equals 4
spaces.  Most editors will allow you to use the  key to indent,
converting it to 4 spaces (Or a different number if you configure your
 differently in the editor.).  A side note:  Don't mix tabs with
spaces!  Ensure that your editor converts tabs to spaces.  Otherwise
you can have a real mess!

So look carefully on page 51 of your book where I found the code you
are playing with.  It looks like you are playing around with modifying
it, which is a good thing to do.  But you still must maintain the
proper block structure of the author's code that you are modifying if
you want to accomplish the kinds of things the author is doing.  Pay
particular to the vertical alignment of each line's contents.  For
instance, the "w" in "while True:" lines up with the "p" in
"print('Access granted.')", which is the last line of the text's code
example.  Everything else between the first and last lines is indented
one or more levels and belongs to the while loop's execution
responsibilities.  When you look at the while loop's block contents
you notice additional levels of indentation belonging to the two if
statements.  Again notice how these additional levels of indentation
define the blocks belonging to each if statement's responsibilities.

Another thing is that if I have found the correct page of the code you
are trying out, you are being rather free with how you match
capitalization with the author's code.  This won't matter a whole lot
for strings you are printing to screen, but if you start being sloppy
with capitalization with Python keywords or identifiers/variable
names, then you will soon come to a lot of grief!  Also, you add extra
spaces to the print() functions.  Fortunately this does not matter
here, but it shows that you are being a bit cavalier with how you
re-type code.  This lack of attention to seemingly teeny-tiny details
can cause you much grief, such as in your indentation problems.
Whereas a human being can usually easily interpret your intentions,
computers, being extremely literal in their interpretation of what is
typed, might easily not get your intentions.  Be careful what you
type!

It might be educational for you to take your current code with its
inconsistent indentation and correct it line by line, but re-running
the code after each such correction to see what exactly happens and
what new error tracebacks you get.

Hope this helps!


-- 
boB
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

Re: [Tutor] How is database creation normally handled?

[Mats Wichmann]

On 09/10/2017 02:08 AM, Alan Gauld via Tutor wrote:
> 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!).

All of this depends on how you intend to use the database.  If it's a
small thing, say to enable experiments, you can certainly build it on
the fly (esp. if you'll never go beyond the sqlite level). I'm generally
agreeing that the "build it outside your program" and "keep the DB code
in SQL" are good ideas.

There are so many different types of databases for different uses. I've
spent a decade and a half on a project (sadly now it's just a trickle of
time to maintain anything that breaks) where we do have a central
"official" database, but want people to be able to do local experiments,
because the "official" database helps describe a standard, making it
update-rarely.  So on any approved change, the database is immediately
dumped, and the version-control copy of that dump has the changes
committed.  With some supporting code (written in Perl, not Python -
hey, I did't start those scripts :)), anybody who wants to experiment
with proposed updates, either to fix a reported bug, or to prototype
changes for the next version, can just pull the repository and go "make
restore" and a local copy of the (mysql) db will be built. So that's
what I just agreed with: the setup process happens outside the code app
which will access the DB; the DB creation _and_ the data population are
both kept entirely in SQL (as a result of using the dumps).  That's
proven to be very workable for that somewhat unusual usage model, but
maybe it's not that unusual - any time you need to start with a
reproducible known state of the DB, that would work pretty well; it
would be a disaster for a DB where there were tons of commits, stuff
changed mostly interactively, etc.

___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

Re: [Tutor] How is database creation normally handled?

[Chris Warrick]

On 10 September 2017 at 02: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.  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.  But this sounds like a lot
> of extra coding and checking to make it work well.  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.

A separate database creation script will be better. It’s good to
separate the logic of your app and configuration/setup — just like you
shouldn’t be “helpfully” installing dependencies when someone runs
your script.

For a good approach, look at Django: every app (sub-package of a site)
has its own set of migrations. Migrations are responsible for creating
tables, and more importantly — for updating them. Because you will
need to make changes to your original DB structure throughout the
lifetime of your software. I built a small Django app over the
weekend, and I’ve created 10 migrations throughout the process¹, and
that will probably be true of your project. Now, if those migrations
would have to go to the main codebase instead of a side directory, it
would be a burden to maintain. If there were no migration frameworks,
I’d have to include something like “run this SQL to upgrade your DB
when upgrading from version 0.1.0 to 0.1.1”, which is even less fun.

So, when I make some change to my models.py (which has the database
schema), I can just run:
./manage.py makemigrations
./manage.py migrate
and my database will be magically updated, hassle-free and I don’t
even have to look at the auto-generated code.

(If you are writing a web app: do look at Django! If you aren’t:
Alembic does the same thing for SQLAlchemy. Something for plain
sqlite3 may or may not exist.)

¹ Progressive enhancement: adding more features that need extra
columns I didn’t think of first. Or removing features that weren’t
cool. Or restoring them the next day.

-- 
Chris Warrick 
PGP: 5EAAEA16
___
Tutor maillist  -  Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

Re: [Tutor] beginning to code

[Alan Gauld via Tutor]

For some reaspon I'm missing the original post so apologies
to replying to Senthil instead of the OP directly...


>> The code i should try is as following:
>>
>> while True:
>>   print ('who are you')
>>   name = input ()
>>   if name != 'bert':
>>   continue
>> print ('hi, joe. pwd?')
>> pwd = input ()
>>
>> But everytime this produces an error (unindent does not match any outer
>> indention level) in the "print ('hi, joe. Pwd?')" sentence.

Its best when posting to the group to include the full error
text rather than summarizing it. In this case we don't need
it but in other cases the extra detail may be important.

The error is telling you that the indentation of that line
is wrong. Indentation, or spacing, is very important in
Python - much more so than in other languages. Python uses
the spacing to tell which lines are grouped together.

In your code Python can't tell whether the print line is
supposed to be inside the if block along with the continue
statement or outside the if block and following the if statement.

You need to align the print (and all subsequent lines)
with the if.

One question though. As written, your code only goes
past the if statement if the input name is *bert*.
Why then does the welcome say Hi *joe*

Just a thought...


-- 
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