[TIP] Using DB on tests without orm

[Mark Sienkiewicz]

marrese at gmail.com wrote:
> Creating a script that queries the tables is not so simple with the
> current structure. We are not using any framework, but I decided to
> make some tests using Django ORM. So far no good. The structure is so
> messed up that the dumpdata fails constantly.
>   

The problem with using django for this is that you are so far from the 
database.  I have seen django work quite well with database that django 
created, but I am less confident that it can deal with an arbitrary 
database.


I was thinking something like this:

db = sqlite3.connect('file')

db.row_factory = sqlite3.Row
    # this row factory makes the returned row indexable by column name
    # MySQLdb has this capability too, but I forget the details

c = db.cursor()

c.execute('SELECT * FROM tablename')

for row in c :
    print "NEXT ROW"
    for col in row :
       print "%s=%s"%(col,row[col])

 From there, sorting the column names, stuffing it into a CSV, and so on 
are just details.

To insert data, something like this:

names = [ 'col1', 'col2', 'col3' ]  # from first line of csv file
values = ( 1, 2, 3 )                # from next line

col_names = ', '.join(names)
    # looks like "col1, col2, col3"

col_values = ','.join( [ '?' for x in names ] )
    # looks like "?, ?, ?"

c.execute('INSERT INTO tablename VALUES ( ' + col_values + ' ) ', values)
    # string looks like
    #   INSERT INTO tablename VALUES ( ?, ?, ? )

For MySQLdb, you'll have to change the '?' to one of the things that 
MySQLdb takes there.  I forget exactly at the moment, but maybe '%s, %s, 
%s' or ':1, :2, :3' ?


If you have any restrictions (stored procedures, triggers, whatever) 
that enforce relations between tables, drop them, load all the tables, 
then restore them.


> I'll try to minimize the amount of data stored in the test db and work
> from there. I guess that with less data the storage will be easy to
> recreate.
>   

That's usually a good plan. :)


> Thanks for the answers ...
>   


You're welcome.