[Tutor] table to dictionary and then analysis

Russel Winder russel at winder.org.uk
Thu May 17 09:27:07 CEST 2012

On Wed, 2012-05-16 at 12:57 -0400, Joel Goldstick wrote:
> I think the OP is just learning and this thread may have gotten of track.

I didn't realize discussion of immediate side issues and alternatives,
and allowing people to exchange information was OT in this mailing list.
Also of course, OP didn't mention databases, but was asking how to do it
with lists and dictionaries. I think there is irony somewhere in here.

> Here is some code to get started.  I decided to use sqlite3 since its
> easy to use with python -- no finding and learning to load packages.
> #!/usr/bin/env python
> import sqlite3 as db
> # Ideally this shouldn't be global, but in this short code snippet it
> gets the job done
> # here we create a database and get a cursor
> conn = db.connect('climate.db')
> cursor = conn.cursor()
> print cursor

I believe that there are more problems than just global data here. One
obvious thing is this code is not safe against exceptions. I appreciate
this is a trivially small program, but I think it important that sample
code presents good style or explicitly states what is wrong so as to
present what not to do. Your comment about global sits well with this,
but for me doesn't go far enough. Python introduced context managers and
the with statement exactly for this sort of situation, following the
lead of C++ with RAII. I think we should all get into the habit of using
the with statement automatically in this situation.

> # this will create a table for our data
> sql_create = """CREATE TABLE if not exists rain (
>             id INTEGER PRIMARY KEY,
>             year INTEGER,
>             month TEXT(3),
>             rainfall FLOAT,
>             fire_area FLOAT
>             )"""
> # this will read the data file and put it in our database
> def populate_climate_table(file_name):
>     """
>     reads the file_name and insert data into sqlite table
>     """
>     sql_insert_string = "insert into rain (year, month, rainfall,
> fire_area) values (%d, '%s', %f, %f)"
>     f = open(file_name)

Same comment about context managers and with statement applies here:
this code is not exception safe.

>     f.readline() # get rid of column headers
>     for l in f.readlines():
>         data_list = l.split()
>         print data_list
>         sql_insert = sql_insert_string % (int(data_list[0]),
> data_list[1], float(data_list[2]), float(data_list[3]))

Should we be promoting use of the format method in strings rather than
the % operator? % is deprecated now.

Although not an issue here, this sort of SQL string manipulation is at
the heart of SQL injection attacks and so should be frowned upon. Hence
SQLAlchemy's expression languages, which goes some way to avoiding the
whole issue.  At the expense of having to load an additional package.
With package management on Debian/Fedora/Ubuntu/MacPorts or the pip
command this is not difficult to add. 

>         print sql_insert
>         cursor.execute(sql_insert)
>     conn.commit()
> if __name__ == '__main__':
>     print sql_create
>     cursor.execute(sql_create)
>     populate_climate_table('data.txt')
> So, I haven't solved all of the questions with this code.  The next
> thing to do is to read a little about sqlite select statements.
> for example: sqlite> select sum(rainfall)/count(*) from rain;
> 3.97352768125
> This statement will give the average rainfall over the complete dataset.
> To get the ave rainfall for a given year do this:
> sqlite> select sum(rainfall)/count(*) from rain where year = 1983;
> Come back with more questions

Dr Russel Winder      t: +44 20 7585 2200   voip: sip:russel.winder at ekiga.net
41 Buckmaster Road    m: +44 7770 465 077   xmpp: russel at winder.org.uk
London SW11 1EN, UK   w: www.russel.org.uk  skype: russel_winder
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 198 bytes
Desc: This is a digitally signed message part
URL: <http://mail.python.org/pipermail/tutor/attachments/20120517/24c34410/attachment-0001.pgp>

More information about the Tutor mailing list