tutorial - sqlalchemy flask postgresql

Soporte SQLAlchemy de los esquemas de Postgres

Question

Alojamos una aplicación multiusuario con SQLAlchemy y postgres. Estoy considerando pasar de tener bases de datos separadas para cada inquilino a una única base de datos con múltiples esquemas. ¿SQLAlchemy es compatible con esto de forma nativa? Básicamente, simplemente quiero que cada consulta que sale tenga un prefijo con un esquema predeterminado ... por ejemplo

select * from client1.users

en lugar de simplemente

select * from users

Tenga en cuenta que quiero cambiar el esquema para todas las tablas en una solicitud / conjunto de solicitudes en particular, no solo una tabla única aquí y allá.

Imagino que esto podría lograrse con una clase de consulta personalizada también, pero no puedo imaginar que algo no se haya hecho en este sentido.

Answer 1

Es posible que pueda gestionar esto utilizando la interfaz de evento sqlalchemy. Entonces, antes de crear la primera conexión, configure un oyente a lo largo de las líneas de

from sqlalchemy import event
from sqlalchemy.pool import Pool

def set_search_path( db_conn, conn_proxy ):
    print "Setting search path..."
    db_conn.cursor().execute(''set search_path=client9, public'')

event.listen(Pool,''connect'', set_search_path )

Obviamente, esto debe ejecutarse antes de que se cree la primera conexión (por ejemplo, en la iniciación de la aplicación)

El problema que veo con la solución session.execute (...) es que esto se ejecuta en una conexión específica utilizada por la sesión. Sin embargo, no puedo ver nada en sqlalchemy que garantice que la sesión continuará usando la misma conexión indefinidamente. Si detecta una nueva conexión desde el grupo de conexiones, perderá la configuración de la ruta de búsqueda.

Necesito un enfoque como este para establecer la aplicación search_path, que es diferente de la base de datos o la ruta de búsqueda del usuario. Me gustaría poder establecer esto en la configuración del motor, pero no veo la manera de hacerlo. Usar el evento connect funciona. Me interesaría una solución más simple si alguien tiene una.

Por otro lado, si quiere manejar múltiples clientes dentro de una aplicación, entonces esto no funcionará, y supongo que el enfoque session.execute (...) puede ser el mejor enfoque.

Answer 2

Hay una propiedad de esquema en las definiciones de Tabla

No estoy seguro de si funciona, pero puedes intentarlo:

Table(CP.get(''users'', metadata, schema=''client1'',....)

Answer 3

No encontré ninguna de las respuestas anteriores trabajadas con SqlAlchmeny 1.2.4 . Esta es la solución que funcionó para mí.

from sqlalchemy import MetaData, Table
from sqlalchemy import create_engine    

def table_schemato_psql(schema_name, table_name):

        conn_str = ''postgresql://{username}:{password}@localhost:5432/{database}''.format(
            username=''<username>'',
            password=''<password>'',
            database=''<database name>''
        )

        engine = create_engine(conn_str)

        with engine.connect() as conn:
            conn.execute(''SET search_path TO {schema}''.format(schema=schema_name))

            meta = MetaData()

            table_data = Table(table_name, meta,
                              autoload=True,
                              autoload_with=conn,
                              postgresql_ignore_search_path=True)

            for column in table_data.columns:
                print column.name

Answer 4

Puedes simplemente cambiar tu search_path. Problema

set search_path=client9;

al comienzo de su sesión y luego simplemente mantenga sus tablas sin calificar.

También puede establecer una ruta de búsqueda predeterminada por nivel de base de datos o por usuario. Estaría tentado de establecerlo en un esquema vacío de forma predeterminada para que pueda detectar fácilmente cualquier falla al establecerlo.

http://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-PATH

Answer 5

Si desea hacer esto en el nivel de cadena de conexión, utilice lo siguiente:

dbschema=''schema1,schema2,public'' # Searches left-to-right
engine = create_engine(
    ''postgresql+psycopg2://dbuser@dbhost:5432/dbname'',
    connect_args={''options'': ''-csearch_path={}''.format(dbschema)})

Sin embargo, una mejor solución para una aplicación multicliente (multi-tenant) es configurar un usuario de db diferente para cada cliente y configurar el search_path relevante para cada usuario:

alter role user1 set search_path = "$user", public

Answer 6

bueno, hay algunas maneras de hacerlo y depende de cómo esté estructurada su aplicación. Esta es la forma más básica:

meta = MetaData(schema="client1")

Si la forma en que se ejecuta su aplicación es un "cliente" a la vez dentro de toda la aplicación, ya está.

Pero lo que puede estar mal con eso aquí es que cada tabla de ese metadato está en ese esquema. Si desea que una aplicación admita varios clientes simultáneamente (generalmente lo que significa "multiusuario"), esto sería difícil de manejar ya que necesitaría crear una copia de los metadatos y deducir todas las asignaciones para cada cliente. Este enfoque se puede hacer, si realmente lo desea, la forma en que funciona es que accedería a cada cliente con una clase asignada particular como:

client1_foo = Client1Foo()

y en ese caso estaría trabajando con la receta "nombre de la entidad" en http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName junto con sometable.tometadata() (vea http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.Table.tometadata ).

Entonces, digamos que la manera en que realmente funciona es en múltiples clientes dentro de la aplicación, pero solo uno a la vez por hilo. Bueno, en realidad, la forma más fácil de hacer eso en Postgresql sería establecer la ruta de búsqueda cuando comiences a trabajar con una conexión:

# start request

# new session
sess = Session()

# set the search path
sess.execute("SET search_path TO client1")

# do stuff with session

# close it.  if you''re using connection pooling, the
# search path is still set up there, so you might want to 
# revert it first
sess.close()

El enfoque final sería anular el compilador utilizando la extensión @compiles para pegar el nombre de "esquema" en las instrucciones. Esto es factible, pero sería complicado ya que no existe un gancho consistente para todos los lugares donde se genera "Tabla". Su mejor opción es, probablemente, establecer la ruta de búsqueda en cada solicitud.