sqlobject-discuss — All things SQLObject-related

[SQLObject] .set() fromPython

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

Method SQLObject.set(), lines 769-774:

        if self._SO_creating or self._lazyUpdate:
            for name, value in kw.items():
                fromPy = getattr(self, '_SO_fromPython_%s' % name, None)
                if fromPy:
                    kw[name] = value = fromPy(value, self._SO_validatorState)
                setattr(self, instanceName(name), value)

Method SQLObject.set(), lines 793-801:

            for name, value in kw.items():
                fromPython = getattr(self, '_SO_fromPython_%s' % name, None)
                if fromPython:
                    dbValue = fromPython(value, self._SO_validatorState)
                else:
                    dbValue = value
                toUpdate[name] = dbValue
                if self._cacheValues:
                    setattr(self, instanceName(name), value)

In the first snippet the value is converted from Python to db value and
assigned to the attribute named "name". In te second snippet in is not
converted; instead, new variable dbValue holds convertd value, and the
attribute "name" receives original, "pythonic" value.
   Isn't there a bug in the first snippet? Shouldn't it be

                    kw[name] = dbValue = fromPy(value, self._SO_validatorState)
                setattr(self, instanceName(name), value)

   ? I am not sure how special _SO_creating is...

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

Re: [SQLObject] SQLObject and Zope 2.7.3/2.8.0a1

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

On Fri, Nov 26, 2004 at 12:26:45PM +0100, Anders Bruun Olsen wrote:
> On Fri, Nov 26, 2004 at 12:32:31PM +0300, Oleg Broytmann wrote:
> > >     people = list()
> >    Why not
> >     people = []
> >    ?
> 
> Readability. I like list() because it seems just a tiny bit more clear
> than [], even though the end result is the same.

   Oops. My readability taste says opposite! :) I prefer [] to list().
The matter of taste...

> > > According to the errorlog the error occurs on the line where I do the
> > > for-loop, which means that the problem is the list(p) call. I am
> >    No. .select() cannot find a module or class.
> 
> Ahh.. and .select() returns None, and the list(p) then fails because
> list(None) does not make sense, since None is not iterable.

   Clear, but not exactly. .select() returns SelectResult, whose
__iter__ returns iterSelect of its dbconnection. It seems connection
object was broken or lost during refresh.

> > > guessing that this is due to persistence or something like that. I
> >    No, this is due to problems with registries. SQLObject registries are
> > not compatible with module reloading (refresh).
> 
> Hmm.. and I assume there is no easy way to fix that?

   Do not use refresh. Always do full Zope restart - stop and start it
anew.

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

Re: [SQLObject] SQLObject and Zope 2.7.3/2.8.0a1

[Anders B. O. <an...@br...>]

On Fri, Nov 26, 2004 at 12:32:31PM +0300, Oleg Broytmann wrote:
> >     people = list()
>    Why not
>     people = []
>    ?

Readability. I like list() because it seems just a tiny bit more clear
than [], even though the end result is the same.

> > According to the errorlog the error occurs on the line where I do the
> > for-loop, which means that the problem is the list(p) call. I am
>    No. .select() cannot find a module or class.

Ahh.. and .select() returns None, and the list(p) then fails because
list(None) does not make sense, since None is not iterable.

> > guessing that this is due to persistence or something like that. I
>    No, this is due to problems with registries. SQLObject registries are
> not compatible with module reloading (refresh).

Hmm.. and I assume there is no easy way to fix that?

> > Is it just 100% impossible to use SQLObject with Zope 2?
>    It is possible, at least in Zope3. Look at
> http://codespeak.net/z3/sqlos/ . May be you can port it to Zope2.

It looks really good, but I doubt that I can easily port it to Zope2,
and Zope3 looks like quite a different beast than Zope2.
I might end up having to just deal with SQL directly :(

-- 
Anders
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GCS/O d--@ s:+ a-- C++ UL+++$ P++ L+++ E- W+ N(+) o K? w O-- M- V
PS+ PE@ Y+ PGP+ t 5 X R+ tv+ b++ DI+++ D+ G e- h !r y?
------END GEEK CODE BLOCK------
PGPKey: http://pgp.mit.edu:11371/pks/lookup?op=get&search=0x8BFECB41

[SQLObject] Re: dbConn.cache.clear()

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

On Thu, Nov 18, 2004 at 04:00:58PM +0300, Oleg Broytmann wrote:
> Hello! cache.py:140:
> 
> -        self.cache.clear()
> +        if self.doCache:
> +            self.cache.clear()

   Applied. Close it on SF, please.

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

[SQLObject] Re: _LikeQuoted(string) + '%'

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

Hello!

   Please be warned :) everyone that I've got write access to the
Subversion repository, and I am going to fix bugs, push minor patches,
experiment with major design changes.

On Wed, Nov 24, 2004 at 03:54:08PM +0300, Oleg Broytmann wrote:
> On Tue, Nov 23, 2004 at 02:56:13PM +0300, Oleg Broytmann wrote:
> > >   File "sqlbuilder.py", line 549, in STARTSWITH
> > > TypeError: unsupported operand type(s) for +: 'instance' and 'str'
> > 
> > def STARTSWITH(expr, string):
> >     return SQLOp("LIKE", expr, _LikeQuoted(string) + '%')
> > 
> > _LikeQuoted is a class. How it is supoosed to work with "+"?
> >    (also in ENDSWITH and CONTAINSSTRING).
> 
>    Simple but probably dumb fix attached.

   The patch has been applied. Ian, please close it on SF.

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

Re: [SQLObject] SQLObject and Zope 2.7.3/2.8.0a1

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

On Fri, Nov 26, 2004 at 08:53:32AM +0100, Anders Bruun Olsen wrote:
> def listPeople(self):
>     """Return a list of people"""
>     p = Backend.People.select(Backend.People.q.entity == 0)
>     people = list()

   Why not
    people = []
   ?

>     for person in list(p):
>         people.append({"id": person.id, "name": person.name, "lived": person.lived})
>     return people
> 
> It is called from a ZPT file to get a list of authors (it's a book
> database) that can be displayed.
> 
> It works fine right after being added, but after refreshing the product
> or restarting the Zope server, I get this error whenever I try to view a
> page that calls that method:
> 
> Error Type: AttributeError
> Error Value: 'NoneType' object has no attribute 'iterSelect'
> 
> According to the errorlog the error occurs on the line where I do the
> for-loop, which means that the problem is the list(p) call. I am

   No. .select() cannot find a module or class.

> guessing that this is due to persistence or something like that. I

   No, this is due to problems with registries. SQLObject registries are
not compatible with module reloading (refresh).

> Is it just 100% impossible to use SQLObject with Zope 2?

   It is possible, at least in Zope3. Look at
http://codespeak.net/z3/sqlos/ . May be you can port it to Zope2.

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

[SQLObject] SQLObject and Zope 2.7.3/2.8.0a1

[Anders B. O. <an...@br...>]

Hi,

I realize that this question may have been brought up before, but I
haven't been able to find a good answer in the archives.

I am trying to make a Zope product that uses SQLObject to talk to a
MySQL database. In my main class (SimpleItem) I have a method called
listPeople() which looks like this:

def listPeople(self):
    """Return a list of people"""
    p = Backend.People.select(Backend.People.q.entity == 0)
    people = list()
    for person in list(p):
        people.append({"id": person.id, "name": person.name, "lived": person.lived})
    return people

It is called from a ZPT file to get a list of authors (it's a book
database) that can be displayed.

It works fine right after being added, but after refreshing the product
or restarting the Zope server, I get this error whenever I try to view a
page that calls that method:

Error Type: AttributeError
Error Value: 'NoneType' object has no attribute 'iterSelect'

According to the errorlog the error occurs on the line where I do the
for-loop, which means that the problem is the list(p) call. I am
guessing that this is due to persistence or something like that. I
thought it might be something related to cache, so I tried setting
_cacheValues = False on the Backend.People class, but that didn't change
anything.

Can anybody help?
Is it just 100% impossible to use SQLObject with Zope 2?
I have tried this with both Zope 2.7.3 and 2.8.0a1 with the same
results.

-- 
Anders
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GCS/O d--@ s:+ a-- C++ UL+++$ P++ L+++ E- W+ N(+) o K? w O-- M- V
PS+ PE@ Y+ PGP+ t 5 X R+ tv+ b++ DI+++ D+ G e- h !r y?
------END GEEK CODE BLOCK------
PGPKey: http://pgp.mit.edu:11371/pks/lookup?op=get&search=0x8BFECB41

[SQLObject] Re: _LikeQuoted(string) + '%'

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

On Tue, Nov 23, 2004 at 02:56:13PM +0300, Oleg Broytmann wrote:
> >   File "sqlbuilder.py", line 549, in STARTSWITH
> > TypeError: unsupported operand type(s) for +: 'instance' and 'str'
> 
> def STARTSWITH(expr, string):
>     return SQLOp("LIKE", expr, _LikeQuoted(string) + '%')
> 
> _LikeQuoted is a class. How it is supoosed to work with "+"?
>    (also in ENDSWITH and CONTAINSSTRING).

   Simple but probably dumb fix attached.

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

[SQLObject] Re: DB API

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

On Wed, Nov 24, 2004 at 02:37:46PM +0300, Oleg Broytmann wrote:
>    a module from Dennis Otkidach - here
> http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/278612

   Dennis' module does not use any style at all - instead, it just
construct templates from a list of strings and parameters.

>    def _executeRetry(self, conn, cursor, query):
>       return cursor.execute(query.template, query.paramlist)

   Of course I meant

return cursor.execute(self.convert_to_dbapi_style(query.template), query.paramlist)

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

[SQLObject] Re: DB API

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

On Tue, Nov 23, 2004 at 06:36:28PM +0300, Oleg Broytmann wrote:
> On Mon, Nov 22, 2004 at 03:14:41PM +0300, Oleg Broytmann wrote:
> >    Why use sqlStringReplace and _[lL]ikeQuoted?...
> >    ...use format/pyformat/etc paramstyle from DB API 2.
> 
>    cursor.execute("INSERT INTO atable (afield) VALUES (:1)", ("astring",))

   So, my plan is as follows. First, SQLObject should adopt a module to
convert between different paramstyles, or grow its own. Available modules
are:
   a module from Dennis Otkidach - here
http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/278612
   or there:
http://cvs.sourceforge.net/viewcvs.py/ppa/misc/DBSingleStyle.py?rev=HEAD&content-type=text/vnd.viewcvs-markup
   The license is pythonic. Another module is paramstyles.py from PyDAL
(pydal.sf.net). The licesne is BSD-like.

   Then we are to choose a single paramstyle for the entire SQLObject. I
personnaly prefer named and numeric.

   And finally SQLObject will be rewritten to generate templates (using
our chosen paramstyle) instead of query strings. I am not sure yet how
to do it right. I think about the following path. Stop generating
strings. Queries have to be objects that contains a template and a list
of parameters:

class SQLQuery:
   def __init__(self, template, paramlist=None):
      self.template = template
      self.paramlist = paramlist

   A query can be executed by a DB connection: dbConn.query(SQLQuery(t, p)).
The _executeRetry() method will be implemented as follows:

   def _executeRetry(self, conn, cursor, query):
      return cursor.execute(query.template, query.paramlist)

   All __sqlrepr__ ethods should return SQLQuery instances, not strings.
Something like this (very simplified):

class Insert:
   def __init__(self, table, valueDict):
      self.table = table
      self.valueDict = valueDict

   def __sqlrepr__(self): # no db parameter required
      fields = self.valueDict.items()
      names = ", ".join([f[0] for f in fields])
      names_template = ", ".join([":%s" % f for f in names])
      template = "INSERT INTO %s (%s) VALUES (%s)" % (self.table, names, names_template)
      return SQLQuery(template, self.valueDict)

   self.valueDict is used for named paramstyle. For numeric paramstyle
it is

   def __sqlrepr__(self):
      fields = self.valueDict.items()
      names = ", ".join([f[0] for f in fields])
      names_template = ", ".join([":%d" % i for i in range(len(names))])
      values = [f[1] for f in fields]
      template = "INSERT INTO %s (%s) VALUES (%s)" % (self.table, names, names_template)
      return SQLQuery(template, values)

   Any words on this? Ian? Anyone?

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

Re: [SQLObject] Re: DB API

[Max I. <ma...@uc...>]

Oleg Broytmann wrote:

>    But this, probably, requires some architectural changes in the
> SQLObject.
>    Well, what do you think of this?

I'd say it's a good idea.

If Ian won't object to the idea per se, you could support your words 
with a patch. ;-)

[SQLObject] "SQLCall instance" instead of actual values

[Justin A. <JA...@ua...>]

I just reworked an app using sqlobject-0.6 to use a single connection to
the backend, and to hand out objects converted to dictionaries using
xmlrpc.  This was mostly to enable caching on the db and to simplify a
few things.

but now, after the first few requests I start to get errors like:

File "/tmp/sqlscanner/scandb.py", line 361, in scanasdict
    st=int(s.starttime)
exceptions.AttributeError: SQLCall instance has no attribute '__int__'

where that function is:
def scanasdict(s):
...
    if s.starttime:
        st=int(s.starttime)
    else :
        st=0
...
    scan={
        "id": s.id,
        "ip": s.ip,
...
        "starttime": st,
...
    return scan

and starttime is simply a DateTimeCol that can be NULL.

What could be causing a SQLCall instance to be returned instead of a
date, and how can I fix that?

-- 
-- Justin Azoff
-- Network Performance Analyst

[SQLObject] Re: DB API

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

On Mon, Nov 22, 2004 at 03:14:41PM +0300, Oleg Broytmann wrote:
>    Why use sqlStringReplace and _[lL]ikeQuoted?...
>    ...use format/pyformat/etc paramstyle from DB API 2.

   Let's me elaborate. Currently SQLObject generates
SELECT/INSERT/UPDATE/etc queries as strings. There is a number of
problems with this approach.

   Problem number one. SQLObject tries to quote/escape strings using its
internal knowledge of quoting rules. This is very fragile as SQLObject
has to know quoting rules for all supported database.
   It is possible to releave the pain, at least partially, by using
quote/escape functions from low-level modules. These functions are not
part of DB API (more on this later), but most modules provide them. Just
import them and use them.
   from psycopg import QuotedString
or
   from MySQLdb import escape_string
Now instead of encapsulating knowledge of each database SQLObject will
encapsulate knowledge of each DB API driver.

   But still this is not a complete solution. Some databases do not
allow to quote special characters at all. Just do not allow. There is no
way one can generate a correct query string that incorporates a special
character. I heard that Sybase/MSSQL and Oracle are of this class. Their
DB API drivers do not have quoting/escaping functions.
   There is only one way a program can send a special character to these
databases - using query parameters:
   cursor.execute("INSERT INTO atable (afield) VALUES (:1)", ("astring",))

   This is the best way for SQLObject 'cause after implementing this
SQLObject will not need to know any quoting rules, and will be ready to
support more databases.
   Different paramstyles will be handled by any paramstyle conversion
modules - there is a number of them on the Net.

   But this, probably, requires some architectural changes in the
SQLObject.
   Well, what do you think of this?

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

[SQLObject] _LikeQuoted(string) + '%'

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

Hi!

>   File "sqlbuilder.py", line 549, in STARTSWITH
> TypeError: unsupported operand type(s) for +: 'instance' and 'str'

def STARTSWITH(expr, string):
    return SQLOp("LIKE", expr, _LikeQuoted(string) + '%')

_LikeQuoted is a class. How it is supoosed to work with "+"?
   (also in ENDSWITH and CONTAINSSTRING).

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

[SQLObject] DB API

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

Helo, Ian!

   Why use sqlStringReplace and _[lL]ikeQuoted? Why not import and use
quoting/escaping functions from low-level DB API modules? At least where
they are avalable at all...

   Why not import DB API exceptions from those modules and reexport them
as SQLObject exceptions?

   Or use format/pyformat/etc paramstyle from DB API 2.

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

[SQLObject] Exceptions are unpredictable?

[Leif K-B. <eu...@ec...>]

I'm writing an application with SQLObject that uses EAFP (Easier to Ask 
for Forgiveness than Permission) style, as does most Python code. 
Unfortunately, the exceptions SQLObject raises seem to change depending 
on the database being used, which makes doing this in a cross-database 
way impossible. Would I be better off using LBYL  (Look Before You Leap) 
style with code that uses SQLObject?

Re: [SQLObject] how to run a plain query?

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

On Thu, Nov 18, 2004 at 04:28:00PM -0500, Martin d'Anjou wrote:
> How do I run a plain query?
> 
> conn = connectionForURI('mysql://user:passwd@server/db')
> conn.query('select count(*) from mytable;')
> 
> Where is the count value goint? How do I get it?

   count =  conn.queryOne('select count(*) from mytable')
or
   count, size =  conn.queryOne('select count(*), sum(size) from mytable')

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

[SQLObject] how to run a plain query?

[Martin d'A. <Mar...@s2...>]

How do I run a plain query?

conn = connectionForURI('mysql://user:passwd@server/db')
conn.query('select count(*) from mytable;')

Where is the count value goint? How do I get it?

Thanks,
Martin

[SQLObject] dbConn.cache.clear()

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

Hello! cache.py:140:

-        self.cache.clear()
+        if self.doCache:
+            self.cache.clear()

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

Re: [SQLObject] Handling upgrades involving database changes?

[Andy T. <an...@gm...>]

On Wed, 17 Nov 2004 04:23:31 -0500, Leif K-Brooks <eu...@ec...> wrote:
> I'm developing a web web application project which uses SQLObject for
> the database connection. Everything's working great on my test system;
> however, looking into the future, I can see that my project will have
> updates released at some point, some of which will probably involve
> changes to the database schema.
> 
> If everyone upgraded straight from version A to version B, it would be
> very simple: release a script that alters the database along with every
> version and have the users run it. Unfortunately, some people will end
> up going from version A straight to version D, which would end up giving
> them a messed up database schema.
> 
> How do other people handle upgrades to database schemas gracefully?
> 
> 

With each release include a script which migrates the database from
the previous version to the current version. If people need/want to
skip a few intermediate releases they will still have to run all of
the update scripts. That saves you writing several different upgrade
scripts for each release.

Regards,
Andy
-- 
From the desk of Andrew J Todd esq

[SQLObject] Re: MySQLConnection is deprecated

[Derrick H. <dma...@dm...>]

On Mon, Nov 15, 2004 at 10:45:08AM -0600, Ian Bicking wrote:
| Brian Beck wrote:

| >On Win32 for SQLite, I can only use sqlite:/file.db or sqlite:///file.db=
,=20

| >AND
| >this creates the file in the ROOT directory of whatever drive the script=
 is
| >running from, NOT the path of the script!  This is highly undesirable.
|=20
| Hmm... unfortunately I can't think of any way of indicating the current=
=20
| directory in a fully qualified URI.  Does anyone have ideas?=20
| sqlite:/:pwd:/file.db ?

Solution #1:
    Use the cygwin python.exe and write the URI as
        sqlite:/cygdrive/m/path/to/whatever/file.db

Solution #2:
    Devise a way to embed the drive letter in the URI.

    As a first approach I would look for any documentation or examples
    that show how msvcrt.dll's fopen() parses out drive letters, then
    document using that same notation.

    (this is the same way forward slashes are handled -- Tim Peters
    once revealed on c.l.py that Windows' fopen() accepts forward
    slashes even though command.com and cmd.exe don't)


Presumably in an application you will ask the user where the database
should be stored instead of assuming it will reside in the
application's own directory.

HTH,
-D

--=20
Microsoft is to operating systems & security ....
                                    .... what McDonald's is to gourmet cook=
ing
=20
www: http://dman13.dyndns.org/~dman/            jabber: dman@dman13.dyndns.=
org

Re: [SQLObject] tryGet()

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

Oleg Broytmann wrote:
> cache.py:48-49:
> 
>     def tryGet(self, id):
>         return self.cache.get(id)
> 
> There is no self.cache, because self.doCache is False. I don't know what
> to return. None?

Yes, None would be best.  Well... actually it was also not paying 
attention to expiredCache, which it should have.

I've applied fixes for this and allIDs

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

[SQLObject] tryGet()

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

cache.py:48-49:

    def tryGet(self, id):
        return self.cache.get(id)

There is no self.cache, because self.doCache is False. I don't know what
to return. None?

Traceback (most recent call last):
  File "/usr/local/lib/python2.3/site-packages/sqlobject/dbconnection.py", line 633, in rollback
    inst = subCache.tryGet(id)
  File "cache.py", line 49, in tryGet
AttributeError: 'CacheFactory' object has no attribute 'cache'

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

[SQLObject] allIDs()

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

cache.py:167:

        for id, value in self.expiredCache:

but self.expiredCache is a dictionary. The line should be

        for id, value in self.expiredCache.items():

or

        for id in self.expiredCache:
            value = self.expiredCache[id]

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

Re: [SQLObject] Re: Update(): AssertionError: Database None unknown

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

Oleg Broytmann wrote:
>>  File "/usr/local/lib/python2.3/site-packages/sqlobject/sqlbuilder.py", line 480, in __sqlrepr__
>>    update += " WHERE %s" % repr(self.whereClause)
> 
> 
>    It must be
> 
>             update += " WHERE %s" % sqlrepr(self.whereClause, db)

Thanks, fixed in svn.

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