Using Named Schemas in Postgresql

[Kjetil Østerås]

Hi,

How can I use named schemas in postgresql together with peewee? Here is an example of what I'm trying to do:

CREATE SCHEMA IF NOT EXISTS foo;
CREATE TABLE IF NOT EXISTS foo.person (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

Then I'm trying to access this table with peewee

from peewee import *

db = PostgresqlDatabase('testdb')

class Person(Model):
  id = IntegerField()
  name = CharField()

  class Meta:
    database = db
    db_table = 'foo.person'
    #schema = 'foo'

for p in Person.select():
  print(p.name)

This fails because the query generated looks like 'SELECT t1."id", t1."name" FROM "foo.person" AS t1' while it should look something like 'SELECT t1."id", t1."name" FROM "foo"."person" AS t1'. Is named schemas in postgresql supported by peewee?

[Charles Leifer]

Did it not work setting schema = 'foo' in the model Meta inner-class?

--
You received this message because you are subscribed to the Google Groups "peewee-orm" group.
To unsubscribe from this group and stop receiving emails from it, send an email to peewee-orm+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Charles Leifer]

Oh and also you can try:

db.set_search_path('foo')

[Kjetil Østerås]

Hi Charles,

db.set_search_path('foo') did the trick. Now it runs smooth. Thanks.

Btw setting schema='foo' does not make any difference for me

You received this message because you are subscribed to a topic in the Google Groups "peewee-orm" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/peewee-orm/0dP_Rk3qzxY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to peewee-orm+...@googlegroups.com.

[Charles Leifer]

I'm surprised that setting `schema = 'foo'` did not have an effect. What in particular did not work? In my testing, it seems that peewee generates the correct SQL.

[Kjetil Østerås]

Hi,

It might be that I'm using an older version of peewee (python-peewee.noarch 2.1.7-1.fc20). This code snippet:

from peewee import *

db = PostgresqlDatabase('testdb')

class Person(Model):
  id = IntegerField()
  name = CharField()

  class Meta:
    database =

db
    db_table = 'person'

    schema = 'foo'

for p in Person.select():
  print(p.name)

Will produce this query on my system:
SELECT t1."id", t1."name" FROM "person" AS t1

And if I try to use pwiz to extract the code for this example I get this output:

~$ pwiz -e postgres -s foo testdb
from peewee import *

database = PostgresqlDatabase('testdb', **{'schema': 'foo'})

class UnknownFieldType(object):
    pass

class BaseModel(Model):
    class Meta:
        database = database

class Person(BaseModel):
    name = TextField()

    class Meta:
        db_table = 'person'

When I try to run this code that is generated I get this output:

Traceback (most recent call last):
  File "pee.py", line 17, in <module>
    for p in Person.select():
  File "/usr/lib/python2.7/site-packages/peewee.py", line 1847, in __iter__
    return iter(self.execute())
  File "/usr/lib/python2.7/site-packages/peewee.py", line 1840, in execute
    self._qr = ResultWrapper(model_class, self._execute(), query_meta)
  File "/usr/lib/python2.7/site-packages/peewee.py", line 1592, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/usr/lib/python2.7/site-packages/peewee.py", line 2060, in execute_sql
    self.commit()
  File "/usr/lib/python2.7/site-packages/peewee.py", line 1937, in __exit__
    reraise(new_type, new_type(*exc_value.args), traceback)
  File "/usr/lib/python2.7/site-packages/peewee.py", line 2051, in execute_sql
    cursor = self.get_cursor()
  File "/usr/lib/python2.7/site-packages/peewee.py", line 2017, in get_cursor
    return self.get_conn().cursor()
  File "/usr/lib/python2.7/site-packages/peewee.py", line 2010, in get_conn
    self.connect()
  File "/usr/lib/python2.7/site-packages/peewee.py", line 1997, in connect
    self.__local.closed = False
  File "/usr/lib/python2.7/site-packages/peewee.py", line 1937, in __exit__
    reraise(new_type, new_type(*exc_value.args), traceback)
  File "/usr/lib/python2.7/site-packages/peewee.py", line 1996, in connect
    **self.connect_kwargs)
  File "/usr/lib/python2.7/site-packages/peewee.py", line 2219, in _connect
    conn = psycopg2.connect(database=database, **kwargs)
  File "/usr/lib64/python2.7/site-packages/psycopg2/__init__.py", line 164, in connect
    conn = _connect(dsn, connection_factory=connection_factory, async=async)
peewee.OperationalError: invalid connection option "schema"

Does these tests produce the same result for you or are they fixed in the newest release?

[Charles Leifer]

Ah, yeah -- the schema issues were changed in 2.2.0.