sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] Manage objects in different databases under the same program instance

[Juan M. <vic...@gm...>]

On Sat, Feb 6, 2010 at 7:56 AM, Petr Jakeš <pet...@tp...> wrote:

> >
> > I don't think that serves the purpose. The program is supposed to let the
> user
> > scan whatever structure he/she wants, and this way i'm limiting that, and
> it
> > is also not scalable.
> >
> > Thanks anyway
> >
>
> Than I do not understand your needs. Maybe some simple example or
> explanation???
>

Sorry for the double post, missed the list address on my previous message =(

The program is a file and directory indexer. Its aim is to provide a sort of
catalog, so you can know what you saved where (for example, on a CD, DVD,
external HD, etc). This catalog is a representation of the file & directory
structure that was scanned.

I was originally using pickle for saving this catalog, but since I had
performance problems on huge directories (lots of files), I moved to using
sqlite (through sqlobject).

The program can currently handle one catalog at a time (which means that it
doesn't keep references in memory to more than one DB). Trying to index a
new directory simply dismisses the previous one that was indexed. What I'm
aiming for is to be able to scan several directories separately (of course,
I have to refactor a lot), and keep in memory several of these references
(thus, handling an X quantity of DBs, being X a number that is up to the
user and which I can't know).

As an example: currently when you scan a directory, there are two treeviews
which are populated: the left one with the entire directory structure (only
dirs), and the right one with the contents of the currently selected dir
(much like windows explorer). If you scan another dir, the treeviews are
wiped and all references to the previous scan are wiped too. My aim is to,
instead of wiping all that, keep it and open a new tab (with another two
treeviews), show the new catalog in that new tab, and be able to switch
between tabs and vieweing both catalogs at the same time.T

Re: [SQLObject] Manage objects in different databases under the same program instance

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

>
> I don't think that serves the purpose. The program is supposed to let the user
> scan whatever structure he/she wants, and this way i'm limiting that, and it
> is also not scalable.
>
> Thanks anyway
>

Than I do not understand your needs. Maybe some simple example or explanation???

Re: [SQLObject] Manage objects in different databases under the same program instance

[Juan M. S. <vic...@gm...>]

On Saturday 06 February 2010 06:54:31 Petr Jakeš wrote:
> > In order to create the DB objects inside the approppriate DB, AFAIK I can
> > pass a connection parameter in the class creation, and all will be fine.
> > But how do I go about doing selects and obtaining results from a specific
> > DB? I may have two DBs open, and each with its file & dir structure
> > inside. If I want to do, say, File.select(), how can I specify that I
> > want results from only one of the DBs?
> 
> What about:
> 
> from sqlobject import *
> 
> connection01 = connectionForURI('sqlite:/:memory:')
> connection02 = connectionForURI('sqlite:/:memory:')
> 
> class File01(SQLObject):
>     _connection = connection01
> 
> class File02(SQLObject):
>     _connection = connection02
> 

I don't think that serves the purpose. The program is supposed to let the user 
scan whatever structure he/she wants, and this way i'm limiting that, and it 
is also not scalable.

Thanks anyway

Re: [SQLObject] Manage objects in different databases under the same program instance

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

> In order to create the DB objects inside the approppriate DB, AFAIK I can pass
> a connection parameter in the class creation, and all will be fine. But how do
> I go about doing selects and obtaining results from a specific DB? I may have
> two DBs open, and each with its file & dir structure inside. If I want to do,
> say, File.select(), how can I specify that I want results from only one of the
> DBs?
>

What about:

from sqlobject import *

connection01 = connectionForURI('sqlite:/:memory:')
connection02 = connectionForURI('sqlite:/:memory:')

class File01(SQLObject):
    _connection = connection01

class File02(SQLObject):
    _connection = connection02

[SQLObject] Manage objects in different databases under the same program instance

[Juan M. S. <vic...@gm...>]

Hello again everybody,

As I mentioned in a post some days ago, I'm writing a program that scans a 
file & directory structure as part of an indexing process. My next development 
target is to make it able to manage several of these structures at the same 
time (for example, opening each one in a different tab). Maybe even being able 
to scan different structures at the same time (through threads, of course :))

Since I don't like huge DBs, each structure is saved to its own sqlite db upon 
scanning. Until now I've been using sqlhub.processConnection in order to 
create the classes into their appropriate DBs, but I believe that 
sqlhub.processConnection forces the assigned connection to be used througout 
the entire program instance, so if in another class (after assigning the 
connection) I create an instance of a SQLObject, it goes to that DB, no matter 
if the connection assigment was done elsewhere.

In order to create the DB objects inside the approppriate DB, AFAIK I can pass 
a connection parameter in the class creation, and all will be fine. But how do 
I go about doing selects and obtaining results from a specific DB? I may have 
two DBs open, and each with its file & dir structure inside. If I want to do, 
say, File.select(), how can I specify that I want results from only one of the 
DBs?

Thanks

Juan Manuel

Re: [SQLObject] how to get id (primary key) from database

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

>   Yes, and idMessage/id_message must not be there. It is there because of
> a bug. So help me to chase the bug.
>
> SHOW COLUMNS FROM ps_message_readed
>
>   (just run the query)

I see, I was trying to get it from within the Python.

Result attached.

Re: [SQLObject] how to get id (primary key) from database

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

On Sat, Feb 06, 2010 at 12:04:27AM +0100, Petr Jake?? wrote:
> I have a function for something like this:
> def printColumnNames(aTable):
>     print "col.origName", "\t", "col.dbName"
>     for col in aTable.sqlmeta.columnList:
>         print col.origName, "\t", col.dbName
> 
> 
> col.origName     col.dbName
> idMessage        id_message
> idEmployee       id_employee
> dateAdd            date_add
> 
> Is that what you mean?

   Yes, and idMessage/id_message must not be there. It is there because of
a bug. So help me to chase the bug.

SHOW COLUMNS FROM ps_message_readed

   (just run the query)

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

Re: [SQLObject] how to get id (primary key) from database

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

  Can you show the results from the query
>
> SHOW COLUMNS FROM ps_message_readed
>   ?
>
>   I suspect column names are in uppercase there, and fromDatabase
> machinery has problems finding the id column. Because of the column
> 'id_message' is used twice - once from id and once from the list of
> columns.

I have a function for something like this:
def printColumnNames(aTable):
    print "col.origName", "\t", "col.dbName"
    for col in aTable.sqlmeta.columnList:
        print col.origName, "\t", col.dbName


col.origName     col.dbName
idMessage        id_message
idEmployee       id_employee
dateAdd            date_add

Is that what you mean?

Re: [SQLObject] [NOT REALLY SOLVED]: how to insert id (primary key) manually to the database?

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

On 5 February 2010 23:30, Petr Jakeš <pet...@tp...> wrote:

> Sorry to bother the list. My hands were to fast and my brain to slow (as
> usually :D )
>
>> PsMessageReaded(id = myId, idEmployee=3, dateAdd =  tStamp)
>>
>
> This code works:
> PsMessageReaded(idMessage = myId, idEmployee=3, dateAdd =  tStamp)
>
> I am able to write the values to the database, but I am getting following
error:

  File
"/usr/lib/python2.5/site-packages/SQLObject-0.10.1-py2.5.egg/sqlobject/main.py",
line 1247, in _SO_finishCreate
    self._init(id)
  File
"/usr/lib/python2.5/site-packages/SQLObject-0.10.1-py2.5.egg/sqlobject/main.py",
line 923, in _init
    raise SQLObjectNotFound, "The object %s by the ID %s does not exist" %
(self.__class__.__name__, self.id)
sqlobject.main.SQLObjectNotFound: The object PsMessageReaded by the ID 0
does not exist


I think this is because SQLObject is trying to read-back the values for the
new instance, but is confused by the different ID naming.

Of course I can live with this (catching exception).

Regards

Petr

Re: [SQLObject] [SOLVED]: how to insert id (primary key) manually to the database?

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

On Fri, Feb 05, 2010 at 11:30:15PM +0100, Petr Jake?? wrote:
> This code works:
> PsMessageReaded(idMessage = myId, idEmployee=3, dateAdd =  tStamp)

   The code hides the error that 'idMessage' is in the list of normal
columns. It is a workaround, but correct code must be

   PsMessageReaded(id=myId, idEmployee=3, dateAdd=tStamp)

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

Re: [SQLObject] how to get id (primary key) from database

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

On Fri, Feb 05, 2010 at 11:22:29PM +0100, Petr Jake?? wrote:
> PsMessageReaded(id = myId, idEmployee=3, dateAdd =  tStamp)
> 
> When I am trying it, I am getting following error:
> 
>     self._executeRetry(conn, c, q)
>   File
> "/usr/lib/python2.5/site-packages/SQLObject-0.10.1-py2.5.egg/sqlobject/mysql/mysqlconnection.py",
> line 129, in _executeRetry
>     raise ProgrammingError(ErrorMessage(e))
> sqlobject.dberrors.ProgrammingError: Column 'id_message' specified twice

   Can you show the results from the query
SHOW COLUMNS FROM ps_message_readed
   ?

   I suspect column names are in uppercase there, and fromDatabase
machinery has problems finding the id column. Because of the column
'id_message' is used twice - once from id and once from the list of
columns.

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

[SQLObject] [SOLVED]: how to insert id (primary key) manually to the database?

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

Sorry to bother the list. My hands were to fast and my brain to slow (as
usually :D )

> PsMessageReaded(id = myId, idEmployee=3, dateAdd =  tStamp)
>

This code works:
PsMessageReaded(idMessage = myId, idEmployee=3, dateAdd =  tStamp)

[SQLObject] how to insert id (primary key) manually to the database?

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

Hi,
I am working with an existing database (MySQL - Prestashop).
On some tables, auto-incrementation for primary key is not used. And I am
not able to set it, because it goes against the logic of the application. I
know, wired, but...

Is it possible to force SQLObject to insert id manually?

PsMessageReaded(id = myId, idEmployee=3, dateAdd =  tStamp)

When I am trying it, I am getting following error:

    self._executeRetry(conn, c, q)
  File
"/usr/lib/python2.5/site-packages/SQLObject-0.10.1-py2.5.egg/sqlobject/mysql/mysqlconnection.py",
line 129, in _executeRetry
    raise ProgrammingError(ErrorMessage(e))
sqlobject.dberrors.ProgrammingError: Column 'id_message' specified twice



id_message is the name of the primary key column in the table:

class PsMessageReaded(SQLObject):
    _connection = connection
    class sqlmeta:
        idName = 'id_message'
        fromDatabase = True

Thanks for your comments

Petr Jakes

Re: [SQLObject] Instance pickling (was: memcache)

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

On Mon, Feb 01, 2010 at 11:43:09PM +0300, Oleg Broytman wrote:
>    I am going to do it in steps. The first step - to make SQLObject
> instances pickleable.

   Committed to the trunk in the revision 4099.

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

Re: [SQLObject] how to set dbEncoding for the whole table/database

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

On Fri, Feb 05, 2010 at 11:49:05AM +0100, Petr Jake?? wrote:
> Hmmm... when the connection is set as:
> connection = connectionForURI("mysql://
> prestashop:prestashop@127.0.0.1:3306/prestashop?use_unicode=1&charset=utf8")
> 
> the encoding setting for each column is not necessary.

   Perhaps because UnicodeCol has a default dbEncoding=utf-8.

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

Re: [SQLObject] how to set dbEncoding for the whole table/database

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

>   Well, I added the idea to my TODO.
>
> Hmmm... when the connection is set as:
connection = connectionForURI("mysql://
prestashop:prestashop@127.0.0.1:3306/prestashop?use_unicode=1&charset=utf8")

the encoding setting for each column is not necessary.

Re: [SQLObject] how to set dbEncoding for the whole table/database

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

On Thu, Feb 04, 2010 at 10:15:27PM +0100, Petr Jake?? wrote:
> >   To define an encoding for all
> > UnicodeCol'umns at once?
> >
> Yes, that was the main idea. When I am working with existing database, I do
> not declare columns manually (I am using fromDatabase = True).

   Well, I added the idea to my TODO.

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

Re: [SQLObject] utf-8 encoding troubles

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

On Fri, Feb 05, 2010 at 03:48:15AM +0100, Petr Jake?? wrote:
> MySQLConnection = mysql.builder()

   You don't need that.

> connection = connectionForURI("mysql://
> prestashop:prestashop@127.0.0.1:3306/prestashop")
> =============================================================================
> 
> import MySQLdb
> 
> conn = MySQLdb.connect (host = "localhost",
>                         user = "prestashop",
>                         passwd = "prestashop",
>                         db = "prestashop",
>                         use_unicode = True,
>                         charset = "utf8")

   You didn't use 'use_unicode' and 'charset' in the SQLObject's DB
URI - my be this is the cause? Try

connection = connectionForURI("mysql://prestashop:prestashop@127.0.0.1:3306/prestashop?use_unicode=1&charset=utf8")

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

[SQLObject] Mail

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

Hello. My provider is upgrading its mail system. Because of this I don't
receive your messages as quick as they used to be. I hope all messages will
be delivered sooner or later, and no later than Monday.

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

Re: [SQLObject] utf-8 encoding troubles

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

SOLVED:


> MySQLConnection = mysql.builder()
> connection = connectionForURI("mysql://
> prestashop:prestashop@127.0.0.1:3306/prestashop")
>
> =============================================================================


Finally:
connection = connectionForURI('mysql://
prestashop:prestashop@127.0.0.1:3306/prestashop?use_unicode=1&charset=utf8')

Did the trick.

Regards Petr

[SQLObject] utf-8 encoding troubles

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

I am having troubles with some unicode utf8 characters. They are not
displayed properly.
I was trying a lot off different settings  on the connection part of the
SQLObject scrpit (see below), but not success till now.

============== this code DOES NOT bring the UTF -8 code from the database
==============
from sqlobject import *
mport MySQLdb.converters

def _mysql_timestamp_converter(raw):
         """Convert a MySQL TIMESTAMP to a floating point number
representing
         the seconds since the Un*x Epoch. It uses custom code the input
seems
         to be the new (MySQL 4.1+) timestamp format, otherwise code from
the
         MySQLdb module is used."""
         if raw[4] == '-':
             return time.mktime(time.strptime(raw, '%Y-%m-%d %H:%M:%S'))
         else:
             return MySQLdb.converters.mysql_timestamp_converter(raw)

conversions = MySQLdb.converters.conversions.copy()
conversions[MySQLdb.constants.FIELD_TYPE.TIMESTAMP] =
_mysql_timestamp_converter

MySQLConnection = mysql.builder()
connection = connectionForURI("mysql://
prestashop:prestashop@127.0.0.1:3306/prestashop")
=============================================================================

Finaly I did try the direct use of the MySQLdb and it worsk OK! But I can
not see why!!!

============== this code brings the UTF-8 code from the database
==============

import MySQLdb

conn = MySQLdb.connect (host = "localhost",
                        user = "prestashop",
                        passwd = "prestashop",
                        db =
"prestashop",
                        use_unicode = True,
                        charset = "utf8")

cursor = conn.cursor ()
cursor.execute ('SELECT * FROM prestashop.ps_address')


Thanks for your comments

Petr Jakeš

Re: [SQLObject] how to set dbEncoding for the whole table/database

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

>   There is no currently. What do you want? To define an encoding for all
> UnicodeCol'umns at once?
>
>
Yes, that was the main idea. When I am working with existing database, I do
not declare columns manually (I am using fromDatabase = True).

But I can define encoding for each row manually of course.

Re: [SQLObject] how to set dbEncoding for the whole table/database

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

On Thu, Feb 04, 2010 at 09:46:16PM +0100, Petr Jake?? wrote:
> I would like to know, if it is possible to
> set the encoding for the whole table at once (or for the
> connection/database) instead to define it for each column.

   There is no currently. What do you want? To define an encoding for all
UnicodeCol'umns at once?

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

Re: [SQLObject] per-instance connection

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

On Thu, Feb 04, 2010 at 10:45:38AM -1000, John Bresnahan wrote:
> Thanks much!  Is there a way to change the connection associated with an object you have already 
> acquired via select?

   Don't do that. If you want a row to be associated with a connection get
or create it via that connection. This is especially important for
transactions and rows locked using SELECT FOR UPDATE.

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

[SQLObject] how to set dbEncoding for the whole table/database, not for the row?

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

Hi,
I know below code example works. I would like to know, if it is possible to
set the encoding for the whole table at once (or for the
connection/database) instead to define it for each column.

Petr

====== code example ============

class Customer(SQLObject):
    _connection = connection
    class sqlmeta:
        idName = 'id_customer'
#        fromDatabase = True
    orders = MultipleJoin('Orders', joinColumn = "id_customer")
    lastname = UnicodeCol(dbEncoding = "1250")
    firstname = UnicodeCol(dbEncoding = "1250")