python - practices - SQLAlchemy ─ Mapeo de una clase contra varias tablas

sqlalchemy select example

Question

# ! /usr/bin/env python
# -*- coding: utf-8 -*-
# login_frontend.py

""" Python        2.7.3
    Cherrypy      3.2.2
    PostgreSQL    9.1
    psycopy2      2.4.5
    SQLAlchemy    0.7.10
"""

Tengo problemas para unir cuatro tablas en una clase de Python / SQLAlchemy. Estoy intentando esto, así que puedo iterar la instancia de esta clase, en lugar de la tupla nombrada, que obtengo al unir las tablas con el ORM.

¿Por qué todo esto? Porque ya empecé de esa manera y llegué demasiado lejos, para dejarlo. Además, tiene que ser posible, así que quiero saber cómo se hace.

Para este proyecto (cherrypy web-frontend) obtuve un módulo ya completado con las clases de tabla. Lo moví al final de esta publicación, porque tal vez ni siquiera sea necesario para ti.

El siguiente es solo un ejemplo de un intento de clase de tablas múltiples unidas. Escogí un caso simple con más de dos tablas y una mesa de unión. Aquí no escribo en estas tablas unidas, pero es necesario en otro lugar. Es por eso que las clases serían una buena solución a este problema.

Mi intento de unirse a una clase,

que es una combinación del módulo de clases de tabla dado y los ejemplos de estos dos sitios web:

- Mapeo de una clase contra varias tablas
- SQLAlchemy: una clase - dos tablas

class JoinUserGroupPerson (Base):

    persons = md.tables[''persons'']
    users = md.tables[''users'']
    user_groups = md.tables[''user_groups'']
    groups = md.tables[''groups'']

    user_group_person =(
        join(persons, users, persons.c.id == users.c.id).
        join(user_groups, users.c.id == user_groups.c.user_id).
        join(groups, groups.c.id == user_groups.c.group_id))

    __table__ = user_group_person

    """ I expanded the redefinition of ''id'' to three tables,
        and removed this following one, since it made no difference:
        users_id = column_property(users.c.id, user_groups.c.user_id)
    """

    id = column_property(persons.c.id, users.c.id, user_groups.c.user_id)
    groups_id = column_property(groups.c.id, user_groups.c.group_id)
    groups_name = groups.c.name

    def __init__(self, group_name, login, name, email=None, phone=None):
        self.groups_name = group_name
        self.login = login
        self.name = name
        self.email = email
        self.phone = phone

    def __repr__(self):
        return(
            "<JoinUserGroupPerson(''%s'', ''%s'', ''%s'', ''%s'', ''%s'')>" %(
            self.groups_name, self.login, self.name, self.email, self.phone))

Diferentes accesos de tabla con esta clase de unión

• Así es como traté de consultar esta clase en otro módulo:

  pg = sqlalchemy.create_engine(
      ''postgresql://{}:{}@{}:{}/{}''.
      format(user, password, server, port, data))
  Session = sessionmaker(bind=pg)
  s1 = Session()
  
  query = (s1.query(JoinUserGroupPerson).
      filter(JoinUserGroupPerson.login==user).
      order_by(JoinUserGroupPerson.id))
  
      record = {}
      for rowX in query:
          for colX in rowX.__table__.columns:
              record[column.name] = getattr(rowX,colX.name)
  
  
  """ RESULT:
  """
  
  
  Traceback (most recent call last):
    File "/usr/local/lib/python2.7/dist-packages/cherrypy/_cprequest.py", line 656, in respond
      response.body = self.handler()
    File "/usr/local/lib/python2.7/dist-packages/cherrypy/lib/encoding.py", line 228, in __call__
      ct.params[''charset''] = self.find_acceptable_charset()
    File "/usr/local/lib/python2.7/dist-packages/cherrypy/lib/encoding.py", line 134, in find_acceptable_charset
      if encoder(encoding):
    File "/usr/local/lib/python2.7/dist-packages/cherrypy/lib/encoding.py", line 86, in encode_string
      for chunk in self.body:
    File "XXX.py", line YYY, in ZZZ
      record[colX.name] = getattr(rowX,colX.name)
  AttributeError: ''JoinUserGroupPerson'' object has no attribute ''user_id''

• Luego revisé los atributos de la tabla:

  for rowX in query:
      return (u''{}''.format(rowX.__table__.columns))
  
  
  """ RESULT:
  """
  
  
  [''persons.id'',
   ''persons.name'',
   ''persons.email'',
   ''persons.phone'',
   ''users.id'',
   ''users.login'',
   ''user_groups.user_id'',
   ''user_groups.group_id'',
   ''groups.id'',
   ''groups.name'']

• Luego revisé, si la consulta o mi clase no funciona en absoluto, usando un contador. Me levanté (cuenta == 5), así que los primeros dos se unieron a las tablas. Pero cuando configuré la condición como (contar == 6), recibí el primer mensaje de error nuevamente. AttributeError: el objeto ''JoinUserGroupPerson'' no tiene ningún atributo ''user_id'' .:

  list = []
  for rowX in query:
      for count, colX in enumerate(rowX.__table__.columns):
          list.append(getattr(rowX,colX.name))
          if count == 5:
              break
  return (u''{}''.format(list))
  
  
  """ RESULT:
  """
  
  
  [4, u''user real name'', None, None, 4, u''user'']
  
  
  """ which are these following six columns:
      persons[id, name, email, phone], users[id, login]
  """

• Luego revisé cada columna:

  list = []
  for rowX in query:
      for colX in rowX.__table__.columns:
          list.append(colX)
  return (u''{}''.format(list))
  
  
  """ RESULT:
  """
  
  
  [Column(u''id'', INTEGER(), table=, primary_key=True, nullable=False, server_default=DefaultClause(, for_update=False)),
   Column(u''name'', VARCHAR(length=252), table=, nullable=False),
   Column(u''email'', VARCHAR(), table=),
   Column(u''phone'', VARCHAR(), table=),
   Column(u''id'', INTEGER(), ForeignKey(u''persons.id''), table=, primary_key=True, nullable=False),
   Column(u''login'', VARCHAR(length=60), table=, nullable=False),
   Column(u''user_id'', INTEGER(), ForeignKey(u''users.id''), table=, primary_key=True, nullable=False),
   Column(u''group_id'', INTEGER(), ForeignKey(u''groups.id''), table=, primary_key=True, nullable=False),
   Column(u''id'', INTEGER(), table=, primary_key=True, nullable=False),
   Column(u''name'', VARCHAR(length=60), table=, nullable=False)]

• Luego probé otros dos accesos directos, que me dieron los dos KeyErrors para ''id'' y ''persons.id'':

  for rowX in query:
      return (u''{}''.format(rowX.__table__.columns[''id''].name))
  
  for rowX in query:
      return (u''{}''.format(rowX.__table__.columns[''persons.id''].name))

Conclusión

Probé algunas otras cosas, que fueron aún más confusas. Como no revelaron más información, no los agregué, y de todos modos, esta publicación ya es lo suficientemente larga. Realmente agradecería algo de ayuda con este asunto, porque no veo, donde mi clase está equivocada.

Supongo que, de alguna manera, debí haber configurado la clase de una manera que solo uniría correctamente las dos primeras tablas. Pero la unión funciona al menos parcialmente, porque cuando la tabla ''user_groups'' estaba vacía, recibí también una consulta vacía.

O tal vez hice algo mal con el mapeo de esta tabla ''user_groups''. Dado que con la unión algunas columnas son dobles, necesitan una definición adicional. Y el ''user_id'' ya es parte de la tabla de personas y usuarios, así que tuve que mapearlo dos veces.

Incluso traté de eliminar la tabla ''user_groups'' de la unión, porque está en las relaciones (con secundaria). Me consiguió un mensaje de error de clave externa. Pero tal vez lo hice mal.

Es cierto que ni siquiera sé por qué ...

rowX.__table__.columns                  # column names as table name suffix 

... tiene diferentes nombres de atributos que ...

colX in rowX.__table__.columns        # column names without table names

Así que por favor ayuda, gracias.

Ediciones adicionales

• ¡Otro pensamiento! ¿Sería todo esto posible con la herencia? Cada clase tiene su propia asignación, pero luego la clase user_groups puede ser necesaria. Las uniones tenían que ser entre las clases individuales en su lugar. El init () y repr () todavía tenían que redefinirse.

• Probablemente tiene algo que ver con la tabla ''user_groups'', porque ni siquiera pude unirla con la tabla ''groups'' o ''users''. Y siempre dice que el objeto de clase no tiene ningún atributo ''user_id''. Tal vez sea algo sobre la relación de muchos a muchos.

Adjunto archivo

Aquí está el módulo SQLAlchemy ya dado, con encabezado, sin información específica sobre la base de datos, y las clases de las tablas unidas:

#!/usr/bin/python
# vim: set fileencoding=utf-8 :

import sqlalchemy
from sqlalchemy import join
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref, column_property

pg = sqlalchemy.create_engine(
    ''postgresql://{}@{}:{}/{}''.format(user, host, port, data))

md = sqlalchemy.MetaData(pg, True)
Base = declarative_base()



""" ... following, three of the four joined tables.
    UserGroups isn''t necessary, so it wasn''t part of the module.
    And the other six classes shouldn''t be important for this ...
"""


class Person(Base):
    __table__ = md.tables[''persons'']

    def __init__(self, name, email=None, phone=None):
        self.name = name
        self.email = email
        self.phone = phone

    def __repr__(self):
        return(
            "<Person(%s, ''%s'', ''%s'', ''%s'')>" %(
            self.id, self.name, self.email, self.phone))

class Group(Base):
    __table__ = md.tables[''groups'']

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return("<Group(%s, ''%s'')>" %(self.id, self.name))

class User(Base):
    __table__ = md.tables[''users'']

    person = relationship(''Person'')
    groups = relationship(
        ''Group'', secondary=md.tables[''user_groups''], order_by=''Group.id'',
        backref=backref(''users'', order_by=''User.login''))

    def __init__(self, person, login):
        if isinstance(person, Person):
            self.person = person
        else:
            self.id = person
        self.login = login

    def __repr__(self):
        return("<User(%s, ''%s'')>" %(self.id, self.login))

Tal vez el siguiente script, que creó la base de datos, y también fue dado, será útil aquí. Como última parte de esto vienen algunos datos de prueba, pero entre las columnas se supone que son pestañas, no espacios. Por eso, este script también se puede encontrar como esencia en github :

-- file create_str.sql
-- database creation script
-- central script for creating all database objects

-- set the database name
/set strdbname logincore

/c admin

BEGIN;
/i str_roles.sql
COMMIT;

DROP DATABASE IF EXISTS :strdbname;
CREATE DATABASE :strdbname TEMPLATE template1 OWNER str_db_owner
    ENCODING ''UTF8'';
/c :strdbname

SET ROLE str_db_owner;

BEGIN;
/i str.sql
COMMIT;
RESET ROLE;





-- file str_roles.sql
-- create roles for the database

-- owner of the database objects
SELECT create_role(''str_db_owner'', ''NOINHERIT'');

-- role for using
SELECT create_role(''str_user'');

-- make str_db_owner member in all relevant roles
GRANT str_user TO str_db_owner WITH ADMIN OPTION;





-- file str.sql
-- creation of database

-- prototypes
/i str_prototypes.sql

-- domain for non empty text
CREATE DOMAIN ntext AS text CHECK (VALUE<>'''');

-- domain for email addresses
CREATE DOMAIN email AS varchar(252) CHECK (is_email_address(VALUE));

-- domain for phone numbers
CREATE DOMAIN phone AS varchar(60) CHECK (is_phone_number(VALUE));

-- persons
CREATE TABLE persons (
    id    serial       PRIMARY KEY,
    name  varchar(252) NOT NULL,
    email email,
    phone phone
);

GRANT SELECT, INSERT, UPDATE, DELETE ON persons TO str_user;
GRANT USAGE ON SEQUENCE persons_id_seq TO str_user;

CREATE TABLE groups (
    id   integer     PRIMARY KEY,
    name varchar(60) UNIQUE NOT NULL
);

GRANT SELECT ON groups TO str_user;

-- database users
CREATE TABLE users (
    id    integer     PRIMARY KEY REFERENCES persons(id) ON UPDATE CASCADE,
    login varchar(60) UNIQUE NOT NULL
);

GRANT SELECT ON users TO str_user;

-- user <-> groups
CREATE TABLE user_groups (
    user_id  integer NOT NULL REFERENCES users(id)
                              ON UPDATE CASCADE ON DELETE CASCADE,
    group_id integer NOT NULL REFERENCES groups(id)
                              ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (user_id, group_id)
);

-- functions
/i str_functions.sql





-- file str_prototypes.sql
-- prototypes for database

-- simple check for correct email address
CREATE FUNCTION is_email_address(email varchar) RETURNS boolean
    AS $CODE$
    SELECT FALSE
    $CODE$ LANGUAGE sql IMMUTABLE STRICT;

-- simple check for correct phone number
CREATE FUNCTION is_phone_number(nr varchar) RETURNS boolean
    AS $CODE$
    SELECT FALSE
    $CODE$ LANGUAGE sql IMMUTABLE STRICT;





-- file str_functions.sql
-- functions for database

-- simple check for correct email address
CREATE OR REPLACE FUNCTION is_email_address(email varchar) RETURNS boolean
    AS $CODE$
    SELECT $1 ~ E''^[A-Za-z0-9.!#$%&/'/*/+/-/=/?/^_/`{/|}/~/.]+@[-a-z0-9/.]+$''
    $CODE$ LANGUAGE sql IMMUTABLE STRICT;

-- simple check for correct phone number
CREATE OR REPLACE FUNCTION is_phone_number(nr varchar) RETURNS boolean
    AS $CODE$
    SELECT $1 ~ E''^[-+0-9/(/)/ ]+$''
    $CODE$ LANGUAGE sql IMMUTABLE STRICT;





-- file fill_str_test.sql
-- test data for database
-- between the columns are supposed to be tabs, no spaces !!!

BEGIN;

COPY persons (id, name, email) FROM STDIN;
1   Joseph Schneider    [email protected]
2   Test User   [email protected]
3   Hans Dampf  /N
/.
SELECT setval(''persons_id_seq'', (SELECT max(id) FROM persons));

COPY groups (id, name) FROM STDIN;
1   IT
2   SSG
/.

COPY users (id, login) FROM STDIN;
1   jschneid
2   tuser
3   dummy
/.

COPY user_groups (user_id, group_id) FROM STDIN;
1   1
2   1
3   2
/.

COMMIT;

Answer 1

En cuanto al KeyError : las cadenas que se imprimen en la repr del objeto __table__.columns NO son las teclas, y debido a que tiene múltiples columnas de id se está produciendo algún efecto de munging. Probablemente desee hacer "persons_id" lugar de "persons.id" pero le recomiendo imprimir __table__.columns.keys() para estar seguro.

En cuanto a AttributeError : SQLAlchemy asigna los nombres de las columnas directamente a los atributos de forma predeterminada, a menos que usted mismo defina asignaciones de atributos. El hecho de que esté definiendo el atributo id como column_property en column_property persons.c.id, users.c.id, user_groups.c.user_id significa que ninguna de esas columnas se está persons.c.id, users.c.id, user_groups.c.user_id directamente a un atributo en la clase ORM, pero todavía estarán en la colección de columns . Entonces, simplemente no puede usar columns como un iterable de nombres de atributos.

No reproduje todos sus códigos / datos, pero armé un caso de prueba más simple con 3 tablas (incluida una relación m2m) para verificar estos elementos.