Apr 24
term projects
Due one week from today - your presentation of your term graph project.
Discussion :
- What exactly are you working on?
- Any questions or issues?
- How are you going to do the timing?
- How are you going to generate graphs to test?
more SQL
So we're talking this week about
SQL - no written assignment
on this stuff, just a first pass on some good stuff while you're
working on your projects.
In class we'll work though some examples of that interface, including
placeholder syntax
# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)
# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())
The ? is typically called a "placeholder", and using it
this way lets the software do the quoting correctly.
For example, numbers don't get quotes but strings do.
And which type of quote works is likely different in
SQL than in the programming language.
Here "insecure" means that there may be problems with
cross-site scripting if you do it wrong ...
see
https://xkcd.com/327/ .
transactions
Discuss what they are and why, and the "commit" notion
is part of SQL databases.
Note that there are various options you can set to
autocommit or handle transactions manually.
The details of transactions varies with the
particular database (i.e. SQLite vs
dictionaries
There is some support in python's sqlite3 (as in
many higher order languages) for getting back
results as a dictionary rather than just a list.
They call this feature a Row object, which
is set by adding a "row_factory" property
to a connection.
workflow
Working with sql from a programming language is a bit
different from using it directly. In particular,
you may need to "prepare" a sql statement,
then "execute" it, then "fetchrow" or otherwise
get the data from it. And that's after you've
connected to the database.
All this varies with both the programming language
library and the sql database - you'll need to
look at examples for any specific library.
We'll walk through examples.
an exercise
To give us something specific to work on,
consider the task of setting up a database
for a bookstore.
We'll talk about this ... and then for example look at
this example.
Note that this is for mysql , not sqlite ... so we may need to make some changes.
And we may want to modify the fields (and possibly the primary keys).
more practice
