SQLObject

Hi,

Is it possible to connect to 2 databases each of which have a common
table name??

Here is my test:

include1.py
------------------------
from sqlobject import *

class Test(SQLObject):
      desc=StringCol()
------------------------

include2.py
------------------------
from sqlobject import *

class Test(SQLObject):
      desc=StringCol()
------------------------



test.py
------------------------
from sqlobject import *
import os

import import1
import import2

if os.path.exists(\'c:\\\\test1.db\'):
      os.unlink(\'c:\\\\test1.db\')
con1=connectionForURI(\'sqlite:/d|/tmp/test1.db\')

if os.path.exists(\'c:\\\\test2.db\'):
      os.unlink(\'d:\\\\test2.db\')
con2=connectionForURI(\'sqlite:/d|/tmp/test2.db\')

import1.Test.createTable(connection=con1)
import2.Test.createTable(connection=con2)

con1.close()
con2.close()
------------------------


Thanks,

Ray Smith
http://RaymondSmith.com

Sorry for the extra \\\"\\\\\\\"\\\'s.

Attaching programs incase anyone wants to run them.

Thanks,

Ray

On Wed, Aug 23, 2006 at 01:05:29PM -0700, Monty Taylor wrote:
> So I think it's what people expect to have the files read. Whether we
> want to add a section [sqlobject] or [sqlalchemy] by default or not,
> or just leave it to read the [client] sections is another question...

   By default it should be None. Don't forget to document the option in
your patch.

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

On Thu, Aug 24, 2006 at 09:30:42AM +1000, Ray Smith wrote:
> Is it possible to connect to 2 databases each of which have a common
> table name??

   It is. I have a program that copies a database from Postgres to SQLite -
both backends have identical structures.
   Just pass a connection object to an every method.

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

do you mean the connect() function for MySQLDB ?  SA can support  
passing keyword arguments through to connect().  i was actually  
cleaning that API up a little bit for a checkin to the trunk today.


On Aug 23, 2006, at 4:05 PM, Monty Taylor wrote:

> Hi - sorry for the cross post - but it's the same general idea.
>
> I've got a possible tiny patch for better MySQL support - but I'm not
> 100% sure what would be the 'best' way to do it would be.
>
> Briefly... (this is in response to having read the turbogears wiki
> article about using a socket file) if you want the python MySQL module
> to actually read the /etc/my.cnf and ~/.my.cnf file information, you
> have to pass read_default_group='' to the contructor. If you put a
> string there (like 'sqlobject' or 'sqlalchemy') to the constructor, it
> will read [sqlalchemy] or [sqlobject] sections and [client] sections
> in the config files and behave as you expect it to.
>
> So I think it's what people expect to have the files read. Whether we
> want to add a section [sqlobject] or [sqlalchemy] by default or not,
> or just leave it to read the [client] sections is another question...
>
> Thoughts?
>
> Monty
>
> ---------------------------------------------------------------------- 
> ---
> Using Tomcat but need to do more? Need to support web services,  
> security?
> Get stuff done quickly with pre-integrated technology to make your  
> job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache  
> Geronimo
> http://sel.as-us.falkag.net/sel? 
> cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> Sqlalchemy-users mailing list
> Sqlalchemy-users@...
> https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users

I do... and you are right - the code already supports setting
read_default_group. You can certainly pass it in client code.

What I was suggesting, though, is that the general case is that people
don't really know all the options of the MySQL client library. Based
on experiences with the MySQL command line clients, almost everyone
expects that options set in /etc/my.cnf will affect how things
connect. I actually wouldn't mind this being set in the MySQLdb
adapter, but I doubt that will happen. I think if we give this a
default value, we will solve a large number of complaints and
confusion. On the other hand, if you don't want it, you can always
override it - but I imagine that to be the minority case.

Monty

On 8/25/06, Michael Bayer <mike_mp@...> wrote:
> do you mean the connect() function for MySQLDB ?  SA can support
> passing keyword arguments through to connect().  i was actually
> cleaning that API up a little bit for a checkin to the trunk today.
>
>
> On Aug 23, 2006, at 4:05 PM, Monty Taylor wrote:
>
> > Hi - sorry for the cross post - but it's the same general idea.
> >
> > I've got a possible tiny patch for better MySQL support - but I'm not
> > 100% sure what would be the 'best' way to do it would be.
> >
> > Briefly... (this is in response to having read the turbogears wiki
> > article about using a socket file) if you want the python MySQL module
> > to actually read the /etc/my.cnf and ~/.my.cnf file information, you
> > have to pass read_default_group='' to the contructor. If you put a
> > string there (like 'sqlobject' or 'sqlalchemy') to the constructor, it
> > will read [sqlalchemy] or [sqlobject] sections and [client] sections
> > in the config files and behave as you expect it to.
> >
> > So I think it's what people expect to have the files read. Whether we
> > want to add a section [sqlobject] or [sqlalchemy] by default or not,
> > or just leave it to read the [client] sections is another question...
> >
> > Thoughts?
> >
> > Monty
> >
> > ----------------------------------------------------------------------
> > ---
> > Using Tomcat but need to do more? Need to support web services,
> > security?
> > Get stuff done quickly with pre-integrated technology to make your
> > job easier
> > Download IBM WebSphere Application Server v.1.0.1 based on Apache
> > Geronimo
> > http://sel.as-us.falkag.net/sel?
> > cmd=lnk&kid=120709&bid=263057&dat=121642
> > _______________________________________________
> > Sqlalchemy-users mailing list
> > Sqlalchemy-users@...
> > https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
>
>

I just tried read_default_group with a mysqldb connection while  
running ktrace (osx), and it doesnt seem to access my.cnf at all. I  
got the same results when i tried it with no arguments.   this is not  
consistent with what the mysqldb docs say, which for  
read_default_group and read_default_file point to the mysql api docs,  
which say that "my.cnf" is the default file and the default group is  
nothing.  isnt this a bug in mysqldb ?   can you show me code where  
it actually works ?


On Aug 25, 2006, at 1:44 PM, Monty Taylor wrote:

> I do... and you are right - the code already supports setting
> read_default_group. You can certainly pass it in client code.
>
> What I was suggesting, though, is that the general case is that people
> don't really know all the options of the MySQL client library. Based
> on experiences with the MySQL command line clients, almost everyone
> expects that options set in /etc/my.cnf will affect how things
> connect. I actually wouldn't mind this being set in the MySQLdb
> adapter, but I doubt that will happen. I think if we give this a
> default value, we will solve a large number of complaints and
> confusion. On the other hand, if you don't want it, you can always
> override it - but I imagine that to be the minority case.
>
> Monty
>
> On 8/25/06, Michael Bayer <mike_mp@...> wrote:
>> do you mean the connect() function for MySQLDB ?  SA can support
>> passing keyword arguments through to connect().  i was actually
>> cleaning that API up a little bit for a checkin to the trunk today.
>>
>>
>> On Aug 23, 2006, at 4:05 PM, Monty Taylor wrote:
>>
>> > Hi - sorry for the cross post - but it's the same general idea.
>> >
>> > I've got a possible tiny patch for better MySQL support - but  
>> I'm not
>> > 100% sure what would be the 'best' way to do it would be.
>> >
>> > Briefly... (this is in response to having read the turbogears wiki
>> > article about using a socket file) if you want the python MySQL  
>> module
>> > to actually read the /etc/my.cnf and ~/.my.cnf file information,  
>> you
>> > have to pass read_default_group='' to the contructor. If you put a
>> > string there (like 'sqlobject' or 'sqlalchemy') to the  
>> constructor, it
>> > will read [sqlalchemy] or [sqlobject] sections and [client]  
>> sections
>> > in the config files and behave as you expect it to.
>> >
>> > So I think it's what people expect to have the files read.  
>> Whether we
>> > want to add a section [sqlobject] or [sqlalchemy] by default or  
>> not,
>> > or just leave it to read the [client] sections is another  
>> question...
>> >
>> > Thoughts?
>> >
>> > Monty
>> >
>> >  
>> --------------------------------------------------------------------- 
>> -
>> > ---
>> > Using Tomcat but need to do more? Need to support web services,
>> > security?
>> > Get stuff done quickly with pre-integrated technology to make your
>> > job easier
>> > Download IBM WebSphere Application Server v.1.0.1 based on Apache
>> > Geronimo
>> > http://sel.as-us.falkag.net/sel?
>> > cmd=lnk&kid=120709&bid=263057&dat=121642
>> > _______________________________________________
>> > Sqlalchemy-users mailing list
>> > Sqlalchemy-users@...
>> > https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
>>
>>

Hi Michael,

test.py:
import MySQLdb

c=MySQLdb.connect(host='localhost',read_default_group='client')
cur=c.cursor()
cur.execute('select * from sf_user where id=1')
print cur.fetchone()

I tried several combinations. First, with nothing in any files, I get:
Traceback (most recent call last):
  File "test.py", line 5, in ?
    cur.execute('select * from sf_user where id=1')
  File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line
137, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line
33, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1046, 'No database selected')

which is as we would expect, since I didn't define one. If I add a
database=sfdev line to /etc/my.cnf, I also get that error... except
that I'm on a debian system (ubuntu, actually) where the default
location is /etc/mysql/my.cnf. So if I edit /etc/mysql/my.cnf OR
~/.my.cnf, I get:

(1L, 'monty', 'mony_email', 'Monty Taylor', 'foo',
datetime.datetime(2006, 8, 23, 14, 21))

as I would expect.

I'm not sure what the behavior is on OSX. It could be like Debian with
non-standard default locations of files - but I don't have access to a
box at the moment to track it down.

Monty

On 8/25/06, Michael Bayer <mike_mp@...> wrote:
> I just tried read_default_group with a mysqldb connection while
> running ktrace (osx), and it doesnt seem to access my.cnf at all. I
> got the same results when i tried it with no arguments.   this is not
> consistent with what the mysqldb docs say, which for
> read_default_group and read_default_file point to the mysql api docs,
> which say that "my.cnf" is the default file and the default group is
> nothing.  isnt this a bug in mysqldb ?   can you show me code where
> it actually works ?
>
>
> On Aug 25, 2006, at 1:44 PM, Monty Taylor wrote:
>
> > I do... and you are right - the code already supports setting
> > read_default_group. You can certainly pass it in client code.
> >
> > What I was suggesting, though, is that the general case is that people
> > don't really know all the options of the MySQL client library. Based
> > on experiences with the MySQL command line clients, almost everyone
> > expects that options set in /etc/my.cnf will affect how things
> > connect. I actually wouldn't mind this being set in the MySQLdb
> > adapter, but I doubt that will happen. I think if we give this a
> > default value, we will solve a large number of complaints and
> > confusion. On the other hand, if you don't want it, you can always
> > override it - but I imagine that to be the minority case.
> >
> > Monty
> >
> > On 8/25/06, Michael Bayer <mike_mp@...> wrote:
> >> do you mean the connect() function for MySQLDB ?  SA can support
> >> passing keyword arguments through to connect().  i was actually
> >> cleaning that API up a little bit for a checkin to the trunk today.
> >>
> >>
> >> On Aug 23, 2006, at 4:05 PM, Monty Taylor wrote:
> >>
> >> > Hi - sorry for the cross post - but it's the same general idea.
> >> >
> >> > I've got a possible tiny patch for better MySQL support - but
> >> I'm not
> >> > 100% sure what would be the 'best' way to do it would be.
> >> >
> >> > Briefly... (this is in response to having read the turbogears wiki
> >> > article about using a socket file) if you want the python MySQL
> >> module
> >> > to actually read the /etc/my.cnf and ~/.my.cnf file information,
> >> you
> >> > have to pass read_default_group='' to the contructor. If you put a
> >> > string there (like 'sqlobject' or 'sqlalchemy') to the
> >> constructor, it
> >> > will read [sqlalchemy] or [sqlobject] sections and [client]
> >> sections
> >> > in the config files and behave as you expect it to.
> >> >
> >> > So I think it's what people expect to have the files read.
> >> Whether we
> >> > want to add a section [sqlobject] or [sqlalchemy] by default or
> >> not,
> >> > or just leave it to read the [client] sections is another
> >> question...
> >> >
> >> > Thoughts?
> >> >
> >> > Monty
> >> >
> >> >
> >> ---------------------------------------------------------------------
> >> -
> >> > ---
> >> > Using Tomcat but need to do more? Need to support web services,
> >> > security?
> >> > Get stuff done quickly with pre-integrated technology to make your
> >> > job easier
> >> > Download IBM WebSphere Application Server v.1.0.1 based on Apache
> >> > Geronimo
> >> > http://sel.as-us.falkag.net/sel?
> >> > cmd=lnk&kid=120709&bid=263057&dat=121642
> >> > _______________________________________________
> >> > Sqlalchemy-users mailing list
> >> > Sqlalchemy-users@...
> >> > https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
> >>
> >>
>
>

Oleg,

Being dense, and having fought with this a while now, can you provide a 
very brief example of "pass a connection object to an every method" in 
this case Ray presents of two databases?  I am sure there is a very 
clean pythonic way to handle this, and I am just missing it.

- Michael


Oleg Broytmann wrote:
> On Thu, Aug 24, 2006 at 09:30:42AM +1000, Ray Smith wrote:
>> Is it possible to connect to 2 databases each of which have a common
>> table name??
> 
>    It is. I have a program that copies a database from Postgres to SQLite -
> both backends have identical structures.
>    Just pass a connection object to an every method.
> 
> Oleg.

Quoting MichaelG <mgrollman@...>:

>
>
> Oleg,
>
> Being dense, and having fought with this a while now, can you provide a
> very brief example of \\\"pass a connection object to an every method\\\" in
> this case Ray presents of two databases?  I am sure there is a very
> clean pythonic way to handle this, and I am just missing it.
>
> - Michael


Hi Michael,

(I\\\'ll do my best to answer)

Each SQLObject method has a \\\"connection\\\" parameter.
If not specified the connection set as  
\\\"sqlhub.processConnection\\\" is used.

See attched example.

Regards,

Ray Smith
http://RaymondSmith.com

That was very helpful, thanks Ray.

I also read through
http://pythonpaste.org/archives/message/20060612.010606.e1e2ddad.en.html
which is similar to your approach.

Safety in numbers!

The timing of connection use at instantiation was, I think, what was 
confusing to me.


Merci.


Ray Smith wrote:
> Quoting MichaelG <mgrollman@...>:
> 
>>
>>
>> Oleg,
>>
>> Being dense, and having fought with this a while now, can you provide a
>> very brief example of \\\"pass a connection object to an every 
>> method\\\" in
>> this case Ray presents of two databases?  I am sure there is a very
>> clean pythonic way to handle this, and I am just missing it.
>>
>> - Michael
> 
> 
> Hi Michael,
> 
> (I\\\'ll do my best to answer)
> 
> Each SQLObject method has a \\\"connection\\\" parameter.
> If not specified the connection set as \\\"sqlhub.processConnection\\\" 
> is used.
> 
> See attched example.
> 
> Regards,
> 
> Ray Smith
> http://RaymondSmith.com
> 
> 
> 
> 
> ------------------------------------------------------------------------
> 
> from sqlobject import *
> import os
> 
> # define a table
> class Test(SQLObject):
>    desc=StringCol()
> 
> # open 2 connections to 2 databases
> if os.path.exists('c:\\test1.db'):
>    os.unlink('c:\\test1.db')
> con1=connectionForURI('sqlite:/c|/test1.db')
> 
> if os.path.exists('c:\\test2.db'):
>    os.unlink('c:\\test2.db')
> con2=connectionForURI('sqlite:/c|/test2.db')
> 
> # execute methods passing our connection objects
> # add record to first database
> Test.createTable(connection=con1)
> Test(desc='Hello', connection=con1)
> 
> # add record to second database
> Test.createTable(connection=con2)
> Test(desc='There', connection=con2)
> 
> # close
> con1.close()
> con2.close()
> 
> 
> ------------------------------------------------------------------------
> 
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> sqlobject-discuss mailing list
> sqlobject-discuss@...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Isn't it possible to use a second class which derivate from the initial
one.
In this class you may specify the connection and the table name?
MichaelG a =E9crit :
> Oleg,
>
> Being dense, and having fought with this a while now, can you provide a=
=20
> very brief example of "pass a connection object to an every method" in=20
> this case Ray presents of two databases?  I am sure there is a very=20
> clean pythonic way to handle this, and I am just missing it.
>
> - Michael
>
>
> Oleg Broytmann wrote:
>  =20
>> On Thu, Aug 24, 2006 at 09:30:42AM +1000, Ray Smith wrote:
>>    =20
>>> Is it possible to connect to 2 databases each of which have a common
>>> table name??
>>>      =20
>>    It is. I have a program that copies a database from Postgres to SQL=
ite -
>> both backends have identical structures.
>>    Just pass a connection object to an every method.
>>
>> Oleg.
>>    =20
>
>
> -----------------------------------------------------------------------=
--
> Using Tomcat but need to do more? Need to support web services, securit=
y?
> Get stuff done quickly with pre-integrated technology to make your job =
easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geron=
imo
> http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D120709&bid=3D263057&dat=
=3D121642
> _______________________________________________
> sqlobject-discuss mailing list
> sqlobject-discuss@...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>
>  =20

On Fri, Sep 01, 2006 at 03:44:24PM +0200, sophana wrote:
> Isn't it possible to use a second class which derivate from the initial
> one.
> In this class you may specify the connection and the table name?

   A different registry would be required, I think.

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

sophana wrote:
 > Isn't it possible to use a second class which derivate from the initial
 > one.
 > In this class you may specify the connection and the table name?

It depends on the situation.
I asked the original question and the problem I was trying to solve was 
connecting to 2 different databases each of which had a table name the same.
The table schemas though were different.

Regards,

Ray Smith
http://RaymondSmith.org

Ray Smith wrote:
> Ray Smith
> http://RaymondSmith.org

I know it's a trivial problem, but at 4:30am I typed 
http://RaymondSmith.org instead of http://RaymondSmith.com.
Just wanted to acknowledge my error in case the Raymond Smith from 
RaymondSmith.org comes to visit the mailing list archives ;)

Regards,

Ray Smith
http://RaymondSmith.com