sqlobject-discuss — All things SQLObject-related

[SQLObject] Converter for struct_time

[Chris A. <ch...@at...>]

Hi there,

I wrote this small converter for python's standard struct_time object:

diff -r1.2 Converters.py
14a15
> import time
83a85,90
>
> if hasattr(time, "struct_time"):
>     def StructTimeConverter(value):
>         return time.strftime("'%Y-%m-%d %H:%M:%S'", value)
>
>     registerConverter(time.struct_time, StructTimeConverter)

I filed this in SF's bug tracker a while ago:
http://sourceforge.net/tracker/index.php?func=detail&aid=774238&group_id=74338&atid=540672

Hope this is useful!

Cheers
-- 
Chris AtLee <ch...@at...>

Re: [SQLObject] New FAQ

[J-P L. <jp...@si...>]

Ian Bicking wrote:

>There's a FAQ up on the website:
>
>  http://sqlobject.org/docs/FAQ.html
>
>I'm happy to add more examples and problems, especially idioms for
>solving typical issues.  It should also probably contain something about
>BOOLEAN, Binaries, composite IDs, non-int IDs... any other common issues
>that aren't solved but should be (and are asked about)?
>  
>

Caching - how to manually purge/get a cache object, etc.?

[SQLObject] New FAQ

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

There's a FAQ up on the website:

  http://sqlobject.org/docs/FAQ.html

I'm happy to add more examples and problems, especially idioms for
solving typical issues.  It should also probably contain something about
BOOLEAN, Binaries, composite IDs, non-int IDs... any other common issues
that aren't solved but should be (and are asked about)?

  Ian

Re: [SQLObject] Character escaping for strings

[Javier R. <jav...@Ho...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ian Bicking wrote:

> On Wednesday, August 13, 2003, at 08:56 PM, Javier Ruere wrote:
>
>>     When storing strings, the characters in them are escaped but they are
>> not unescaped when retrieving the string from the database. Is this a
>> bug or the intended behaviour?
>>
> That's odd... that shouldn't be the behavior, but it's the
> responsibility of the database driver (psycopg, MySQLdb, etc) to do
> anything related to converting data that comes from the database.
>
>   Ian

Ian,
  Welcome back!
  But IIRC SQLObject converts the scaped characters
(for ex. '\n' -> '\\n') so shouldn't it convert the characters back?
  I got the problem while trying to store th result from a pickle.

Later,
	Javier

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/RDxD8XQC840MeeoRAqk7AJ4gGoxuBj6u5rkhWNefB+E69fBmbQCgrngx
bKVMmfC0iMiVhAHkjS3RmQk=
=4Rap
-----END PGP SIGNATURE-----

Re: [SQLObject] Binary data

[Javier R. <jav...@Ho...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ian,
    Thanks for answering.

Ian Bicking wrote:

> On Saturday, August 9, 2003, at 07:25 PM, Javier Ruere wrote:
>
>>     How can binary data be stored with SQLObject?
>>     I'm trying to pickle something and storing it but caracters are
>> scaped.
>> How can I avoid this?
>>
> This response is a little late, but I don't see another response.
>
> There's an option to Pickle to use a string representation, but that's
> only really a string if all the components can be represented as strings
> (i.e., you don't have any binary instance variables in your objects).

  Yes, I already tried this but doesn't seem to work. For example:

	>>> pickle.dumps('Hello')
	"S'Hello'\np0\n."

  The pickle of 'Hello' has a '\n' in it which when passed through
SQLObject returns as '\\n' which cannot be unpickled. I worked around
this be searching for the function which does this modification, copying
the list of tuples and making the inverse operation.
  Why is this done? I tried storing strings with '\n' in SQLite and it
seems to work alright. Is there a problem with the other DBs? I will be
using this system with SQLite and Postgress eventually so I would be
very interested in knowing this.

> I haven't really used much binary data, so I'm not sure how to represent
> that to the database.  Obviously the data must be escaped somehow.  If
> you figure it out for your database, and say make a dbEscape() function
> that creates the SQL version of the value (complete with '' if
> necessary) you could so something like:
>
> class Binary:
>     def __init__(self, value):
>         self.value = value
>     def sqlRepr(self):
>         return dbEscape(self.value)
>
> You could also use something like base64, like:
>
>     def _set_binaryColumn(self, value):
>          self._SO_set_binaryColumn(value.encode('base64'))
>     def _get_binaryColumn(self):
>          return self._SO_get_binaryColumn().decode('base64')

  I have check this possibility as well but they all count on using '\n'
so there would be little benefit.

Later,
	Javier

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/RD768XQC840MeeoRAnDeAJ4oBXIXhsIzSbV67aLAzsEpZMjwugCdGmXg
eLVSkscsSUnR1Q7vy5Rific=
=AOD/
-----END PGP SIGNATURE-----

Re: [SQLObject] Character escaping for strings

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

On Wed, 2003-08-20 at 22:27, Javier Ruere wrote:
> > On Wednesday, August 13, 2003, at 08:56 PM, Javier Ruere wrote:
> >
> >>     When storing strings, the characters in them are escaped but they are
> >> not unescaped when retrieving the string from the database. Is this a
> >> bug or the intended behaviour?
> >>
> > That's odd... that shouldn't be the behavior, but it's the
> > responsibility of the database driver (psycopg, MySQLdb, etc) to do
> > anything related to converting data that comes from the database.
> >
> >   Ian
> 
> Ian,
>   Welcome back!
>   But IIRC SQLObject converts the scaped characters
> (for ex. '\n' -> '\\n') so shouldn't it convert the characters back?
>   I got the problem while trying to store th result from a pickle.

No, SQLObject creates SQL queries, and to include \n in a string in a
SQL query, of course you have to quote it.  When SQLObject fetches data
from the database, it does not receive SQL literals, but rather receives
raw data, so no unquoting is necessary.

It is possible that SQLObject is not quoting some characters properly
(maybe the low control characters), and that could cause a problem.

  Ian

Re: [SQLObject] *id* column mandatory? BLOBS ?

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

On Wednesday, August 13, 2003, at 04:35 PM, Luke Opperman wrote:
> I haven't looked at what it will take to support BLOBs, my guess is a 
> release
> implementation won't exist until the marshall/unmarshall column code 
> exists
> (same thing that full BooleanCol support is waiting for).

The elegant implementation will have to wait, but you should still be 
able to use them now.  I posted a message with the subject "Binary 
Data" today, which kind of outlines it.

   Ian

Re: [SQLObject] Character escaping for strings

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

On Wednesday, August 13, 2003, at 08:56 PM, Javier Ruere wrote:
> 	When storing strings, the characters in them are escaped but they are
> not unescaped when retrieving the string from the database. Is this a
> bug or the intended behaviour?
>
That's odd... that shouldn't be the behavior, but it's the 
responsibility of the database driver (psycopg, MySQLdb, etc) to do 
anything related to converting data that comes from the database.

   Ian

Re: [SQLObject] Binary data

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

On Saturday, August 9, 2003, at 07:25 PM, Javier Ruere wrote:
> 	How can binary data be stored with SQLObject?
> 	I'm trying to pickle something and storing it but caracters are 
> scaped.
> How can I avoid this?
>
This response is a little late, but I don't see another response.

There's an option to Pickle to use a string representation, but that's 
only really a string if all the components can be represented as 
strings (i.e., you don't have any binary instance variables in your 
objects).

I haven't really used much binary data, so I'm not sure how to 
represent that to the database.  Obviously the data must be escaped 
somehow.  If you figure it out for your database, and say make a 
dbEscape() function that creates the SQL version of the value (complete 
with '' if necessary) you could so something like:

class Binary:
     def __init__(self, value):
         self.value = value
     def sqlRepr(self):
         return dbEscape(self.value)

You could also use something like base64, like:

     def _set_binaryColumn(self, value):
          self._SO_set_binaryColumn(value.encode('base64'))
     def _get_binaryColumn(self):
          return self._SO_get_binaryColumn().decode('base64')

   Ian

Re: [SQLObject] Composed Primary key

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

On Tuesday, August 5, 2003, at 05:59 PM, Magnus Lyck=E5 wrote:
> order_line
> |id |order_id|spec          |price_amt|price_currency|
> +---+--------+--------------+---------+--------------+
> |  1|     123|Dog collar    |12.50    |EUR           |
> |  2|     123|Dog food      |0.34     |USD           |
>
>
> In this case you could say that the price is a value from a
> design point of view, but an object from a programming point
> of view. Martin Fowler calls such objects "Value Objects" in
> the book "Patterns of Enterprise Application Architechture".

Indeed, any value object (i.e., immutable) will work fine with=20
SQLObject, using a certain idiom:

class OrderLine(SQLObject):
     priceAmt =3D CurrencyCol()
     priceCurrency =3D String(length=3D3)

     def _get_price(self):
         return Price(self.priceAmt, self.priceCurrency)
     def _set_price(self, value):
         self.set(priceAmt=3Dvalue.amount, priceCurrency=3Dvalue.currency)=


class Price(object):
     def __init__(self, amount, currency):
         self.amount =3D amount
         self.currency =3D currency
     # and probably more methods too


It is important that Price be immutable (or at least treated that way),=20=

as the OrderLine instance won't detect changes to a Price instance, so=20=

they won't be saved in the database.

In fact, Price instances could be mutable if the instances were also=20
given a reference to the containing OrderLine, and notified it of=20
changes.

The whole idiom could be encapsulated in something like a=20
compositeproperty (analogous to property), but that would be only=20
slightly more compact while being less clear, IMHO.

I think this idiom is sufficient, so I don't plan any other support. =20
Except for the primary key, which is somewhat unique in that SQLObject=20=

uses that column internally.

   Ian

Re: [SQLObject] disabling select after insert on newly created objects

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

On Wed, 2003-08-06 at 09:07, Dave Poirier wrote:
> I've noticed that 2 (well 3, but commit is ok) queries gets done
> everytime I create a new record in the database:
[...]
> Is there a way to tell SQLObject not to perform this select everytime I
> create a new Smdr_uniformized object?

No, afraid not.  At some point this should be fixed, as it obviously is
redundant, but currently all your arguments to .new() are thrown away,
and the column values have to be fetched to create the object that is
returned from .new().

  Ian

Re: [SQLObject] large retrieval of data

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

On Tue, 2003-08-05 at 21:35, Dave Poirier wrote:
> I'm trying to import around 7 million rows from one database connection
> to another.  Due to the enormous quantity of data, displaying some
> progress to the user is a basic requirement.  I've tried various methods
> but it seems that SQLObject, even when using the _connection directly,
> fetch all the data from the database connection before returning from
> the execute statement.
> 
>   #! /usr/local/bin/python -u
> 
>   from myclasses import *
> 
>   connection = X_sqlobject._connection
>   c = connection.getConnection()
>   print "selecting..."
>   c.execute('SELECT * FROM remote_table;')
>   print "fetching..."
>   print c.fetchone()
> 
> I get the "selecting..." right away but it takes some serious time, with
> heavy network activity before "fetching..." ever appears.  Is there any
> way to use the database connection so as to be able to display progress
> while the records are being fetched?

I presume from the example that it's actually the database driver that's
taking a long time for you -- or perhaps the underlying database takes a
long time to prepare the results (which is quite likely).  Did Frank's
suggestion to batch (i.e., using limit/offset) help?  If it's the
database that is the bottleneck, it might not (or even be worse), since
the database may take a long time to decide what portion of the results
to send.  Anyway, curious how it turned out.

  Ian

Re: [SQLObject] *id* column mandatory? BLOBS ?

[Jim V. <Jim...@no...>]

Thanks for the confirmation.

I asked this question, because we have some existing tables that do not have an
integer primary key, but I seem to be able to use SQLObject to read from them if I
tell SQLObject that some non-integer column is the *id* column (via _idName).

From your response, however, this is probably not safe.

Frank Barknecht wrote:

> Hallo,
> Jim Vickroy hat gesagt: // Jim Vickroy wrote:
>
> > May SQLObject be used with a table that has no primary key column (e.g., with a
> > table whose key is a combination of columns -- some perhaps being non-integer)?
>
> Currently no. An integer ID column is mandatory. From the docs: "You
> can override the name, but some integer primary key must exist."
>
> ciao
> --
>  Frank Barknecht                               _ ______footils.org__
>
> -------------------------------------------------------
> This SF.Net email sponsored by: Free pre-built ASP.NET sites including
> Data Reports, E-commerce, Portals, and Forums are available now.
> Download today and enter to win an XBOX or Visual Studio .NET.
> http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

[SQLObject] Character escaping for strings

[Javier R. <jav...@Ho...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,
	When storing strings, the characters in them are escaped but they are
not unescaped when retrieving the string from the database. Is this a
bug or the intended behaviour?

Thanks,
	Javier
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/Ouxh8XQC840MeeoRApjbAKCjZptvjst/0XwZtS2JIXvU9eqgTwCeLTrb
y7s0vBzliZw56jPLF6a62HI=
=ay3W
-----END PGP SIGNATURE-----

Re: [SQLObject] *id* column mandatory? BLOBS ?

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

The current version of SQLObject only supports integer primary key columns (can
use a different name than 'id' using the _idName class attribute). I believe
there is a patch to allow non-integer single key columns. There has been a lot
of discussion of multi-column primary keys, but I'm not aware of any
implementation.

There has been no effort towards having tables with *no* primary key
constraint, but I don't think that's what you're really wanting.

I haven't looked at what it will take to support BLOBs, my guess is a release
implementation won't exist until the marshall/unmarshall column code exists
(same thing that full BooleanCol support is waiting for).

Sorry I can't be more helpful,

- Luke

Quoting Jim Vickroy <Jim...@no...>:

> Thanks for the information.
>
> Sorry that my question was imprecisely worded.
>
> May SQLObject be used with a table that has no primary key column (e.g.,
> with a
> table whose key is a combination of columns -- some perhaps being
> non-integer)?
>
> -- jv
>
> Dave Poirier wrote:
>
> > On Wed, 2003-08-13 at 13:36, Jim Vickroy wrote:
> > > Hello,
> > >
> > > >From the documentation, it appears that SQLObject is for use with
> tables
> > > that have an *id* column.  May SQLObject be used with tables that do not
> > > have an *id* column?
> > >
> > > Also, a previous post asked about support for BLOBS.  Presently, there
> > > appears to be no such support.  Are there plans to support BLOBS?
> > >
> > > Thanks,
> > >
> > > -- jv
> >
> > Yes, it is possible to name another column as being the primary key.
> >
> > alternateID:
> >
> >     This boolean (default False) indicates if the column can be used as
> > an ID for the field (for instance, a username), though it is not a
> > primary key. If so a class method will be added, like byUsername which
> > will return that object. Use alternateMethodName if you don't like the
> > by* name (e.g. alternateMethodName="username").
> >
> >     The column should be declared UNIQUE in your table schema.
> >
> > see http://sqlobject.org/docs/SQLObject.html for further details.
> >
> > -Dave
>
>
>
> -------------------------------------------------------
> This SF.Net email sponsored by: Free pre-built ASP.NET sites including
> Data Reports, E-commerce, Portals, and Forums are available now.
> Download today and enter to win an XBOX or Visual Studio .NET.
> http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>


--
The Pursuit of Counterfactual Histories

Re: [SQLObject] *id* column mandatory? BLOBS ?

[Frank B. <fb...@fo...>]

Hallo,
Jim Vickroy hat gesagt: // Jim Vickroy wrote:

> May SQLObject be used with a table that has no primary key column (e.g., with a
> table whose key is a combination of columns -- some perhaps being non-integer)?

Currently no. An integer ID column is mandatory. From the docs: "You
can override the name, but some integer primary key must exist."

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] *id* column mandatory? BLOBS ?

[Jim V. <Jim...@no...>]

Thanks for the information.

Sorry that my question was imprecisely worded.

May SQLObject be used with a table that has no primary key column (e.g., with a
table whose key is a combination of columns -- some perhaps being non-integer)?

-- jv

Dave Poirier wrote:

> On Wed, 2003-08-13 at 13:36, Jim Vickroy wrote:
> > Hello,
> >
> > >From the documentation, it appears that SQLObject is for use with tables
> > that have an *id* column.  May SQLObject be used with tables that do not
> > have an *id* column?
> >
> > Also, a previous post asked about support for BLOBS.  Presently, there
> > appears to be no such support.  Are there plans to support BLOBS?
> >
> > Thanks,
> >
> > -- jv
>
> Yes, it is possible to name another column as being the primary key.
>
> alternateID:
>
>     This boolean (default False) indicates if the column can be used as
> an ID for the field (for instance, a username), though it is not a
> primary key. If so a class method will be added, like byUsername which
> will return that object. Use alternateMethodName if you don't like the
> by* name (e.g. alternateMethodName="username").
>
>     The column should be declared UNIQUE in your table schema.
>
> see http://sqlobject.org/docs/SQLObject.html for further details.
>
> -Dave

[SQLObject] *id* column mandatory? BLOBS ?

[Jim V. <Jim...@no...>]

Hello,

From the documentation, it appears that SQLObject is for use with tables
that have an *id* column.  May SQLObject be used with tables that do not
have an *id* column?

Also, a previous post asked about support for BLOBS.  Presently, there
appears to be no such support.  Are there plans to support BLOBS?

Thanks,

-- jv

Re: [SQLObject] Composed Primary key

[Brad B. <br...@bb...>]

On Sunday, August 10, 2003, at 06:57 AM, Magnus Lyck=E5 wrote:

> At 19:13 2003-08-05 -0400, Brad Bollenbach wrote:
>
>> On Tuesday, August 5, 2003, at 06:59 PM, Magnus Lyck=E5 wrote:
>>
>>> At 14:18 2003-07-24 -0400, Brad Bollenbach wrote:
>>>> > Well, if there's going to be composite keys, that might as well =
be
>>>> > generalized to all the columns (i.e., any attribute could be a=20
>>>> composite
>>>> > of several columns).
>>>>
>>>> What's the use case for this?
>>>
>>> For instance money objects that consist of an amount and a currency.
>>> You could make them full objects with an identifier and do:
>>>
>>> order_line
>>> |id |order_id|spec          |money_id|
>>> +---+--------+--------------+--------+
>>> |  1|     123|Dog collar    |       1|
>>> |  2|     123|Dog food      |       2|
>>>
>>> money
>>> |id |amt    |currency|
>>> +---+-------+--------+
>>> |  1|12.50  |EUR     |
>>> |  2|0.34   |USD     |
>>
>> This is bad database design. amt and currency belong in the=20
>> order_line table.
>
> I think so too! That's why the propsed feature is good.
>
>>> But is seems simpler to just do:
>>>
>>> order_line
>>> |id |order_id|spec          |price_amt|price_currency|
>>> +---+--------+--------------+---------+--------------+
>>> |  1|     123|Dog collar    |12.50    |EUR           |
>>> |  2|     123|Dog food      |0.34     |USD           |
>>>
>>>
>>> In this case you could say that the price is a value from a
>>> design point of view, but an object from a programming point
>>> of view. Martin Fowler calls such objects "Value Objects" in
>>> the book "Patterns of Enterprise Application Architechture".
>>
>> I don't follow your point. At what point do Python properties stop=20
>> being useable here, and composite columns (for something other than=20=

>> the PK) become necessary?
>
> If I have a Money class, my Python class OrderLine would have
> an attribute "price", which was an instance of the Money class.
> It should *not* have a priceAmount attribute and a separate
> priceCurrency attribute. The internal composition of the price
> is not the concern of the OrderLine class!
>
> Thus, we get a mismatch between Python classes and RDBMS tables.

To reiterate, this can already be solved using properties.

> =46rom an OO Design point of view, you separate your data into
> two categories: Objects and values. An object is something that
> has an identity on its own, like a person, a window, or an
> order line. A value is some data which just describes some
> property of another object, but doesn't have its own identity,
> such as a length, a name, or a monetary amount.
>
> In Python, it's very common that design objects are instances
> of a class, and design values have another type than instance,
> such as long, float or string, but that's not always the case.
>
> So, while the idea that a class in Python corresponds to a
> table in SQL fits the bill in most cases, this is not always
> the case. There can be mismatches in both directions.

Not in SQLObject there can't :) Each SQLObject-derived class=20
corresponds to one database table, though, naturally not necessarily=20
every property of your SQLObject need represent exactly one column.

> If you have simple non-scalar objects like dates with time-zones,
> money objects with a currency, measurements with a unit and a
> given precision etc, you would probably want to "inline" these
> values into your main table, rather than to assign some kind
> of made up identity to these values and store them in a separate
> table. You still implement these "value objects" as instances of
> their own class in Python though.

I disagree here. This is a use case for properties, not for "composite=20=

columns".

> The other way around, you can have a list of integers as a simple
> attribute in a Python class, and then you want to go the other
> way around, and split your Python class into more than one table.
>
> It would be nice if SQLObject handles both these deviations from
> the typical 1 class =3D=3D 1 table situation in a convenient way.
>
> E.g. Python classes that have attributes like below...
>
> class Money
>     amt =3D int()
>     currency =3D str()
>
> class OrderLine
>     id =3D oid()
>     order_id =3D oid from Order()
>     spec =3D str()
>     price =3D Money()
>
> ...should be stored in *one* (not two) tables as we seem to
> agree on above, with as little tweaking as possible.

This design seems rather odd to me. It would probably make more sense=20
to do it like:

class Currency(SQLObject):
     currency_code =3D StringCol(length =3D 3, notNone =3D True)
     order_lines =3D MultipleJoin('OrderLine')

class OrderLine(SQLObject):
     ...
     amount =3D CurrencyCol(notNone =3D True)
     currency =3D ForiegnKey('Currency')

At which point the need for composite columns in this case evaporates.=20=

I've handled a multi-currency credit card payment processing system in=20=

precisely this way with SQLObject.

But -- to give another example (in addition to one I posted earlier in=20=

this thread) -- let's say you had a burning desire to have a "composite=20=

column" for transaction fees on credit cards.

So:

class TransactionFee(SQLObject):
     card_type =3D ForeignKey('CardType')
     tx_fee =3D CurrencyCol()
     commission_percentage =3D FloatCol()

Now, each purchase transaction *has* to record the tx fee and=20
commission percentage in its row, because the fees are subject to=20
change anytime, but we want to ensure we're only charging the customer=20=

exactly the rate they were quoted at the time the sale was made. In=20
this case, it's a bit tedious to manually assign to tx_fee and=20
commission_percentage in PurchaseTransaction every time, so we'll=20
create a composite by using Python properties:

class PurchaseTransaction(SQLObject):
     ...
     card_type =3D ForeignKey('CardType')
     tx_fee =3D CurrencyCol()
     commission_percentage =3D FloatCol()

     def _set_transaction_charge(self, value):
         self.tx_fee =3D value.tx_fee
         self.commission_percentage =3D value.commission_percentage

     transaction_charge =3D property(None, _set_transaction_charge)

In this particular case, I've left out the getter, because at any given=20=

moment in time the tx_fee/com % combo in a purchase transaction doesn't=20=

necessarily currently exist in the transaction_fee table, because the=20
admin may have since adjusted the rates for that card.

The nice thing about using Python properties instead of trying to build=20=

something into SQLObject here is that:

1. We use a feature of Python for one of its intended use cases,=20
instead of reinventing the wheel in SQLObject.

2. We document the class better, because I don't have to read the=20
implementation of another class to figure out what columns I have (e.g.=20=

when I read my class definition, and I want to know  "how do I sum up=20
all the transaction fees for this month for all of the transactions?" I=20=

wouldn't be able to answer this if I had wrapped tx_fee and=20
commission_percentage up into some external class and assigned that to=20=

a "composite column").

Explicit is better than implicit, as is using a feature that Python=20
already has, rather than reinventing it in SQLObject.

--
Brad Bollenbach
BBnet.ca=

Re: [SQLObject] Composed Primary key

[Magnus <ma...@th...>]

At 19:13 2003-08-05 -0400, Brad Bollenbach wrote:

>On Tuesday, August 5, 2003, at 06:59 PM, Magnus Lyck=E5 wrote:
>
>>At 14:18 2003-07-24 -0400, Brad Bollenbach wrote:
>>> > Well, if there's going to be composite keys, that might as well be
>>> > generalized to all the columns (i.e., any attribute could be a=
 composite
>>> > of several columns).
>>>
>>>What's the use case for this?
>>
>>For instance money objects that consist of an amount and a currency.
>>You could make them full objects with an identifier and do:
>>
>>order_line
>>|id |order_id|spec          |money_id|
>>+---+--------+--------------+--------+
>>|  1|     123|Dog collar    |       1|
>>|  2|     123|Dog food      |       2|
>>
>>money
>>|id |amt    |currency|
>>+---+-------+--------+
>>|  1|12.50  |EUR     |
>>|  2|0.34   |USD     |
>
>This is bad database design. amt and currency belong in the order_line=
 table.

I think so too! That's why the propsed feature is good.

>>But is seems simpler to just do:
>>
>>order_line
>>|id |order_id|spec          |price_amt|price_currency|
>>+---+--------+--------------+---------+--------------+
>>|  1|     123|Dog collar    |12.50    |EUR           |
>>|  2|     123|Dog food      |0.34     |USD           |
>>
>>
>>In this case you could say that the price is a value from a
>>design point of view, but an object from a programming point
>>of view. Martin Fowler calls such objects "Value Objects" in
>>the book "Patterns of Enterprise Application Architechture".
>
>I don't follow your point. At what point do Python properties stop being=20
>useable here, and composite columns (for something other than the PK)=20
>become necessary?

If I have a Money class, my Python class OrderLine would have
an attribute "price", which was an instance of the Money class.
It should *not* have a priceAmount attribute and a separate
priceCurrency attribute. The internal composition of the price
is not the concern of the OrderLine class!

Thus, we get a mismatch between Python classes and RDBMS tables.

 From an OO Design point of view, you separate your data into
two categories: Objects and values. An object is something that
has an identity on its own, like a person, a window, or an
order line. A value is some data which just describes some
property of another object, but doesn't have its own identity,
such as a length, a name, or a monetary amount.

In Python, it's very common that design objects are instances
of a class, and design values have another type than instance,
such as long, float or string, but that's not always the case.

So, while the idea that a class in Python corresponds to a
table in SQL fits the bill in most cases, this is not always
the case. There can be mismatches in both directions.

If you have simple non-scalar objects like dates with time-zones,
money objects with a currency, measurements with a unit and a
given precision etc, you would probably want to "inline" these
values into your main table, rather than to assign some kind
of made up identity to these values and store them in a separate
table. You still implement these "value objects" as instances of
their own class in Python though.

The other way around, you can have a list of integers as a simple
attribute in a Python class, and then you want to go the other
way around, and split your Python class into more than one table.

It would be nice if SQLObject handles both these deviations from
the typical 1 class =3D=3D 1 table situation in a convenient way.

E.g. Python classes that have attributes like below...

class Money
     amt =3D int()
     currency =3D str()

class OrderLine
     id =3D oid()
     order_id =3D oid from Order()
     spec =3D str()
     price =3D Money()

...should be stored in *one* (not two) tables as we seem to
agree on above, with as little tweaking as possible.

On the other hand, if you have something like:

class Curve
    color =3D str()
    style =3D str()
    coords =3D list of complex

E.g.

c =3D Curve()
c.color =3D 'RED'
c.style =3D 'DASHED'
c.coords =3D [(1+2j), (3+5j), (3+2j)]

Then you obviously need two tables to comply to the first
normal form. One with an id, the color and the style, and
a separate table with the coordinate list.


--
Magnus Lycka (It's really Lyckå), ma...@th...
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The Agile Programming Language=20

[SQLObject] Binary data

[Javier R. <jav...@Ho...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,
	How can binary data be stored with SQLObject?
	I'm trying to pickle something and storing it but caracters are scaped.
How can I avoid this?

Later,
	Javier
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/NZD18XQC840MeeoRAvl0AKCjDOrB2oWeajEY+9SXB0MVZfPCTACaAq4D
m/WwL8/kpunortLYe/DD3qY=
=yjkw
-----END PGP SIGNATURE-----

[SQLObject] disabling select after insert on newly created objects

[Dave P. <dpo...@te...>]

I've noticed that 2 (well 3, but commit is ok) queries gets done
everytime I create a new record in the database:

7726 Query       INSERT INTO smdr_uniformized (extension, dialed,
source_id, start_time, call_type_id, source_row, pbx_id, system_id,
end_time, trunk, transaction_id, tenant) VALUES (208, '', 1,
'20030509162933', 1, 2998, 1, 2, '20030509163138', 100, 1, 1)
7726 Query       COMMIT
7726 Query       SELECT transaction_id, extension, dialed, start_time,
source_row, system_id, pbx_id, source_id, end_time, trunk, call_type_id,
tenant FROM smdr_uniformized WHERE id = 2969


Is there a way to tell SQLObject not to perform this select everytime I
create a new Smdr_uniformized object?


code reference:
---------------

  record_count = len( Smdr_nec_2400.select(
Smdr_nec_2400.q.transactionID == trans.id ))
  print "converting",record_count, "records.<br />"
  for record_start in range(0,record_count,100):

    print record_start, '...'
    for smdr_nec_2400 in Smdr_nec_2400.select(
Smdr_nec_2400.q.transactionID == trans.id )[record_start:record_start +
99]:

      #--# Uniformize Start/End time #--#
      #  \---------------------------/  #
      uniform_start_time = '20'\
        + smdr_nec_2400.start_time_year\
        + smdr_nec_2400.start_time
      uniform_end_time = '20'\
        + smdr_nec_2400.end_time_year\
        + smdr_nec_2400.end_time


      #--# Create Uniformized Record #--#
      #  \---------------------------/  #
      Smdr_uniformized.new(
        system          = smdr_nec_2400.systemID,
        source          = 1,
        source_row      = smdr_nec_2400.id,
        pbx             = smdr_nec_2400.pbxID,
        tenant          = smdr_nec_2400.tenant_no,
        extension       = smdr_nec_2400.extension_no,
        trunk           = smdr_nec_2400.trunk_no,
        start_time      = uniform_start_time,
        end_time        = uniform_end_time,
        call_type       = nec_calltypes[ smdr_nec_2400.type_of_record ],
        dialed          = smdr_nec_2400.called_no,
        transaction     = trans.id )

  print "completed.<br />"
---------------------
end of code reference


Also, thanks to Frank Barknecht for his code snippet, allowed me to
figure out how to do limits using SQLObject! :)

Thanks,
-Dave.

Re: [SQLObject] large retrieval of data

[Frank B. <fb...@fo...>]

Hallo,
Dave Poirier hat gesagt: // Dave Poirier wrote:

> I'm trying to import around 7 million rows from one database connection
> to another.  Due to the enormous quantity of data, displaying some
> progress to the user is a basic requirement.  I've tried various methods
> but it seems that SQLObject, even when using the _connection directly,
> fetch all the data from the database connection before returning from
> the execute statement.
> 
>   #! /usr/local/bin/python -u
> 
>   from myclasses import *
> 
>   connection = X_sqlobject._connection
>   c = connection.getConnection()
>   print "selecting..."
>   c.execute('SELECT * FROM remote_table;')
>   print "fetching..."
>   print c.fetchone()
> 
> I get the "selecting..." right away but it takes some serious time, with
> heavy network activity before "fetching..." ever appears.  Is there any
> way to use the database connection so as to be able to display progress
> while the records are being fetched?

I'd use a combination of COUNT and LIMIT to do what the SO docs call a 
"batched query" like 
* select count(*) from rtable
* store count, divide by 10k to get decent intervals
* select * from rtable limit(x,y)

Depending on the underlying database the limit-clause will be different.

In SQLObject this would be like this pseudocode: 

length = len( RemoteTable.select("all") )
steps = int( length/10000 )
start=0
results = []

while r = RemoteTable.select("all")[start:start + steps]:
	results.append(r)
	print "still selecting..."
	start += steps

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

[SQLObject] large retrieval of data

[Dave P. <dpo...@te...>]

I'm trying to import around 7 million rows from one database connection
to another.  Due to the enormous quantity of data, displaying some
progress to the user is a basic requirement.  I've tried various methods
but it seems that SQLObject, even when using the _connection directly,
fetch all the data from the database connection before returning from
the execute statement.

  #! /usr/local/bin/python -u

  from myclasses import *

  connection = X_sqlobject._connection
  c = connection.getConnection()
  print "selecting..."
  c.execute('SELECT * FROM remote_table;')
  print "fetching..."
  print c.fetchone()

I get the "selecting..." right away but it takes some serious time, with
heavy network activity before "fetching..." ever appears.  Is there any
way to use the database connection so as to be able to display progress
while the records are being fetched?

[SQLObject] (no subject)

[Brad B. <br...@bb...>]

On Tuesday, August 5, 2003, at 08:31 PM, Bruno Trevisan wrote:

> Hi There
>
>>
>>> At 14:18 2003-07-24 -0400, Brad Bollenbach wrote:
>>>>> Well, if there's going to be composite keys, that might as well be
>>>>> generalized to all the columns (i.e., any attribute could be a
>>>> composite
>>>>> of several columns).
>>>>
>>>> What's the use case for this?
>
> So I think a have a situation where some could need this:
> (note, this is existent database, system under development has to
> integrate to it, so bad database layout is not a way out here. Badly
> designed databases are out there and we must work with some of them) :(
>
>
> products_table
> +---+-----------------------+
> |id | description    | family_id | subfamily_id |
> +---+----------------+-----------+--------------+
> |  1| Foobar gum     |        10 |             5|
> |  1| Foobar lolipops|        10 |             6|
> |  1| Foobar socks   |        20 |             5|
>
> family_table
> +---+-----------------------+
> |id | description           |
> +---+-----------------------+
> | 10| Candies               |
> | 20| Underwear             |
>
> subfamily_table
> +---+-----------+---------------+
> |id | family_id | description   |
> +---+-----------+---------------+
> |  5|         10| chewing gum   |
> |  5|         20| socks         |
> |  6|         10| lolipops      |
>
> I need to retrieve subfamily description for my products. It is 
> possible
> to manually retrieve it, but the idea is just state to SQLObject that 
> id
> and family_id are primary keys for the subfamily table and that the
> subfamily are linked to the products through those keys. Having
> SQLObject retrieving the subfamily object when I retrieve a product
> using those definitions would be great.

Once again, that's what properties are for (see example I posted early 
on in this thread). :)

--
Brad Bollenbach
BBnet.ca