Aaron Pestel | 5 Jun 08:04 2010
Picon

Creating SQL connection to Postgres over SSL

Hello,

I'm working with a Python application that is using SQLObject to connect 
to Postgres.  However, we need the connection from Python to the 
Postgres DB to be over SSL.

It looks like the Postgres Python driver has an "sslmode" that can be 
set [1].  However, I'm having difficulty seeing how that could be set 
through the PostgresConnection [2].  I have a Java background rather 
than a Python background, so that may be part of the problem.

I think the connection is being created like this:

connectionForURI("postgresql://cumin <at> localhost/cumin").getConnection()

Is it possible for me to add something like listed in [1] to make the DB 
connection over SSL?  I tried 
postgresql://cumin <at> localhost/cumin?[sslmode]=require, but that doesn't 
seem to work.

[1]  
http://python.projects.postgresql.org/docs/1.0/driver.html#connection-keywords 

[2]  
http://svn.colorstudy.com/SQLObject/trunk/sqlobject/postgres/pgconnection.py 

Thanks!

Aaron Pestel

(Continue reading)

Frank Wagner | 5 Jun 10:43 2010
Picon

Re: Creating SQL connection to Postgres over SSL

Hi Aaron,

from what I have seen in the code my guess is that any parameter added to the connection uri is passed along to
the database driver, if it isn´t amongst the ones specified in http://sqlobject.org/SQLObject.html#declaring-a-connection

having said that, your approach 
postgresql://cumin <at> localhost/cumin?sslmode=require

should be working, except for the square brackets, just omit them.
my uris look like this for example:
mysql://user:pass <at> host/dbname?charset=utf8&use_unicode=1&debug=True

HTH,
Frank

Am 05.06.2010 um 08:04 schrieb Aaron Pestel:

> Hello,
> 
> I'm working with a Python application that is using SQLObject to connect 
> to Postgres.  However, we need the connection from Python to the 
> Postgres DB to be over SSL.
> 
> It looks like the Postgres Python driver has an "sslmode" that can be 
> set [1].  However, I'm having difficulty seeing how that could be set 
> through the PostgresConnection [2].  I have a Java background rather 
> than a Python background, so that may be part of the problem.
> 
> I think the connection is being created like this:
> 
(Continue reading)

Oleg Broytman | 18 Jun 10:19 2010
X-Face
Picon

Re: Creating SQL connection to Postgres over SSL

On Sat, Jun 05, 2010 at 01:04:37AM -0500, Aaron Pestel wrote:
> postgresql://cumin <at> localhost/cumin?[sslmode]=require, but that doesn't 
> seem to work.

   Currently it's impossible. I added the request to my TODO list.

Oleg.
--

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

------------------------------------------------------------------------------
ThinkGeek and WIRED's GeekDad team up for the Ultimate 
GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the 
lucky parental unit.  See the prize list and enter to win: 
http://p.sf.net/sfu/thinkgeek-promo
Julien Syx | 21 Jun 23:34 2010
Picon

Problem with "driver has already been registered"

Hi everyone.

I'm working on a project and i use sqlobject.

I decided to create a package which contains all my functions, classes and modules.

The package is like this :

/application.tac
/package
/package/sql/scopssql.py -> contains my class to connect to the database, and classes to use/create it. (
/package/sql/sqlobject/ -> contains the sqlobject module.
/package/servers/

In my .tac, i do :
import sys
sys.path.append(".")
sys.path.append("./scops")

In the file scopssql.py, i do a : 
from sqlobject import *
When i launch my app :
seraf <at> tagada-server:/opt/Scops/Serveur# twistd -noy scops.tac 

Traceback (most recent call last):

  File "/usr/lib/python2.6/dist-packages/twisted/application/app.py", line 693, in run

    runApp(config)

  File "/usr/lib/python2.6/dist-packages/twisted/scripts/twistd.py", line 23, in runApp

    _SomeApplicationRunner(config).run()

  File "/usr/lib/python2.6/dist-packages/twisted/application/app.py", line 411, in run

    self.application = self.createOrGetApplication()

  File "/usr/lib/python2.6/dist-packages/twisted/application/app.py", line 494, in createOrGetApplication

    application = getApplication(self.config, passphrase)

--- <exception caught here> ---

  File "/usr/lib/python2.6/dist-packages/twisted/application/app.py", line 505, in getApplication

    application = service.loadApplication(filename, style, passphrase)

  File "/usr/lib/python2.6/dist-packages/twisted/application/service.py", line 390, in loadApplication

    application = sob.loadValueFromFile(filename, 'application', passphrase)

  File "/usr/lib/python2.6/dist-packages/twisted/persisted/sob.py", line 210, in loadValueFromFile

    exec fileObj in d, d

  File "scops.tac", line 16, in <module>

    from scops.sql.scopssql import connectSQL

  File "./scops/sql/scopssql.py", line 1, in <module>

    from sqlobject import *

  File "./scops/sql/sqlobject/__init__.py", line 8, in <module>

    from main import *

  File "./scops/sql/sqlobject/main.py", line 32, in <module>

    import dbconnection

  File "./scops/sql/sqlobject/dbconnection.py", line 964, in <module>

    import firebird

  File "./scops/sql/sqlobject/firebird/__init__.py", line 7, in <module>

    registerConnection(['firebird', 'interbase'], builder)

  File "./scops/sql/sqlobject/dbconnection.py", line 918, in registerConnection

    "A driver has already been registered for the URI scheme %s" % uriScheme

exceptions.AssertionError: A driver has already been registered for the URI scheme firebird

Failed to load application: A driver has already been registered for the URI scheme firebird

Have you an idea why it fails ?

Thanks a lot !
------------------------------------------------------------------------------
ThinkGeek and WIRED's GeekDad team up for the Ultimate 
GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the 
lucky parental unit.  See the prize list and enter to win: 
http://p.sf.net/sfu/thinkgeek-promo
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Imre Horvath | 22 Jun 11:20 2010
Picon

select only specified columns

Hi everyone!

Is it possible to select only a few columns from a table?
My primary problem is speed when i select all rows from a big table (>1
mill. record).
But i only need 2-3 columns from it. I think it's because all other
columns & related objects are selected.

FYI: The exact scenario is:
Customers table: id, name, lots of other data.
For a master/detail form, i need to fill the list with id and name only.
When the user selects a row in the list, i get the customer object via
id, and fill the details form.

Thanks for advance:
Imre Horvath

------------------------------------------------------------------------------
ThinkGeek and WIRED's GeekDad team up for the Ultimate 
GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the 
lucky parental unit.  See the prize list and enter to win: 
http://p.sf.net/sfu/thinkgeek-promo
Oleg Broytman | 22 Jun 11:37 2010
X-Face
Picon

Re: Problem with "driver has already been registered"

On Mon, Jun 21, 2010 at 11:34:50PM +0200, Julien Syx wrote:
>     from scops.sql.scopssql import connectSQL
>   File "./scops/sql/scopssql.py", line 1, in <module>
>     from sqlobject import *
>   File "./scops/sql/sqlobject/__init__.py", line 8, in <module>
>     from main import *
>   File "./scops/sql/sqlobject/main.py", line 32, in <module>
>     import dbconnection
>   File "./scops/sql/sqlobject/dbconnection.py", line 964, in <module>
>     import firebird
>   File "./scops/sql/sqlobject/firebird/__init__.py", line 7, in <module>
>     registerConnection(['firebird', 'interbase'], builder)
>   File "./scops/sql/sqlobject/dbconnection.py", line 918, in
> registerConnection
>     "A driver has already been registered for the URI scheme %s" % uriScheme
> exceptions.AssertionError: A driver has already been registered for the URI
> scheme firebird

   I suspect it could be because sqlobject was imported twice, probably
because of manipulations with sys.path.

Oleg.
--

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

------------------------------------------------------------------------------
ThinkGeek and WIRED's GeekDad team up for the Ultimate 
GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the 
lucky parental unit.  See the prize list and enter to win: 
http://p.sf.net/sfu/thinkgeek-promo
Oleg Broytman | 22 Jun 11:42 2010
X-Face
Picon

Re: select only specified columns

On Tue, Jun 22, 2010 at 11:20:39AM +0200, Imre Horvath wrote:
> Is it possible to select only a few columns from a table?

   No, but you can lazily selects columns using lazyColumns=True; with this
SQLObject doesn't initially draw any column, only id's; but when you touch a
row and ask for a column SQLObject executes a specific one-row one-column
SELECT query. If you are going to touch a million rows this lead to at least
few millions queries; perhaps it'd be faster to select all columns at once,
but YMMV.

Oleg.
--

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

------------------------------------------------------------------------------
ThinkGeek and WIRED's GeekDad team up for the Ultimate 
GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the 
lucky parental unit.  See the prize list and enter to win: 
http://p.sf.net/sfu/thinkgeek-promo
Julien Syx | 22 Jun 12:06 2010
Picon

Re: Problem with "driver has already been registered"

Thanks for your quick answer.

I don't know where it can be imported twice.
Does the sys.path manipulation auto import modules ?

Here is an access to the code :
login : mlsqlobject
pass : scops

it's accessible through webbrowser.

Thanks a lot.

On Tue, Jun 22, 2010 at 11:37 AM, Oleg Broytman <phd <at> phd.pp.ru> wrote:
On Mon, Jun 21, 2010 at 11:34:50PM +0200, Julien Syx wrote:
>     from scops.sql.scopssql import connectSQL
>   File "./scops/sql/scopssql.py", line 1, in <module>
>     from sqlobject import *
>   File "./scops/sql/sqlobject/__init__.py", line 8, in <module>
>     from main import *
>   File "./scops/sql/sqlobject/main.py", line 32, in <module>
>     import dbconnection
>   File "./scops/sql/sqlobject/dbconnection.py", line 964, in <module>
>     import firebird
>   File "./scops/sql/sqlobject/firebird/__init__.py", line 7, in <module>
>     registerConnection(['firebird', 'interbase'], builder)
>   File "./scops/sql/sqlobject/dbconnection.py", line 918, in
> registerConnection
>     "A driver has already been registered for the URI scheme %s" % uriScheme
> exceptions.AssertionError: A driver has already been registered for the URI
> scheme firebird

  I suspect it could be because sqlobject was imported twice, probably
because of manipulations with sys.path.

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

------------------------------------------------------------------------------
ThinkGeek and WIRED's GeekDad team up for the Ultimate
GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the
lucky parental unit.  See the prize list and enter to win:
http://p.sf.net/sfu/thinkgeek-promo
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

------------------------------------------------------------------------------
ThinkGeek and WIRED's GeekDad team up for the Ultimate 
GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the 
lucky parental unit.  See the prize list and enter to win: 
http://p.sf.net/sfu/thinkgeek-promo
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Imre Horvath | 22 Jun 12:09 2010
Picon

Re: select only specified columns

Thank you, that's what I've looking for.
In my case, I only fill details view for one record only, 30-40 columns,
it's fast enough with one-column queries too.
But I wonder if it's possible to override get method to temporary switch
off lazyColumns?
I mean:

class Customer(SQLObject):
	def get(self, id):
		self.lazyColumns = False
		entity = Customer.get(id)
		self.lazyColumns = True
		return entity

Imre

2010. 06. 22, kedd keltezéssel 13.42-kor Oleg Broytman ezt írta:
> On Tue, Jun 22, 2010 at 11:20:39AM +0200, Imre Horvath wrote:
> > Is it possible to select only a few columns from a table?
> 
>    No, but you can lazily selects columns using lazyColumns=True; with this
> SQLObject doesn't initially draw any column, only id's; but when you touch a
> row and ask for a column SQLObject executes a specific one-row one-column
> SELECT query. If you are going to touch a million rows this lead to at least
> few millions queries; perhaps it'd be faster to select all columns at once,
> but YMMV.
> 
> Oleg.

------------------------------------------------------------------------------
ThinkGeek and WIRED's GeekDad team up for the Ultimate 
GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the 
lucky parental unit.  See the prize list and enter to win: 
http://p.sf.net/sfu/thinkgeek-promo
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss <at> lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Oleg Broytman | 22 Jun 12:15 2010
X-Face
Picon

Re: Problem with "driver has already been registered"

On Tue, Jun 22, 2010 at 12:06:35PM +0200, Julien Syx wrote:
> I don't know where it can be imported twice.
> Does the sys.path manipulation auto import modules ?

   By itself - no, but there could be two different imports in different
parts of the application before and after sys.path change.

Oleg.
--

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

------------------------------------------------------------------------------
ThinkGeek and WIRED's GeekDad team up for the Ultimate 
GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the 
lucky parental unit.  See the prize list and enter to win: 
http://p.sf.net/sfu/thinkgeek-promo

Gmane