def test_render_statement(self, session, User):
statement = User.__table__.select().where(User.id == 3)
text = render_statement(statement, bind=session.bind)
assert 'SELECT user.id, user.name' in text
assert 'FROM user' in text
assert 'WHERE user.id = 3' in text
def test_render_orm_query(self, session, User):
query = session.query(User).filter_by(id=3)
text = render_statement(query)
assert 'SELECT user.id, user.name' in text
assert 'FROM user' in text
assert 'WHERE user.id = 3' in text
def test_render_statement(self):
statement = self.User.__table__.select().where(self.User.id == 3)
render = render_statement(statement, bind=self.session.bind)
assert 'SELECT user.id, user.name' in render
assert 'FROM user' in render
assert 'WHERE user.id = 3' in render
def test_render_statement_query(self):
query = self.session.query(self.User).filter_by(id=3)
render = render_statement(query)
assert 'SELECT user.id, user.name' in render
assert 'FROM user' in render
assert 'WHERE user.id = 3' in render
def render_query(statement, bind=None, reindent=True):
"""Generate an SQL expression string with bound parameters rendered inline
for the given SQLAlchemy statement.
The function can also receive a `sqlalchemy.orm.Query` object instead of
statement.
"""
from sqlalchemy_utils.functions import render_statement
raw_sql = render_statement(statement, bind)
try: # pragma: no cover
import sqlparse
return sqlparse.format(raw_sql, reindent=reindent)
except ImportError: # pragma: no cover
return raw_sql
def clone_db(ctx, ignored_tables=()):
message = ' ~> `%s` to `%s` database' % (ctx.current_db_name,
ctx.archive_db_name)
ctx.log(green(' clone') + message)
if ctx.current_db.dialect == 'postgresql':
ctx.current_db.session.connection().connection.set_isolation_level(0)
ctx.current_db.session.execute(
'CREATE DATABASE "%s" WITH TEMPLATE "%s";'
% (ctx.archive_db_name, ctx.current_db_name)
)
ctx.current_db.session.connection().connection.set_isolation_level(1)
elif ctx.current_db.dialect == 'mysql':
# Horribly slow implementation.
create_database_if_not_exists(ctx, ctx.archive_db.engine)
show_tables_query = 'SHOW TABLES in %s;' % ctx.current_db_name
for row in ctx.current_db.session.execute(show_tables_query):
name = row[0]
ctx.current_db.session.execute(
'CREATE TABLE %s.%s LIKE %s.%s'
% (ctx.archive_db_name, name, ctx.current_db_name, name)
)
if name in ignored_tables:
ctx.log(yellow(' ignore') + ' ~> table %s' % name)
continue
ctx.current_db.session.execute(
'ALTER TABLE %s.%s DISABLE KEYS' % (ctx.archive_db_name, name)
)
table = reflect_table(ctx.current_db.engine, name)
criterion = get_jobs_sync_criterion(ctx, table)
query = select([table])
if criterion:
query = query.where(reduce(and_, criterion))
raw_sql = render_statement(query, ctx.current_db.engine)
raw_sql = raw_sql.replace(";", "").replace("\n", "")
matcher = re.compile(r'SELECT(.*?)FROM', re.IGNORECASE | re.DOTALL)
result = matcher.search(raw_sql)
if result:
raw_sql = raw_sql.replace(result.group(1), " * ")
ctx.current_db.session.execute(
'INSERT INTO %s.%s (%s)' % (ctx.archive_db_name, name, raw_sql)
)
ctx.current_db.session.execute(
'ALTER TABLE %s.%s ENABLE KEYS' % (ctx.archive_db_name, name)
)
else:
raise NotSupportedDatabase()
def test_render_statement_without_mapper(self, session):
statement = sa.select([sa.text('1')])
text = render_statement(statement, bind=session.bind)
assert 'SELECT 1' in text