Skip to content

Instantly share code, notes, and snippets.

@nenodias
Last active March 4, 2016 18:29
Show Gist options
  • Save nenodias/d15e08d998c267cf0c8e to your computer and use it in GitHub Desktop.
Save nenodias/d15e08d998c267cf0c8e to your computer and use it in GitHub Desktop.
Sql Alchemy Mapping Example
# *-* coding: utf-8 *-*
from sqlalchemy import Table, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.types import Enum
''' Session '''
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.pool import StaticPool
''' Meta '''
from sqlalchemy.schema import MetaData
Base = declarative_base()
engine = create_engine('sqlite:///:memory:',
connect_args={'check_same_thread':False},
pool_size=20)
DATABASE_NAME = "banco"
#engine = create_engine('sqlite:///banco.db')
#engine = create_engine('sqlite:///foo.db')
Session = sessionmaker(bind=engine)
session = Session()
'''
Exemplo de OneToMany
'''
class Usuario(Base):
__tablename__ = 'usuario'
id = Column(Integer, primary_key=True)
nome = Column(String(100) )
#enderecos = relationship("Endereco", backref="usuario") usando backref ao invés de backref
enderecos = relationship("Endereco", back_populates="usuario")
class Endereco(Base):
__tablename__ = 'endereco'
id = Column(Integer, primary_key=True)
descricao = Column(String(100) )
usuario_id = Column(Integer, ForeignKey('usuario.id'))
usuario = relationship("Usuario", back_populates="enderecos")
'''
Exemplos OneToOne
'''
class Pessoa(Base):
__tablename__ = 'pessoa'
id = Column(Integer, primary_key=True)
nome = Column(String(100) )
dados_pessoais = relationship("DadosPessoais", uselist=False, backref="pessoa")
class DadosPessoais(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
descricao = Column(String(100) )
pessoa_id = Column(Integer, ForeignKey('pessoa.id'))
'''
Exemplo ManyToMany
'''
tabela_associacao = Table('association', Base.metadata,
Column('funcionario_id', Integer, ForeignKey('funcionario.id')),
Column('funcao_id', Integer, ForeignKey('funcao.id'))
)
class Funcionario(Base):
__tablename__ = 'funcionario'
id = Column(Integer, primary_key=True)
nome = Column(String(100) )
funcoes = relationship("Funcao", secondary=tabela_associacao, back_populates="funcionarios")
class Funcao(Base):
__tablename__ = 'funcao'
id = Column(Integer, primary_key=True)
descricao = Column(String(100) )
funcionarios = relationship("Funcionario", secondary=tabela_associacao, back_populates="funcoes")
'''
Exemplo de ENUM
'''
class TipoClienteEnum():
_type = str
bom = "bom"
caloteiro = "caloteiro"
@classmethod
def get_values(cls):
return [i for i in cls.__dict__.values() if isinstance(i, cls._type) and not i.startswith('_')]
class Cliente(Base):
__tablename__ = 'cliente'
id = Column(Integer, primary_key=True)
nome = Column(String(100) )
tipo = Column('value', Enum(*TipoClienteEnum.get_values(), name="tipo"))
if __name__ == '__main__':
Base.metadata.create_all(engine)
conn = engine.connect()
usuario = Usuario(nome="Horácio")
endereco1 = Endereco(descricao="Rua dos Bobos, 0")
endereco2 = Endereco(descricao="Rua Luiz Trecenti, 70")
endereco1.usuario = usuario
endereco2.usuario = usuario
# Save or Update
session.add(usuario)
# Presica que as tabelas existam
session.commit()
funcionario1 = Funcionario(nome="Vagal")
funcionario2 = Funcionario(nome="Empenhado")
funcao1 = Funcao(descricao="Coçar")
funcao2 = Funcao(descricao="Trabalhar")
funcionario1.funcoes.append(funcao1)
funcionario1.funcoes.append(funcao2)
funcionario2.funcoes.append(funcao2)
print( funcionario1.funcoes )
print( funcionario2.funcoes )
session.commit()
print( funcao1.funcionarios )
print( funcao2.funcionarios )
print(TipoClienteEnum.get_values())
cliente = Cliente(nome="Cliente 1", tipo=TipoClienteEnum.bom)
session.add(cliente)
clientes = session.query(Cliente).all()
print(clientes[0].tipo)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment