def test_match_errors(sphinx_connections):
MockSphinxModel, session, sphinx_engine = sphinx_connections
query = session.query(MockSphinxModel.id)
with pytest.raises(CompileError):
query.filter(func.match(MockSphinxModel.name, "word1", "word2")).statement.compile(sphinx_engine)
with pytest.raises(CompileError):
query.filter(func.match()).statement.compile(sphinx_engine)
# not_ inside or_
with pytest.raises(CompileError, match='Invalid source'):
query.filter(or_(not_(MockSphinxModel.name), MockSphinxModel.country).match("US")).\
statement.compile(sphinx_engine)
# multi level or
with pytest.raises(CompileError, match='Invalid source'):
query.filter(or_(or_(MockSphinxModel.name, MockSphinxModel.country), MockSphinxModel.name).match("US")).\
statement.compile(sphinx_engine)
# invalid unary
with pytest.raises(CompileError, match='Invalid unary'):
query.filter(MockSphinxModel.name.asc().match("US")).\
statement.compile(sphinx_engine)
# and_
with pytest.raises(CompileError, match='Invalid boolean'):
query.filter(and_(MockSphinxModel.name, MockSphinxModel.country).match("US")).statement.compile(sphinx_engine)
# and_ inside not_
with pytest.raises(CompileError, match='Invalid boolean'):
query.filter(not_(and_(MockSphinxModel.name, MockSphinxModel.country)).match("US")).\
statement.compile(sphinx_engine)
def test_count(sphinx_connections):
MockSphinxModel, session, sphinx_engine = sphinx_connections
query = session.query(func.count(MockSphinxModel.id))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == 'SELECT COUNT(*) AS count_1 \nFROM mock_table'
query = session.query(func.count('*')).select_from(MockSphinxModel).filter(func.match("adriel"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT COUNT(*) AS count_1 \nFROM mock_table \nWHERE MATCH('adriel')"
def test_match(sphinx_connections):
MockSphinxModel, session, sphinx_engine = sphinx_connections
# One Match
query = session.query(MockSphinxModel.id)
query = query.filter(MockSphinxModel.name.match("adriel"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name adriel)')"
query = session.query(MockSphinxModel.id)
query = query.filter(func.match(MockSphinxModel.name, "adriel"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name adriel)')"
# Matching single columns
query = session.query(MockSphinxModel.id)
query = query.filter(MockSphinxModel.name.match("adriel"), MockSphinxModel.country.match("US"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name adriel) (@country US)')"
# Matching through functions
query = session.query(MockSphinxModel.id)
query = query.filter(func.match(MockSphinxModel.name, "adriel"), func.match(MockSphinxModel.country, "US"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name adriel) (@country US)')"
# Mixing and Matching
query = session.query(MockSphinxModel.id)
query = query.filter(func.match(MockSphinxModel.name, "adriel"), MockSphinxModel.country.match("US"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name adriel) (@country US)')"
# Match with normal filter
query = session.query(MockSphinxModel.id)
query = query.filter(func.match(MockSphinxModel.name, "adriel"), MockSphinxModel.country.match("US"),
MockSphinxModel.id == 1)
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name adriel) (@country US)') AND id = %s"
query = session.query(MockSphinxModel.id)
query = query.filter(func.random(MockSphinxModel.name))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE random(name)"
def test_match(sphinx_connections):
MockSphinxModel, session, sphinx_engine = sphinx_connections
base_query = session.query(MockSphinxModel.id)
# One Match
query = session.query(MockSphinxModel.id)
query = query.filter(MockSphinxModel.name.match("adriel"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name adriel)')"
query = session.query(MockSphinxModel.id)
query = query.filter(func.match(MockSphinxModel.name, "adriel"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name adriel)')"
# Escape quote
query = session.query(MockSphinxModel.id)
query = query.filter(func.match(MockSphinxModel.name, "adri'el"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name adri\\'el)')"
# Escape at symbol
query = session.query(MockSphinxModel.id)
query = query.filter(func.match(MockSphinxModel.name, "@username"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name \\\\@username)')"
# Escape multiple at symbols
query = session.query(MockSphinxModel.id)
query = query.filter(func.match(MockSphinxModel.name, "user @user @name"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name user \\\\@user \\\\@name)')"
# Escape brackets
query = session.query(MockSphinxModel.id)
query = query.filter(func.match(MockSphinxModel.name, "user )))("))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name user \\\\)\\\\)\\\\)\\\\()')"
# Function match all
query = session.query(MockSphinxModel.id)
query = query.filter(func.match("adriel"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('adriel')"
# Function match all with quote
query = session.query(MockSphinxModel.id)
query = query.filter(func.match("adri'el"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('adri\\'el')"
# Function match all with unicode
query = session.query(MockSphinxModel.id)
query = query.filter(func.match(u"miljøet"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == u"SELECT id \nFROM mock_table \nWHERE MATCH('miljøet')"
# Function match specific
query = session.query(MockSphinxModel.id)
query = query.filter(func.match("@name adriel"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('@name adriel')"
# Function match specific with quote
query = session.query(MockSphinxModel.id)
query = query.filter(func.match("@name adri'el"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('@name adri\\'el')"
# Function match specific with unicode
query = session.query(MockSphinxModel.id)
query = query.filter(func.match(u"@name miljøet"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == u"SELECT id \nFROM mock_table \nWHERE MATCH('@name miljøet')"
# Matching single columns
query = session.query(MockSphinxModel.id)
query = query.filter(MockSphinxModel.name.match("adriel"), MockSphinxModel.country.match("US"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name adriel) (@country US)')"
# Matching single columns with quotes
query = session.query(MockSphinxModel.id)
query = query.filter(MockSphinxModel.name.match("adri'el"), MockSphinxModel.country.match("US"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name adri\\'el) (@country US)')"
# Matching single columns with at symbol
query = session.query(MockSphinxModel.id)
query = query.filter(MockSphinxModel.name.match("@username"), MockSphinxModel.country.match("US"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name \\\\@username) (@country US)')"
# Matching single columns with multiple at symbols
query = session.query(MockSphinxModel.id)
query = query.filter(MockSphinxModel.name.match("user @user @name"), MockSphinxModel.country.match("US"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name user \\\\@user \\\\@name) (@country US)')"
# Matching single columns with brackets
query = session.query(MockSphinxModel.id)
query = query.filter(MockSphinxModel.name.match("user )))("), MockSphinxModel.country.match("US"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name user \\\\)\\\\)\\\\)\\\\() (@country US)')"
# Matching through functions
query = session.query(MockSphinxModel.id)
query = query.filter(func.match(MockSphinxModel.name, "adriel"), func.match(MockSphinxModel.country, "US"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name adriel) (@country US)')"
# Matching with not_
base_expression = not_(MockSphinxModel.country)
for expression in (base_expression.match("US"), func.match(base_expression, "US")):
query = base_query.filter(expression)
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@!country US)')"
# Matching multiple columns with or_
base_expression = or_(MockSphinxModel.name, MockSphinxModel.country)
for expression in (base_expression.match("US"), func.match(base_expression, "US")):
query = base_query.filter(expression)
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@(name,country) US)')"
# Matching multiple columns with or_ and not_ through functions
base_expression = not_(or_(MockSphinxModel.name, MockSphinxModel.country))
for expression in (base_expression.match("US"), func.match(base_expression, "US")):
query = base_query.filter(expression)
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@!(name,country) US)')"
# Mixing and Matching
query = session.query(MockSphinxModel.id)
query = query.filter(func.match(MockSphinxModel.name, "adriel"), MockSphinxModel.country.match("US"))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name adriel) (@country US)')"
# Match with normal filter
query = session.query(MockSphinxModel.id)
query = query.filter(func.match(MockSphinxModel.name, "adriel"), MockSphinxModel.country.match("US"),
MockSphinxModel.id == 1)
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE MATCH('(@name adriel) (@country US)') AND id = %s"
# Match with normal filter with unicode
query = session.query(MockSphinxModel.id)
query = query.filter(func.match(MockSphinxModel.name, u"miljøet"), MockSphinxModel.country.match("US"),
MockSphinxModel.id == 1)
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == u"SELECT id \nFROM mock_table \nWHERE MATCH('(@name miljøet) (@country US)') AND id = %s"
query = session.query(MockSphinxModel.id)
query = query.filter(func.random(MockSphinxModel.name))
sql_text = query.statement.compile(sphinx_engine).string
assert sql_text == "SELECT id \nFROM mock_table \nWHERE random(name)"
Base = declarative_base()
Session = sessionmaker(engine)
session = Session()
class Tweet(Base):
__tablename__ = 'tweets'
id = sa.Column(sa.String, primary_key=True)
created_at = sa.Column(sa.DateTime)
text = sa.Column(sa.String)
source = sa.Column(sa.String)
retweeted = sa.Column(sa.Boolean)
user = sa.Column(Object)
def __repr__(self):
return '<Tweet {0}>'.format(self.user.get('id'))
tweets = session.query(Tweet)
tweets_austria = session.query(Tweet).filter(func.match(Tweet.text, 'Austria'))
group_by_followers_count = session\
.query(Tweet.user['followers_count'], func.count(Tweet.user['followers_count']))\
.group_by(Tweet.user['followers_count'])\
.order_by(desc(func.count(Tweet.user['followers_count'])))
from IPython import embed
embed()