sqlobject-discuss — All things SQLObject-related

[SQLObject] SQL Server and SQLObject... unit testing

[Joseph K. <jko...@ma...>]

Anyone want to try to help me get some unit tests written for an mssql 
connection that uses mxODBC? I have the code written and it seems to 
work well, but I don't really understand how SQLObject's unit tests 
work, and I'm not going to take the time to figure them out anytime 
soon. Too many other things going on. Also, (since you're back Ian ;) is 
the addition of something like this someething you'd consider since 
mxODBC isn't free? If not, have you considered methods for adding in 
support for different databases as external modules? I couldn't figure 
out how to get things working without modifying the following:

sqlobject/converters.py
sqlobject/__init__.py
sqlobject/col.py
setup.py

Ideas and comments are welcome. I also have an mxodbcda adapter for 
zope3 in the works for use with sqlos. One step at a time though.

Joseph Kocherhans

[SQLObject] Re: unknown column table creation..novice

[Brian R. <br...@se...>]

> From: "Philippe Normand" <sw...@fr...>
> 
> 
>>> 
>>>    rows = zip(headers,cell)
>>>    line_item = MailList.new(*rows)
>>> 
>> 
>> Try:
>> 
>> MailList.new(**rows)
>> 
> 
> Woops, this won't work i think, you have to build a dictionnary to be
> able to use **. So:
> 
> rows = dict(zip(headers,cell))
> line_item = MailList.new(**rows)
> 
> 

Works like a charm. Thanks Ian and Philippe!

Complete code for csv2sql.py:



#!/usr/bin/env python
"""
   $Id: csv2sql.py,v 1.1 2004/08/19 16:02:37 brian Exp $

   Quick conversion script of any CSV to
   any SQLObject support database

   Brian Ray

""" 
from SQLObject import *
import csv

__connection__ =  SQLiteConnection('../USERDATA/1/mailist.sqlite')
data_file = "../USERDATA/QM_sample_job/sample.csv"

data_sample = file(data_file).read(8192)
dialect = csv.Sniffer().sniff(data_sample)
data = csv.reader(file(data_file),dialect)

headers = []
row_num = 0
for row in data:
   row_num += 1
   if row_num == 1:
      for cell in row:
         headers.append(cell)
      try:
         class MailList(SQLObject):
            pass
         for header in headers:
            MailList.addColumn(Col(name=header))
         MailList.createTable()
         print MailList._table+" Created."
      except:
         print MailList._table+" Skipped already exists or error."
   else:
      rows = dict(zip(headers,row))
      line_item = MailList.new(**rows)

print "Done: "+str(row_num-1)+" converted"

# Please let me know if this has issues

[SQLObject] Re: unknown column table creation..novice

[Philippe N. <sw...@fr...>]

>>
>>    rows =3D zip(headers,cell)
>>    line_item =3D MailList.new(*rows)
>>
>
>Try:
>
>MailList.new(**rows)
>

Woops, this won't work i think, you have to build a dictionnary to be
able to use **. So:

rows =3D dict(zip(headers,cell))
line_item =3D MailList.new(**rows)

[SQLObject] Re: unknown column table creation..novice

[Philippe N. <sw...@fr...>]

Le 19/8/2004, "Brian Ray" <br...@se...> a =E9crit:

>
>Next I want to create a new record:
>
>    rows =3D zip(headers,cell)
>    line_item =3D MailList.new(*rows)
>
>However the new method does not like this argument list:
>
>    TypeError: new() takes exactly 1 argument (16 given)
>
>When I explicitly write out all 16 arguments, it works.

Try:

MailList.new(**rows)

Philippe

[SQLObject] Re: unknown column table creation..novice

[Brian R. <br...@se...>]

 
>> I am looking for a way to created a table from a list of values. Say I have:
>> 
>>      headers = ['bbd_id', 'co_name1', 'address1', 'address2', 'city',
>> 'state', 'zip5', 'zip4', 'first_name', 'surname', 'telephone']
> 
> class MailList(SQLObject):
>    pass
> 
> for header in headers:
>    MailList.addColumn(Col(name=header))

This works great, thnx!

Next I want to create a new record:

    rows = zip(headers,cell)
    line_item = MailList.new(*rows)

However the new method does not like this argument list:

    TypeError: new() takes exactly 1 argument (16 given)

When I explicitly write out all 16 arguments, it works.

Re: [SQLObject] problem with contructor of an object

[Ian B. <ia...@co...>]

Markus Jais wrote:
> hello
> 
> this is my sample code:
> 
> ---
> from SQLObject import *
> 
> 
> conn = MySQLConnection(user='abc', db='abook', passwd='def')
> 
> class Person(SQLObject):
> 
> 	_connection = conn
> 
> 	firstName = StringCol()
> 	lastName = StringCol()
> 
> 
> #Person.createTable()
> 
> p1 = Person.new(firstName="Frodo", lastName="Baggins")
> p2 = Person(firstName="Bilbo", lastName="Baggins")

This must be some confusion between SQLObject 0.5 and 0.6 -- in 0.5 your 
p1 example (with .new()) is correct, in 0.6 (which is unrelease, and 
only available through svn://colorstudy.com/trunk/SQLObject) it is like 
your p2 example.

-- 
Ian Bicking  /  ia...@co...  / http://blog.ianbicking.org

Re: [SQLObject] unknown column table creation..novice

[Ian B. <ia...@co...>]

Brian Ray wrote:
> Thanks for SQLObject, it's great!
> 
> I have used SQLObject with one project and I am working on my second. Still,
> I am rather new to Python and totally new to SQLobject so excuse me if this
> question is dumb.
> 
> I am looking for a way to created a table from a list of values. Say I have:
> 
>      headers = ['bbd_id', 'co_name1', 'address1', 'address2', 'city',
> 'state', 'zip5', 'zip4', 'first_name', 'surname', 'telephone']

class MailList(SQLObject):
     pass

for header in headers:
     MailList.addColumn(Col(name=header))

-- 
Ian Bicking  /  ia...@co...  / http://blog.ianbicking.org

Re: [SQLObject] support for EXISTS()

[Ian B. <ia...@co...>]

Dennis Heuer wrote:
> I couldn't find a note about the SQL EXISTS() function. Is it supported or will it be supported by 0.6?

There aren't any plans for it.  I'm not sure exactly how it would work 
into things -- aggregating functions are a little challenging.  Select 
results do have a .count() method, which is somewhat similar.

-- 
Ian Bicking  /  ia...@co...  / http://blog.ianbicking.org

Re: [SQLObject] One to many relationships manipulation

[Ian B. <ia...@co...>]

Robert Leftwich wrote:
> Warning SQLObject (and Python) newbie question follows:
> 
> I have a requirement to display the data from the 'many' side of a 
> relationship (e.g. the addresses in a person-address) in an html table, 
> where each column in the html table is one row from the 'many' table 
> (i.e. each property of the address object is displayed in a new row of 
> the same column). I have a working solution but it is not pretty and I 
> wonder if there is an efficient SQLObject (or Pythonic way) to do this?
> 
> An illustration may help. I need to go from:
> 
> Person
>   Address1(street, city, state, zip)
>   Address2(street, city, state, zip)
>   Address3(street, city, state, zip)
>   Address4(street, city, state, zip)
> 
> to:
> 
> Person
>   Address1.street  Address2.street  Address3.street Address4.street
>   Address1.city    Address2.city    Address3.city   Address4.city
>   Address1.state   Address2.state   Address3.state  Address4.state
>   Address1.zip     Address2.zip     Address3.zip    Address4.zip

You'll simply have to do this as a loop, like:

addresses = Person.addresses()

for address in addresses:
     print address.street
for address in addresses:
     print address.city

etc.  You could be fancy and do:

for attr in ['street', 'city', 'state', 'zip']:
     for address in addresses:
         print getattr(address, attr)

-- 
Ian Bicking  /  ia...@co...  / http://blog.ianbicking.org

Re: [SQLObject] column types

[Ian B. <ia...@co...>]

Dennis Heuer wrote:
> your column types are somewhat unclean. For example, why you use INT
> for a boolean value. The most close type would be TINYINT (SMALLINT
> where TINYINT isn't supported). Using a CHAR(1) and character-set
> safe binar values like \x60 and \x61 is another alternative.
> 
> Your StringCol(length) seems to be byte-oriented. It would be nice if
> there was a character-based length attribute.

A lot of these issues are database-specific.  E.g., character vs. byte 
lengths; I don't know how that would work in all the different 
databases.  BOOLEAN is another database-specific thing -- I think 
TINYINT is used for some backends, BOOLEAN elsewhere, maybe INT for a 
couple places.  Often it doesn't really matter, as some of these data 
types are holdovers from times when resources were more restricted.

--
Ian Bicking  /  ia...@co...  / http://blog.ianbicking.org

[SQLObject] Hi, I'm back.

[Ian B. <ia...@co...>]

OK, after stepping away from SQLObject for a while, I'm trying to get 
back into it -- I'm starting to use it for some projects at work, and 
I'm feeling a little more comfortable with my general balance of 
computer and non-computer life.

Last night I closed most of the bugs on SourceForge.  If anyone knows of 
other bugs, they should have put them in the bug tracker!  I'm not going 
to read through all the old messages carefully from when I was 
disengaged, since a lot of the questions are out of date, or people have 
figured out the solutions collectively or on their own.  BTW, I very 
much appreciate that people have been answering questions.  So if you 
have open bugs, post them to SF, or open questions or comments, please 
resend them to this list.

I'd like to make a 0.5.3 release very soon, for real.  It will be the 
last 0.5 release.  Then I'd like to make an 0.6 release fairly soon 
after, without adding too many other features.  After that I'd like to 
spend some time doing code cleanup, breaking things up into smaller 
modules, etc.  If anyone has opinions about 0.6, please bring them up 
soon, as I want to limit the number of API changes as much as I can. 
0.7 will probably have a new way of defining classes, but classes and 
instances will have the same public methods.

Cheers... Ian

--
Ian Bicking  /  ia...@co...  / http://blog.ianbicking.org

[SQLObject] problem with contructor of an object

[Markus J. <mar...@ya...>]

hello

this is my sample code:

---
from SQLObject import *


conn = MySQLConnection(user='abc', db='abook', passwd='def')

class Person(SQLObject):

	_connection = conn

	firstName = StringCol()
	lastName = StringCol()


#Person.createTable()

p1 = Person.new(firstName="Frodo", lastName="Baggins")
p2 = Person(firstName="Bilbo", lastName="Baggins")
---


creating p1 works fine and Frodo is inserted into the database.
but Bilbo does not work. but according to the Documentation it should
work if I understand this correctly.


I get this error:


p2 = Person(firstName="Bilbo", lastName="Baggins")
TypeError: __new__() got an unexpected keyword argument 'lastName'


I have Python 2.3.3 
and a SQLObject Snapshot I downloaded a few days ago.

but I am not sure if this is the correct version. the documentation says
0.6, but setup.py says 5.1


any hints ?

I am quite new to SQLObject, so maybe I just did not understand
everything correctly.

regards

Markus

[SQLObject] unknown column table creation..novice

[Brian R. <br...@se...>]

Thanks for SQLObject, it's great!

I have used SQLObject with one project and I am working on my second. Still,
I am rather new to Python and totally new to SQLobject so excuse me if this
question is dumb.

I am looking for a way to created a table from a list of values. Say I have:

     headers = ['bbd_id', 'co_name1', 'address1', 'address2', 'city',
'state', 'zip5', 'zip4', 'first_name', 'surname', 'telephone']

Is there a way to define my class attributes from this list?

I tried playing with the _columns like this:

#.....snip...does not work......

     class mail_list(SQLObject):
          def __init__(self, headers):
             for h in headers:
                  self._columns.append("StringCol("+h+")")
     mail_list.createTable()

#....snip

But I was unsure on how to pass headers while initiating the class with
'createTable'. I end up with a table with only one field, id. Is there a way
to inherit these values from elsewhere?

Then I have made a string of arguments to use when initialize the new
instance:

     new_values  = "bbd_id='1', co_name1='big corp', address1='123
AnyStreet', address2='', city='Sample', state='ST', zip5='12345',
zip4='9999', first_name='Bob', surname='Villa', telephone='555-1212'"

I plan on simply creating each row with the string:

     row = mail_list.new.new(new_values)

Thanks in advance for any help.


SQLObject-0.5.2
Python 2.3

[SQLObject] MultipleJoin

[Philippe N. <sw...@fr...>]

Hi,

I'm having a little problem with two SQLObjects:

from sqlobject import *
import datetime
now =3D datetime.datetime.now
__connection__ =3D dbconnection.connectionForURI('sqlite:///tmp/toto.db')

class Album(SQLObject):
    creationDate =3D DateTimeCol(default=3Dnow())
    modificationDate =3D DateTimeCol(default=3Dnow())
    ownerId =3D StringCol()
    title =3D StringCol()
    directories =3D MultipleJoin('Directory')

class Directory(SQLObject):
    dirName =3D StringCol(default=3D'')
    description =3D StringCol(default=3D'')
    album =3D ForeignKey('Album')
    #parentDir =3D ForeignKey('Directory')#,notNone=3DFalse,default=3DNone)
    creationDate =3D DateTimeCol(default=3Dnow())
    modificationDate =3D DateTimeCol(default=3Dnow())
    #objects =3D MultipleJoin('AlbumObject')

Album.dropTable(ifExists=3DTrue)
Album.createTable()
Directory.dropTable(ifExists=3DTrue)
Directory.createTable()

album1 =3D Album(ownerId=3D'foo',title=3D'blah')
print album1.directories
"" AttributeError: 'Album' object has no attribute 'directories' "

If i inspect the album1 instance i can see :

(Pdb) album1._SO_joinList[0].otherClass
<class 'Directory.Directory'>

I don't see where's my problem, I have used MultipleJoin / ForeignKey
as explained in the docs. Using latest 0.6 revision. Anyone can bring
some light to my lantern ? ;)

Philippe

[SQLObject] [Solved] MultipleJoin

[Philippe N. <sw...@fr...>]

Erh ok found my problem ...

s/directories/directorys/

Naming conventions rulez .........

[SQLObject] Re: One to many relationships manipulation

[Chris G. <ch...@il...>]

On 16 Aug 2004, Chris Gahan said:

> class Address(SQLObject):
>     [... stuff here ...]
> 
>     def __str__(cls):
>         return "%(street)s\n%(city)s, %(state)s    %(zip)s" % cls.
> __dict__
> 

Oops, I should've used "self" instead of "cls". Otherwise, it's good. :)

[SQLObject] Re: One to many relationships manipulation

[Chris G. <ch...@il...>]

On 16 Aug 2004, Robert Leftwich said:

> I have a requirement to display the data from the 'many' side of a 
> relationship (e.g. the addresses in a person-address) in an html table, 
> where each column in the html table is one row from the 'many' table 
> (i.e. each property of the address object is displayed in a new row of 
> the same column).

Hmm... How about adding a __str__() function to the Address class like 
this:

class Address(SQLObject):
    [... stuff here ...]

    def __str__(cls):
        return "%(street)s\n%(city)s, %(state)s    %(zip)s" % cls.
__dict__


Then, whenever you tried to use an address object as a string, it would 
become:

Street
City, State     ZIP

And you could just merrily print each Address object! :D

[SQLObject] Re: Re: Adding DB auto-timestamping to DateTimeCol

[Chris G. <ch...@il...>]

On 15 Aug 2004, Michael Watkins said:

> On Sun, 2004-08-15 at 01:10 +0100, Andrew Bennetts wrote:
>> There's already a "default" flag.  When left with its default value
>> (NoDefault), probably SQLObject should just omit it from the INSERT
>> statement, rather than refuse to even try (or perhaps there should be
>> a default=OmitOk or something).  Currently, there's no way to make
>> use of a "DEFAULT ..." declaration in a table definition.
> 
> Yup, exactly. And that breaks the "SQLObject should fit in with
> existing schema's" goal.

Alright. So the solution is to have some way of telling it to use the
database's built-in default value if it exists. 

It should probably be a boolean paramter to Col(), for simplicity.
Something like "defaultFromDB=True"? "useDBDefault=True"? 

I don't like the "omit" bit. Too many negatives can get confusing,
especially with booleans (eg. notNull=False). :) 

[SQLObject] support for EXISTS()

[Dennis H. <de...@tr...>]

Hello again,

I couldn't find a note about the SQL EXISTS() function. Is it supported or will it be supported by 0.6?

Thanks,
Dennis

[SQLObject] One to many relationships manipulation

[Robert L. <ro...@le...>]

Warning SQLObject (and Python) newbie question follows:

I have a requirement to display the data from the 'many' side of a 
relationship (e.g. the addresses in a person-address) in an html table, 
where each column in the html table is one row from the 'many' table 
(i.e. each property of the address object is displayed in a new row of 
the same column). I have a working solution but it is not pretty and I 
wonder if there is an efficient SQLObject (or Pythonic way) to do this?

An illustration may help. I need to go from:

Person
   Address1(street, city, state, zip)
   Address2(street, city, state, zip)
   Address3(street, city, state, zip)
   Address4(street, city, state, zip)

to:

Person
   Address1.street  Address2.street  Address3.street Address4.street
   Address1.city    Address2.city    Address3.city   Address4.city
   Address1.state   Address2.state   Address3.state  Address4.state
   Address1.zip     Address2.zip     Address3.zip    Address4.zip


Robert

Re: [SQLObject] Re: Adding DB auto-timestamping to DateTimeCol

[Michael W. <li...@mi...>]

On Sun, 2004-08-15 at 01:10 +0100, Andrew Bennetts wrote:
> There's already a "default" flag.  When left with its default value
> (NoDefault), probably SQLObject should just omit it from the INSERT
> statement, rather than refuse to even try (or perhaps there should be
> a default=OmitOk or something).  Currently, there's no way to make use
> of a "DEFAULT ..." declaration in a table definition.

Yup, exactly. And that breaks the "SQLObject should fit in with existing
schema's" goal.

[SQLObject] column types

[Dennis H. <de...@tr...>]

Hello again,

your column types are somewhat unclean. For example, why you use INT for a boolean value. The most close type would be TINYINT (SMALLINT where TINYINT isn't supported). Using a CHAR(1) and character-set safe binar values like \x60 and \x61 is another alternative.

Your StringCol(length) seems to be byte-oriented. It would be nice if there was a character-based length attribute.

Regards,
Dennis

[SQLObject] transactions

[Dennis H. <de...@tr...>]

Hello,

your documentation is quite unclear about transactions. It says that SQLite and MySQL don't support transactions. However, both SQLite and MySQL *support* transactions, and the python modules do as well. In the case of MySQL support depends on the table type. MySQLdb checks for that and always succeeds gracefully. However, I don't know if SQLObject supports setting the table type when creating a table. 

The documentation does not say if SQLObject at least tries to start a transaction and catches the exception. What is actually happening when I'm using the trans object with SQLite or MySQL? 

Please, forSQLObject 0.6, at least try to start the transaction (best based on the supported modules) and provide a way to select the table type for MySQL.

When do you expect the next release of SQLObject?

Thanks,
Dennis

Re: [SQLObject] Re: Adding DB auto-timestamping to DateTimeCol

[Andrew B. <and...@pu...>]

On Sat, Aug 14, 2004 at 02:51:28PM -0500, Luke Opperman wrote:
[...]
> 
> Isn't this a more generic idea, saying "not a required field, and don't 
> include
> when creating"? Ie, a flag that could apply to any Col type, not just
> DateTimeCol? Right now, my understanding is that we can have fields that
> aren't required for creating SO objects, but SO requires a default
> value/function to use in that case and includes it in the INSERT statement.
> Not sure what word to use for the flag, "auto" by itself is the right 
> concept
> but pretty generic. "autoInsert"? "databaseDefault"? "defaultFromDatabase"?

There's already a "default" flag.  When left with its default value
(NoDefault), probably SQLObject should just omit it from the INSERT
statement, rather than refuse to even try (or perhaps there should be a
default=OmitOk or something).  Currently, there's no way to make use of a
"DEFAULT ..." declaration in a table definition.

-Andrew.

Re: [SQLObject] Re: Adding DB auto-timestamping to DateTimeCol

[Luke O. <lu...@me...>]

> I think we should fix this. How about adding a boolean to the
> DateTimeCol class' constructor, like this:
>
>    timestamp = DateTimeCol(autoTimestamp=True)
>
> That could work. Is "autoTimestamp" a keyword people are familiar with?
> :)

Isn't this a more generic idea, saying "not a required field, and don't include
when creating"? Ie, a flag that could apply to any Col type, not just
DateTimeCol? Right now, my understanding is that we can have fields that
aren't required for creating SO objects, but SO requires a default
value/function to use in that case and includes it in the INSERT statement.
Not sure what word to use for the flag, "auto" by itself is the right concept
but pretty generic. "autoInsert"? "databaseDefault"? "defaultFromDatabase"?

- Luke