[Tutor] table to dictionary and then analysis
joel.goldstick at gmail.com
Wed May 16 18:57:36 CEST 2012
On Wed, May 16, 2012 at 11:03 AM, Alan Gauld <alan.gauld at btinternet.com> wrote:
> On 16/05/12 12:27, Russel Winder wrote:
>>> As a matter of interest why?
>> Because there are alternatives that need to be investigated on a per
>> problem basis for the best database.
> I agree, but in this case SQL seemed like the most likely fit of the ones I
> knew. however:
> I know about these
> These are new to me.
>> etc. Python only has SQLite3 as standard but there are alternatives. I
>> have been using PyMongo quite successfully.
> Python comes with several storage/access options including shelve, gdbm,
> ldap, cobfig files, XML, in addition to SQL.
>> It is not clear that the original table works better with the relational
>> model compared to one of the key-value stores or document stores.
> Most key-value stores are optimised for fast queries of a single type
> and generally not great at grouping or ordering. They also tend to major on
> flexiblity of data format. The OPs requirements suggested intelligent
> filtering of a fixed record format which is one of the areas where SQL works
> well. The other side of the coin is that the data is essentially single
> table so the relationship management aspects of SQL would not be needed. So
> I agree we don't have enough detail
> to be 100% sure that another option would not work as well or better.
> But most other options require learning new (often bespoke) query languages
> and have limited user tools. All of these factors need to be included too.
> Mongo et al tend to be better suited, in my experience, to machine access
> applications rather than end user access.
>> There are various articles around the Web comparing and contrasting
>> these various models. Some of the articles are even reasonable :-)
> Wikipedia is my friend :-)
> Alan G
> Author of the Learn to Program web site
> Tutor maillist - Tutor at python.org
> To unsubscribe or change subscription options:
I think the OP is just learning and this thread may have gotten of track.
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.
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()
# this will create a table for our data
sql_create = """CREATE TABLE if not exists rain (
id INTEGER PRIMARY KEY,
# this will read the data file and put it in our database
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)
f.readline() # get rid of column headers
for l in f.readlines():
data_list = l.split()
sql_insert = sql_insert_string % (int(data_list),
data_list, float(data_list), float(data_list))
if __name__ == '__main__':
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;
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
More information about the Tutor