sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] Select does not work with some fieldname

[Petr J. <pet...@tp...>]

2009/11/10 Wolfgang Morawetz <wol...@gm...>

> Petr Jakeš schrieb:
> >
> >     Maybe you can printout the names SQLObject reads from the database
> >     and than use this names:
> >     for col in TCountry.sqlmeta.columnList:
> >         print col
> >
> > Or maybe:
> >
> > print col.origName, col.dbName
> >
> > Petr
> >
> Hmmm,
> for TAthlet i get:
> TUnionID TUnion_id
> short short
> name name
> TGenderID TGender_id
> TGroupID TGroup_id
> TCountryID TCountry_id
>
> and for TCountry:
> name name
>
> So SQLObject removes the underline ok, but i get no id fields?
>
> BTW Thanks for helping Petr!
>

You can define (name) existing columns (names) explicitly. Even existing ID
column. I am using existing database as well and I am connecting to it this
way:

class Cenktgm01(SQLObject):
#    _connection = connection
    class sqlmeta:
        idType = str
        idName = 'OID$CENKTGM01'
    ktgi = StringCol(dbName="ktginterni")

BTW, you do not need to know about the id. SQLObject is treating id
automatically beneath the scene.

Sometime wiki can help: http://wiki.sqlobject.org/
I am experimenting with the SQLite inMemory database a lot as well (as shown
in following exampe).

from sqlobject import *
connectionInMemory = connectionForURI('sqlite:/:memory:')
class Person(SQLObject):
    _connection = connectionInMemory
    firstName = StringCol()
    middleInitial = StringCol(length=1, default=None)
    lastName = StringCol()

Regards

Petr

PS: please try to "reply to all", so other peeople in the mailing list can
help/comment as well.

Re: [SQLObject] Select does not work with some fieldname

[Petr J. <pet...@tp...>]

> please help.
> how do i this SQL
> SELECT *
> FROM `TAthlet`
> WHERE TCountry_id = 'AUT'
>
> The table "TAthlet" have this fields:
> TUnion_id
> id
> short
> name
> TGender_id
> TGoup_id
> TCountry_id
>
> This does not work:
> from sqlobject import *
> sqlhub.processConnection =
> connectionForURI('mysql://archer:xxxxxx@localhost/adb')
>
> class TAthlet(SQLObject):
>    class sqlmeta:
>        #if i dont do this i get a wrong tathlet table
>        table = "TAthlet"
>        fromDatabase = True
>
> TAthlet._connection.debug = True
> r = TAthlet.select(TAthlet.q.Tcountry_id=='AUT')
>
> The Error:
> AttributeError: TAthlet instance has no attribute 'Tcountry_id'
>

what about:
r = TAthlet.select(TAthlet.q.TcountryId=='AUT')

Petr

[SQLObject] Select does not work with some fieldname

[Wolfgang M. <wol...@gm...>]

Hi,
please help.
how do i this SQL
SELECT *
FROM `TAthlet`
WHERE TCountry_id = 'AUT'

The table "TAthlet" have this fields:
TUnion_id
id
short
name
TGender_id
TGoup_id
TCountry_id

This does not work:
from sqlobject import *
sqlhub.processConnection =
connectionForURI('mysql://archer:xxxxxx@localhost/adb')

class TAthlet(SQLObject):
    class sqlmeta:
        #if i dont do this i get a wrong tathlet table
        table = "TAthlet"
        fromDatabase = True

TAthlet._connection.debug = True
r = TAthlet.select(TAthlet.q.Tcountry_id=='AUT')

The Error:
AttributeError: TAthlet instance has no attribute 'Tcountry_id'

Thanks
Wolfgang Morawetz

Re: [SQLObject] Short vacation

[Daniel F. <fet...@go...>]

> Hi! I'm leaving the Net for a week. Will be back at 6th of November.

Have fun on your vacation Oleg!

Cheers,
Daniel

-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown

[SQLObject] Short vacation

[Oleg B. <ph...@ph...>]

Hi! I'm leaving the Net for a week. Will be back at 6th of November.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] SQLObject 0.12.0

[Oleg B. <ph...@ph...>]

On Tue, Oct 20, 2009 at 04:17:34PM +0200, Daniel Fetchinson wrote:
> > I'm pleased to announce version 0.12.0, the first stable release of
> > branch 0.12 of SQLObject.
> 
> Great, my app works with the new release without a problem.

   Thank you for the report!

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] SQLObject 0.12.0

[Daniel F. <fet...@go...>]

> I'm pleased to announce version 0.12.0, the first stable release of
> branch 0.12 of SQLObject.

Great, my app works with the new release without a problem.

Cheers,
Daniel


-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown

Re: [SQLObject] SQLObject 0.12.0

[Oleg B. <ph...@ph...>]

On Tue, Oct 20, 2009 at 06:02:47PM +0400, Oleg Broytman wrote:
> version 0.12.0, the first stable release of
> branch 0.12 of SQLObject.

   With this release branch 0.10 is retired.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] SQLObject 0.12.0

[Oleg B. <ph...@ph...>]

Hello!

I'm pleased to announce version 0.12.0, the first stable release of
branch 0.12 of SQLObject.


What is SQLObject
=================

SQLObject is an object-relational mapper.  Your database tables are described
as classes, and rows are instances of those classes.  SQLObject is meant to be
easy to use and quick to get started with.

SQLObject supports a number of backends: MySQL, PostgreSQL, SQLite,
Firebird, Sybase, MSSQL and MaxDB (also known as SAPDB).


Where is SQLObject
==================

Site:
http://sqlobject.org

Development:
http://sqlobject.org/devel/

Mailing list:
https://lists.sourceforge.net/mailman/listinfo/sqlobject-discuss

Archives:
http://news.gmane.org/gmane.comp.python.sqlobject

Download:
http://cheeseshop.python.org/pypi/SQLObject/0.12.0

News and changes:
http://sqlobject.org/News.html


What's New
==========

Features & Interface
--------------------

* .selectBy(), .deleteBy() and .by*() methods pass all values through
  .from_python(), not only unicode.

* The user can choose a DB API driver for SQLite by using a "backend"
  parameter in DB URI or SQLiteConnection that can be a comma-separated
  list of backend names. Possible backends are: "pysqlite2" (alias
  "sqlite2"), "sqlite3", "sqlite" (alias "sqlite1"). Default is to test
  pysqlite2, sqlite3 and sqlite in that order.

* The user can choose a DB API driver for PostgreSQL by using a "backend"
  parameter in DB URI or PostgresConnection that can be a comma-separated
  list of backend names. Possible backends are: "psycopg2", "psycopg1",
  "psycopg" (tries psycopg2 and psycopg1), "pygresql". Default is
  "psycopg".
  WARNING: API change! PostgresConnection's parameter
  "usePygresql" is now replaced with "backend=pygresql".

* The user can choose a DB API driver for MSSQL by using a "backend"
  parameter in DB URI or MSSQLConnection that can be a comma-separated list
  of backend names. Possible backends are: "adodb" (alias "adodbapi") and
  "pymssql". Default is to test adodbapi and pymssql in that order.

* alternateMethodName is defined for all unique fields, not only alternateID;
  this makes SQLObject create .by*() methods for all unique fields.

* SET client_encoding for PostgreSQL to the value of "charset" parameter
  in DB URI or PostgresConnection.

* TimestampCol() can be instantiated without any defaults, in this case
  default will be None (good default for TIMESTAMP columns in MySQL).

Small Features
--------------

* Imported DB API drivers are stored as connection instance variables, not
  in global variables; this allows to use different DB API drivers at the
  same time; for example, PySQLite2 and sqlite3.

* Removed all deprecated attribute and functions.

* Removed the last remained string exceptions.

For a more complete list, please see the news:
http://sqlobject.org/News.html

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] porting to python 3

[Oleg B. <ph...@ph...>]

On Sun, Oct 18, 2009 at 11:14:40AM +0200, Daniel Fetchinson wrote:
> I'm using 2to3 from trunk with python 2.6.1 and sqlobject 0.12b2 is
> 2to3-able. Lots of files need modification of course but apparently
> 2to3 could take care of all of them. I haven't run the test suite
> though, but this in itself is already promising.

   Thank you for the report!

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] porting to python 3

[Daniel F. <fet...@go...>]

>> Great! Does this mean that 0.12 aims to 2to3-able?
>
>    I don't know, I didn't switch even to 2.7 yet. Please report any
> problem.

I'm using 2to3 from trunk with python 2.6.1 and sqlobject 0.12b2 is
2to3-able. Lots of files need modification of course but apparently
2to3 could take care of all of them. I haven't run the test suite
though, but this in itself is already promising.

Cheers,
Daniel


-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown

[SQLObject] SQLObject 0.12.0b2

[Oleg B. <ph...@ph...>]

Hello!

I'm pleased to announce version 0.12.0b2, the second beta release of the
upcoming branch 0.12 of SQLObject.


What is SQLObject
=================

SQLObject is an object-relational mapper.  Your database tables are described
as classes, and rows are instances of those classes.  SQLObject is meant to be
easy to use and quick to get started with.

SQLObject supports a number of backends: MySQL, PostgreSQL, SQLite,
Firebird, Sybase, MSSQL and MaxDB (also known as SAPDB).


Where is SQLObject
==================

Site:
http://sqlobject.org

Development:
http://sqlobject.org/devel/

Mailing list:
https://lists.sourceforge.net/mailman/listinfo/sqlobject-discuss

Archives:
http://news.gmane.org/gmane.comp.python.sqlobject

Download:
http://cheeseshop.python.org/pypi/SQLObject/0.12.0b2

News and changes:
http://sqlobject.org/News.html


What's New
==========

News since 0.12.0b1
-------------------

* Fixed a bug in calling column.from_python in sqlbuilder.

For a more complete list, please see the news:
http://sqlobject.org/News.html

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] porting to python 3

[Oleg B. <ph...@ph...>]

On Wed, Oct 14, 2009 at 10:40:39PM +0200, Daniel Fetchinson wrote:
> Great! Does this mean that 0.12 aims to 2to3-able?

   I don't know, I didn't switch even to 2.7 yet. Please report any
problem.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] porting to python 3

[Daniel F. <fet...@go...>]

>> RefactoringTool: ### In file col.py ###
>> RefactoringTool: Line 357: could not convert: raise '@@: figure out
>> the exception for a delete'
>> RefactoringTool: Python 3 does not support string exceptions
>> RefactoringTool: Line 978: could not convert: raise "Enum type is not
>> supported"
>> RefactoringTool: Python 3 does not support string exceptions
>
>    Finally I fixed them in the trunk. Will be in version 0.12 soon.

Great! Does this mean that 0.12 aims to 2to3-able?

>> I'd say a simple Exception( '@@: figure out the exception for a delete' )
>> and Exception( 'Enum type is not supported' ) fix will be good enough.
>
>    I made the first one RuntimeError and the second one TypeError.

+1

>> I'll report on any further progress I make on porting sqlobject to python
>> 3.
>
>    Any progress?

Well, I didn't really do anything else than running the 2to3 script
whose output I copied in the original post...

Cheers,
Daniel

-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown

Re: [SQLObject] show/log query

[Oleg B. <ph...@ph...>]

On Tue, Oct 13, 2009 at 10:11:13AM +0200, Lutz Steinborn wrote:
> On Tue, 13 Oct 2009 11:45:36 +0400
> Oleg Broytman <ph...@ph...> wrote:
> > On Tue, Oct 13, 2009 at 08:52:48AM +0200, Lutz Steinborn wrote:
> > > is there a way to log the query that SQLObject generates ?
> > 
> >    Sure. You can turn on debug flag in DB URI or connection:
> But this gives me all queries. What I wan't is a possibility to log only on
> query.

   You can override .get() or .select() or any other method in the
SQLObject class you are interesting in, lower logging level, call the
overridden method and restore logging level. For example, configure
logging at the INFO level, configure SQLObject at the debug level;
debug logging is not emitted at the INFO level; in your overridden methods
lower logging level to DEBUG and then back to INFO.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] show/log query

[Lutz S. <l.s...@4c...>]

On Tue, 13 Oct 2009 11:45:36 +0400
Oleg Broytman <ph...@ph...> wrote:

> On Tue, Oct 13, 2009 at 08:52:48AM +0200, Lutz Steinborn wrote:
> > is there a way to log the query that SQLObject generates ?
> 
>    Sure. You can turn on debug flag in DB URI or connection:
But this gives me all queries. What I wan't is a possibility to log only on
query.
We have a lot of queries so its hard to find the right query in the logfiles.
Some times you will see only a query with special parameters. Its not only
interesting for logging its nice to have this for evidence.

Is where a way to do this ?


Regards 
Lutz

Re: [SQLObject] show/log query

[Oleg B. <ph...@ph...>]

On Tue, Oct 13, 2009 at 08:52:48AM +0200, Lutz Steinborn wrote:
> is there a way to log the query that SQLObject generates ?

   Sure. You can turn on debug flag in DB URI or connection:

__connection__ = 'sqlite:///path/to/db?debug=1'

   With debug turned on SQLObject prints queries to stdout, so you can
redirect the stdout of your program or sys.stdout. You can also redirect
this printing to stderr:

__connection__ = 'sqlite:///path/to/db?debug=1&loglevel=stderr'

   To use logging instead of printing you have to name both the logger and
the loglevel:

__connection__ = 'sqlite:///path/to/db?logger=sqllog&loglevel=debug'

   'logger' is any log name, see
http://sqlobject.org/SQLObject.html#dbconnection-database-connections for
details on configuring logging. 'loglevel' in this case must be a logging
method (debug, info, warning, error, critical, exception).

   SQLObject (DBConnection, actually) emits a number of logging messages,
see method printDebug at
http://svn.colorstudy.com/SQLObject/trunk/sqlobject/dbconnection.py

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] show/log query

[Simon C. <hod...@gm...>]

On Tue, Oct 13, 2009 at 8:52 AM, Lutz Steinborn <l.s...@4c...> wrote:
> is there a way to log the query that SQLObject generates ?

See http://www.sqlobject.org/SQLObject.html#dbconnection-database-connections.

[SQLObject] show/log query

[Lutz S. <l.s...@4c...>]

Good morning,

is there a way to log the query that SQLObject generates ?

example (maybe stupid):

result = MyTable.get(1)
logging.debug('%s' % result.print_sql_query())

or without execution

logging.debug('%s' % MyTable.get(1).return_only_query_as_string())


Kindly regards
Lutz

[SQLObject] SQLObject 0.12.0b1

[Oleg B. <ph...@ph...>]

Hello!

I'm pleased to announce version 0.12.0b1, the first beta release of the
upcoming branch 0.12 of SQLObject.


What is SQLObject
=================

SQLObject is an object-relational mapper.  Your database tables are described
as classes, and rows are instances of those classes.  SQLObject is meant to be
easy to use and quick to get started with.

SQLObject supports a number of backends: MySQL, PostgreSQL, SQLite,
Firebird, Sybase, MSSQL and MaxDB (also known as SAPDB).


Where is SQLObject
==================

Site:
http://sqlobject.org

Development:
http://sqlobject.org/devel/

Mailing list:
https://lists.sourceforge.net/mailman/listinfo/sqlobject-discuss

Archives:
http://news.gmane.org/gmane.comp.python.sqlobject

Download:
http://cheeseshop.python.org/pypi/SQLObject/0.12.0b1

News and changes:
http://sqlobject.org/News.html


What's New
==========

Features & Interface
--------------------

* .selectBy(), .deleteBy() and .by*() methods pass all values through
  .from_python(), not only unicode.

* The user can choose a DB API driver for SQLite by using a "backend"
  parameter in DB URI or SQLiteConnection that can be a comma-separated
  list of backend names. Possible backends are: "pysqlite2" (alias
  "sqlite2"), "sqlite3", "sqlite" (alias "sqlite1"). Default is to test
  pysqlite2, sqlite3 and sqlite in that order.

* The user can choose a DB API driver for PostgreSQL by using a "backend"
  parameter in DB URI or PostgresConnection that can be a comma-separated
  list of backend names. Possible backends are: "psycopg2", "psycopg1",
  "psycopg" (tries psycopg2 and psycopg1), "pygresql". Default is
  "psycopg".
  WARNING: API change! PostgresConnection's parameter
  "usePygresql" is now replaced with "backend=pygresql".

* The user can choose a DB API driver for MSSQL by using a "backend"
  parameter in DB URI or MSSQLConnection that can be a comma-separated list
  of backend names. Possible backends are: "adodb" (alias "adodbapi") and
  "pymssql". Default is to test adodbapi and pymssql in that order.

* alternateMethodName is defined for all unique fields, not only alternateID;
  this makes SQLObject create .by*() methods for all unique fields.

* SET client_encoding for PostgreSQL to the value of "charset" parameter
  in DB URI or PostgresConnection.

* TimestampCol() can be instantiated without any defaults, in this case
  default will be None (good default for TIMESTAMP columns in MySQL).

Small Features
--------------

* Imported DB API drivers are stored as connection instance variables, not
  in global variables; this allows to use different DB API drivers at the
  same time; for example, PySQLite2 and sqlite3.

* Removed all deprecated attribute and functions.

* Removed the last remained string exceptions.

For a more complete list, please see the news:
http://sqlobject.org/News.html

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] "Memory Leak" in sqlobject/cache.py

[Oleg B. <ph...@ph...>]

On Thu, Oct 08, 2009 at 11:30:51AM -0700, Jason Culverhouse wrote:
> I do 2 things in the cull method
> 1 - cull dead weak references out of the expiredCache.
> 2 - avoid placing dead weak references into the expiredCache

   Works fine. I applied it and committed in the revisions 4028-4030
(branches 0.10, 0.11 and the trunk). Thank you very much!

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] "Memory Leak" in sqlobject/cache.py

[Oleg B. <ph...@ph...>]

On Thu, Oct 08, 2009 at 11:30:51AM -0700, Jason Culverhouse wrote:
> I do 2 things in the cull method
> 1 - cull dead weak references out of the expiredCache.
> 2 - avoid placing dead weak references into the expiredCache

   Looks good. I will try it and apply it.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] "Memory Leak" in sqlobject/cache.py

[Jason C. <ja...@me...>]

Hi,
I'm using an older copy of SQLObject but sqlobject/cache.py hasn't  
really changed in 2 years, so this applies to current and past versions.

I operate a website that runs around 4million requests a day through  
an application using SQLObject.
At about mid day I end up with large amount of weakref objects and  
slowing response times.

Top 6 objects in memory:
2887448 <type 'weakref'>
  209963 <type 'dict'>
   68189 <type 'tuple'>
   45584 <type 'weakproxy'>
   45584 <class 'sqlobject.main.SQLObjectState'>
   45584 <class 'sqlobject.declarative.sqlmeta'>

These objects are from the CacheFactory.expiredCache dictionary that  
holds weak references to SQLObject instances by id.  Some of these  
dictionaries reach ~1.5 million keys, each SQLObject.get() request has  
to look into this dictionary which results in an access slowdown and  
memory growth. When the object behind the weak reference is collected,  
a weakref with a dead object is left in the cache, In my case this is  
a memory leak.

Any comments or improvements on this method are appreciated.

There are lots of ways to solve this problem but I think this is the  
least disruptive.   Weakref callbacks can't be used because the  
"release" callback happens in the cull method.
I do 2 things in the cull method
1 - cull dead weak references out of the expiredCache.
2 - avoid placing dead weak references into the expiredCache

     def cull(self):
         """
         Runs through the cache and expires objects.  E.g., if
         ``cullFraction`` is 3, then every third object is moved to
         the 'expired' (aka weakref) cache.
         """
         self.lock.acquire()
         try:
             #remove dead references from the expired cache
             keys = self.expiredCache.keys()
             for key in keys:
                 if self.expiredCache[key]() is None:
                     self.expiredCache.pop(key, None)

             keys = self.cache.keys()
             for i in xrange(self.cullOffset, len(keys),  
self.cullFraction):
                 id = keys[i]
                 # create a weakref, then remove from the cache
                 obj = ref(self.cache[id])
                 del self.cache[id]

                 #the object may have been gc'd when removed from the  
cache
                 #above, no need to place in expiredCache
                 if obj() is not None:
                     self.expiredCache[id] = obj
             # This offset tries to balance out which objects we
             # expire, so no object will just hang out in the cache
             # forever.
             self.cullOffset = (self.cullOffset + 1) % self.cullFraction
         finally:
             self.lock.release()

Thanks,
Jason

Re: [SQLObject] SQLObject

[Oleg B. <ph...@ph...>]

Hello!

   Please write to the mailing list.

On Wed, Oct 07, 2009 at 04:21:50PM +0100, Chris Wood wrote:
> My database already contains intermediate tables of many:many relationships,

   Many-to-many relations in SQLObject are created using two RelatedJoin's.
Explicit intermediate tables are possible, too:

http://sqlobject.org/FAQ.html#how-can-i-define-my-own-intermediate-table-in-my-many-to-many-relationship

   The example in the FAQ uses SQLRelatedJoin's. The difference is that
RelatedJoin attributes return a list of rows (SQLObject instances) where
SQLRelatedJoin attributes return a SelectResults instance which you can
iterate over.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] porting to python 3

[Oleg B. <ph...@ph...>]

On Mon, Jan 26, 2009 at 04:27:47PM -0800, Daniel Fetchinson wrote:
> RefactoringTool: ### In file col.py ###
> RefactoringTool: Line 357: could not convert: raise '@@: figure out
> the exception for a delete'
> RefactoringTool: Python 3 does not support string exceptions
> RefactoringTool: Line 978: could not convert: raise "Enum type is not supported"
> RefactoringTool: Python 3 does not support string exceptions

   Finally I fixed them in the trunk. Will be in version 0.12 soon.

> I'd say a simple Exception( '@@: figure out the exception for a delete' ) and
> Exception( 'Enum type is not supported' ) fix will be good enough.

   I made the first one RuntimeError and the second one TypeError.

> I'll report on any further progress I make on porting sqlobject to python 3.

   Any progress?

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.