Das deutsche Python-Forum

Code: Alles auswählen

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *


engine = create_engine('sqlite:///metadata.db', echo=True)

Base = declarative_base()


books_authors_link = Table('books_authors_link', Base.metadata,
	Column('book', Integer, ForeignKey('books.id'), primary_key=True),
	Column('author', Integer, ForeignKey('authors.id'), primary_key=True)
	)


class Authors(Base):
	__tablename__ = 'authors'

	id = Column(Integer, primary_key=True)
	name = Column(String)
	sort = Column(String)
	link = Column(String)

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

	def __repr__(self):
		return u"<Authors('{0},{1}{2}')>".format(self.name, self.sort, self.link)

class Books(Base):
	__tablename__ = 'books'

	id = Column(Integer,primary_key=True)
	title = Column(String)
	sort = Column(String)
	path = Column(String)
	has_cover = Column(Integer)

	authors = relationship('Authors', secondary=books_authors_link, backref='books')

	def __init__(self, title, sort, path, has_cover, authors, tags):
		self.title = title
		self.sort = sort
		self.path = path
		self.has_cover = has_cover
		self.tags = tags


	def __repr__(self):
		return u"<Books('{0},{1}{2}{3}')>".format(self.title, self.sort, self.path, self.has_cover)

Base.metadata.create_all(engine)
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()

result = session.query(Books).filter(Books.authors.like('Charlotte Link')).first() 
print result.title

Code: Alles auswählen

result = session.query(Books).filter(Books.authors.like('%Link%')).first() 

Code: Alles auswählen

  File "build/bdist.macosx-10.6-intel/egg/sqlalchemy/sql/operators.py", line 267, in like
  File "build/bdist.macosx-10.6-intel/egg/sqlalchemy/orm/attributes.py", line 123, in operate
  File "build/bdist.macosx-10.6-intel/egg/sqlalchemy/sql/operators.py", line 499, in like_op
  File "build/bdist.macosx-10.6-intel/egg/sqlalchemy/sql/operators.py", line 267, in like
  File "build/bdist.macosx-10.6-intel/egg/sqlalchemy/sql/operators.py", line 148, in operate
NotImplementedError: <function like_op at 0x1007bb140>

Code: Alles auswählen

result = session.query(Books).filter(Books.authors.any(name='Charlotte Link')).first() 

Code: Alles auswählen

result = session.query(Books).join(Authors).filter(Authors.name.like('%Link%')).first() 

Code: Alles auswählen

sqlalchemy.exc.InvalidRequestError: Could not find a FROM clause to join from.  Tried joining to <class '__main__.Books'>, but got: Can't find any foreign key relationships between 'authors' and 'books'.

Code: Alles auswählen

result = session.query(Books).filter(Books.authors.name.like('Charlotte Link')).first()

Code: Alles auswählen

result = (
    session.query(Books)
        .filter(Books.authors.any(Authors.name.like('%Link%')))
        .first()
)

Code: Alles auswählen

SELECT books.id AS books_id, books.title AS books_title,
    books.sort AS books_sort, books.path AS books_path,
    books.has_cover AS books_has_cover 
 FROM books 
WHERE EXISTS (
    SELECT 1 
      FROM books_authors_link, authors 
     WHERE books.id = books_authors_link.book
       AND authors.id = books_authors_link.author
       AND authors.name LIKE '%Link%'
)
LIMIT 1
OFFSET 0

Code: Alles auswählen

result = (
    session.query(Books)
        .filter(Books.authors.any(Authors.name.like('%Link%')))
        .first()
)