[storm] I need a tutorial that deals with working on an established database.

[James Henstridge]

On Tue, Jul 7, 2009 at 12:03 PM, Mark Richardson<MRichardson3 at gmail.com> wrote:
> Greetings all,
>
> I am a noob. Please go easy on me. I've been searching for a tutorial
> that shows how you can work with an existing database.
> I.e, the database's schema has already been defined (it's a Joomla
> database). I need to be able to select the
> table I want, iterate over the rows in the database, change some records
> and finally write the changes to those records back out.

Storm is designed so that it can work with most existing database
schemas: the main restriction is that each table have a primary key
(simple or compound).  I doubt you'd have much trouble here.


> All of the tutorials I've been able to find on the Internet seem to show
> the user creating the tables from scratch.  Having to write
> python classes showing how the fields are defined, for example.  In my
> case, all of that work is done. I want
> to open the database, have it give me a list of tables and subsequently
> the columns in those tables
> and then iterate over all of the rows in the table changing columns as
> per needed.  Below would be some
> hypothetical pseudo code showing what I want to do. I'd like to see some
> real world examples that accomplishes
> the same task, but written using Storm.

You will need to write Storm classes to represent the tables you want
to access.  They don't necessarily need to fully describe the tables:
providing a subset of columns works for most cases.

There is no support for building these classes via introspection.


> <begin hypothetical code>
>
> database = open('/mysql/joomla.db','rw')      # this is way simplified
> as I know MYSQL needs a bunch of parameters, but basically I want to
> open it in read, write mode.
> tablelist = database.tables()                     #get me the list of
> all tables associated with the database
> for table in tablelist:                                # iterate through
> all of the tables in the database
>  columnlist = table.columns()                 # for each table, return a
> list of all of the column names,
>  rowlist = table.fetchall()                        # put all rows into a
> database list, alternatively I might use the fetchone() option if I want
> to do it one by one
>
>  for row in rowlist:                      # now iterate through every
> row in the table in the database
>   row.column(columnlist[0]) = row.column(columnlist[0]) * 0.07     #
> for each row in the table, multiply the first column by 7 percent
>   row.column('AnnualPay') = row.column('AnnualPay') * 0.07
> #alternatively, we could directly access the field name if we knew its
> name a-priori
>
> row.save()
> #write the row changes back out to the data base table
>
> table.close()
> #close the table and save the changes
> database.close()
> #finish with the database.
>
> <end hypothetical code>

For simple changes like above, you could use a single UPDATE statement
to update the table:

    UPDATE table SET col1 = col1 * 0.07, AnualPay = AnyalPay * 0.07;

If you've written a class to represent the table, that can be
represented as follows with Storm:

    store.find(Table).set(col1=Table.col1 * 0.07,
AnualPay=Table.AnualPay * 0.07)

If you are doing something more complicated, you can iterate the loop in python:

    for item in store.find(Table):
        item.col1 = whatever


> I know this pseudo code is extremely terse and probably wrong in some
> places, but it gives the flavor of what I want to do.  I
> just need some simple code showing me how to process every row of a
> table in a database.  If this question has already been
> answered, could you please send me the link to the final discussion.  I
> may be showing a fundamental lack of understanding
> about using a database, but it seems I should be able to manipulate my
> database this way if I choose to.

Storm should be able to help with your problem, but you will need to
write the relevant Storm classes.


James.