def test_endswith_text_mysql(self):
self.assert_compile(
column('x').endswith(text('y')),
"x LIKE concat('%%', y)",
checkparams={},
dialect=mysql.dialect()
)
def test_not_startswith_concat(self):
self.assert_compile(
~column("x").startswith("y"),
"x NOT LIKE concat(%s, '%%')",
checkparams={"x_1": "y"},
dialect=mysql.dialect(),
)
def test_not_contains_concat(self):
self.assert_compile(
~column("x").contains("y"),
"x NOT LIKE concat(concat('%%', %s), '%%')",
checkparams={"x_1": "y"},
dialect=mysql.dialect(),
)
def test_contains_literal_concat(self):
self.assert_compile(
column("x").contains(literal_column("y")),
"x LIKE concat(concat('%%', y), '%%')",
checkparams={},
dialect=mysql.dialect(),
)
def test_update_bound_ordering(self):
"""test that bound parameters between the UPDATE and FROM clauses
order correctly in different SQL compilation scenarios.
"""
table1 = self.tables.mytable
table2 = self.tables.myothertable
sel = select([table2]).where(table2.c.otherid == 5).alias()
upd = table1.update().where(table1.c.name == sel.c.othername).values(name="foo")
dialect = default.DefaultDialect()
dialect.positional = True
self.assert_compile(
upd,
"UPDATE mytable SET name=:name FROM (SELECT "
"myothertable.otherid AS otherid, "
"myothertable.othername AS othername "
"FROM myothertable "
"WHERE myothertable.otherid = :otherid_1) AS anon_1 "
"WHERE mytable.name = anon_1.othername",
checkpositional=("foo", 5),
dialect=dialect,
)
self.assert_compile(
upd,
"UPDATE mytable, (SELECT myothertable.otherid AS otherid, "
"myothertable.othername AS othername "
"FROM myothertable "
"WHERE myothertable.otherid = %s) AS anon_1 SET mytable.name=%s "
"WHERE mytable.name = anon_1.othername",
checkpositional=(5, "foo"),
dialect=mysql.dialect(),
)
def left_choice(self, evt):
"""Sets the left column and updates the available columns in the right choice"""
choice = self.panel.choiceLeft.GetCurrentSelection()
if choice == 0:
self.workingJoin['tableValue'] = None
self.workingJoin['joiningValue'] = None
self.panel.choiceRight.Clear()
self.rightSelections = ['Choose a compatible column...']
self.panel.choiceRight.Append(self.rightSelections[0])
for i in self.rightSelectionsTypes:
self.rightSelections.append((i[0], i[2]))
self.panel.choiceRight.Append(i[0])
self.panel.rightChoice.SetSelection(0)
self.panel.btnOk.Enable(False)
else:
#set values
self.workingJoin['tableValue'] = self.leftColumnNames[choice]
self.workingJoin['joiningValue'] = None
#load up compatible columns into right hand side
choiceType = self.leftSelectionsTypes[choice]
self.rightSelections = ['Choose a compatible column...']
for k in self.rightSelectionsTypes[1:]:
#run through columns and check if types are the same, some columns are MySQL types
#and non-standard SQL types, thus the dialect type checking.
if str(k[1].compile(dialect=mysql.dialect())) == str(choiceType.compile(dialect=mysql.dialect())):
self.rightSelections.append((k[0], k[2]))
self.panel.choiceRight.Clear()
self.panel.choiceRight.Append(self.rightSelections[0])
for i in self.rightSelections[1:]:
self.panel.choiceRight.Append(i[0])
self.panel.choiceRight.SetSelection(0)
self.panel.btnOk.Enable(False)
def test_startswith_literal_mysql(self):
self.assert_compile(
column('x').startswith(literal_column('y')),
"x LIKE concat(y, '%%')",
checkparams={},
dialect=mysql.dialect()
)
def test_not_endswith_mysql(self):
self.assert_compile(
~column('x').endswith('y'),
"x NOT LIKE concat('%%', %s)",
checkparams={'x_1': 'y'},
dialect=mysql.dialect()
)
def test_not_startswith_concat(self):
self.assert_compile(
~column('x').startswith('y'),
"x NOT LIKE concat(%s, '%%')",
checkparams={'x_1': 'y'},
dialect=mysql.dialect()
)
def test_mariadb_normalized_version(self):
for expected, raw_version, version, is_mariadb in [
((10, 2, 7), "10.2.7-MariaDB", (10, 2, 7, "MariaDB"), True),
(
(10, 2, 7),
"5.6.15.10.2.7-MariaDB",
(5, 6, 15, 10, 2, 7, "MariaDB"),
True,
),
((10, 2, 10), "10.2.10-MariaDB", (10, 2, 10, "MariaDB"), True),
((5, 7, 20), "5.7.20", (5, 7, 20), False),
((5, 6, 15), "5.6.15", (5, 6, 15), False),
(
(10, 2, 6),
"10.2.6.MariaDB.10.2.6+maria~stretch-log",
(10, 2, 6, "MariaDB", 10, 2, "6+maria~stretch", "log"),
True,
),
(
(10, 1, 9),
"10.1.9-MariaDBV1.0R050D002-20170809-1522",
(10, 1, 9, "MariaDB", "V1", "0R050D002", 20170809, 1522),
True,
),
]:
dialect = mysql.dialect()
eq_(dialect._parse_server_version(raw_version), version)
dialect.server_version_info = version
eq_(dialect._mariadb_normalized_version_info, expected)
assert dialect._is_mariadb is is_mariadb
def test_contains_text_concat(self):
self.assert_compile(
column('x').contains(text('y')),
"x LIKE concat(concat('%%', y), '%%')",
checkparams={},
dialect=mysql.dialect()
)
def test_not_contains_concat(self):
self.assert_compile(
~column('x').contains('y'),
"x NOT LIKE concat(concat('%%', %s), '%%')",
checkparams={'x_1': 'y'},
dialect=mysql.dialect()
)
def test_repr_plain_sqla_type(self):
type_ = Integer()
autogen_context = {
"opts": {"sqlalchemy_module_prefix": "sa.", "alembic_module_prefix": "op."},
"dialect": mysql.dialect(),
}
eq_ignore_whitespace(autogenerate.render._repr_type(type_, autogen_context), "sa.Integer()")
def setup_class(cls):
cls.autogen_context = {
'opts':{
'sqlalchemy_module_prefix' : 'sa.',
'alembic_module_prefix' : 'op.',
},
'dialect':mysql.dialect()
}
def setup_class(cls):
cls.autogen_context = {
"opts": {"sqlalchemy_module_prefix": "sa.", "alembic_module_prefix": "op."},
"dialect": mysql.dialect(),
}
cls.pg_autogen_context = {
"opts": {"sqlalchemy_module_prefix": "sa.", "alembic_module_prefix": "op."},
"dialect": postgresql.dialect(),
}
def test_generic_now(self):
assert isinstance(func.now().type, sqltypes.DateTime)
for ret, dialect in [
('CURRENT_TIMESTAMP', sqlite.dialect()),
('now()', postgresql.dialect()),
('now()', mysql.dialect()),
('CURRENT_TIMESTAMP', oracle.dialect())
]:
self.assert_compile(func.now(), ret, dialect=dialect)
def test_generic_random(self):
assert func.random().type == sqltypes.NULLTYPE
assert isinstance(func.random(type_=Integer).type, Integer)
for ret, dialect in [
('random()', sqlite.dialect()),
('random()', postgresql.dialect()),
('rand()', mysql.dialect()),
('random()', oracle.dialect())
]:
self.assert_compile(func.random(), ret, dialect=dialect)
def test_prefix_with(self):
table1 = self.tables.mytable
stmt = table1.delete().\
prefix_with('A', 'B', dialect='mysql').\
prefix_with('C', 'D')
self.assert_compile(stmt, 'DELETE C D FROM mytable')
self.assert_compile(stmt,
'DELETE A B C D FROM mytable',
dialect=mysql.dialect())
def clean_allocations(instance_uuid, hypervisor, connection=None,
debug=False, dry_run=True):
"""clean up multiple allocations in nova_api db for instance
:param str instance_uuid: instance uuid
:param str hypervisor: hypervisor running the instance
:param str connection: specify db connection string
:param bool debug (False): show sql calls
:param bool dry_run (True): commit changes to the db
"""
connection = get_connection(connection)
engine = connect(connection, debug)
db_conn = engine.connect()
alloc_tb = Table('allocations', metadata,
autoload=True, autoload_with=engine)
res_tb = Table('resource_providers', metadata,
autoload=True, autoload_with=engine)
query = select(
[alloc_tb.c.id,
alloc_tb.c.consumer_id,
alloc_tb.c.resource_provider_id,
res_tb.c.name]).select_from(
alloc_tb.join(
res_tb,
alloc_tb.c.resource_provider_id == res_tb.c.id)).where(
and_(res_tb.c.name != hypervisor,
alloc_tb.c.consumer_id == instance_uuid))
results = db_conn.execute(query).fetchall()
if not results:
print("multiple allocation records not found for %s\n" % instance_uuid)
return
ids = [r[0] for r in results]
if dry_run:
print("extra allocations found:")
print_table(results)
stmt = alloc_tb.delete().where(alloc_tb.c.id.in_(ids))
print('would execute statement:')
print(str(stmt.compile(
dialect=mysql.dialect(),
compile_kwargs={"literal_binds": True})))
print("run with --no-dry-run to commit changes to the db")
else:
print("deleting extra allocations:")
print_table(results)
stmt = alloc_tb.delete().where(alloc_tb.c.id.in_(ids))
db_conn.execute(stmt)
print("remaining allocations:")
show_allocations(instance_uuid)
def test_prefix_with(self):
table1 = self.tables.mytable
stmt = (table1.delete().prefix_with("A", "B",
dialect="mysql").prefix_with(
"C", "D"))
self.assert_compile(stmt, "DELETE C D FROM mytable")
self.assert_compile(stmt,
"DELETE A B C D FROM mytable",
dialect=mysql.dialect())
def test_render_table_mysql(self):
users, addresses = self.tables.users, self.tables.addresses
self.assert_compile(
users.update().\
values(name='newname').\
where(users.c.id==addresses.c.user_id).\
where(addresses.c.email_address=='e1'),
"UPDATE users, addresses SET users.name=%s "
"WHERE users.id = addresses.user_id AND "
"addresses.email_address = %s",
checkparams={u'email_address_1': 'e1', 'name': 'newname'},
dialect=mysql.dialect()
)
def get_compiled_raw_mysql(query):
"""
# chú ý: muốn chạy get_compiled_raw_mysql phải bỏ .all() trong query
:param cmd: SQLAlchemy query or statement
:rtype: str
"""
if hasattr(query, 'statement'):
stmt = query.statement
else:
stmt = query
return stmt.compile(dialect=mysql.dialect(),
compile_kwargs={"literal_binds": True})
def test_prefix_with(self):
table1 = self.tables.mytable
stmt = table1.delete().\
prefix_with('A', 'B', dialect='mysql').\
prefix_with('C', 'D')
self.assert_compile(stmt,
'DELETE C D FROM mytable')
self.assert_compile(stmt,
'DELETE A B C D FROM mytable',
dialect=mysql.dialect())
def test_mariadb_normalized_version(self):
for expected, version in [
((10, 2, 7), (10, 2, 7, 'MariaDB')),
((10, 2, 7), (5, 6, 15, 10, 2, 7, 'MariaDB')),
((10, 2, 10), (10, 2, 10, 'MariaDB')),
((5, 7, 20), (5, 7, 20)),
((5, 6, 15), (5, 6, 15)),
((10, 2, 6), (10, 2, 6, 'MariaDB', 10, 2, '6+maria~stretch',
'log')),
]:
dialect = mysql.dialect()
dialect.server_version_info = version
eq_(dialect._mariadb_normalized_version_info, expected)
def find_prescription_details(pres):
session = get_local_session()[1]
logging.info("从中间表查询处方详情：")
pres_detail_query = session.query(PresDetails).filter(
PresDetails.pres_num == pres.pres_num)
sql = str(
pres_detail_query.statement.compile(
dialect=mysql.dialect(), compile_kwargs={"literal_binds": True}))
logging.info('==> executing:%s' % sql)
pres_detail_res = pres_detail_query.all()
logging.info("==> Parameters:%s" % pres_detail_res)
session.close()
return pres_detail_res
class MySQLJDBC(JDBC):
DRIVER = 'org.mariadb.jdbc.Driver'
JDBC_NAME = 'jdbc:mariadb'
DIALECT: Dialect = mysql.dialect(paramstyle='named')
@staticmethod
def reset_generated_id(cursor: jaydebeapi.Cursor) -> None:
cursor.execute('SELECT LAST_INSERT_ID(NULL)')
@staticmethod
def last_generated_id(cursor: jaydebeapi.Cursor) -> int:
cursor.execute("SELECT LAST_INSERT_ID()")
return int(str(cursor.fetchone()[0]))
async def fetchall(self, query, args=None):
if not isinstance(query, str):
query = str(query.compile(dialect=mysql.dialect()))
pool = await self.get_mysql_pool()
async with pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(query, args)
result = await cur.fetchall()
if cur.description is not None:
columns = [i[0] for i in cur.description]
return [dict(zip((c for c in columns), row)) for row in result]
else:
return []
class DummyResource(Resource):
DIALECT = mysql.dialect(paramstyle='named')
@classmethod
def create_connection_maker(
cls,
host: Optional[str] = None,
port: Optional[int] = None,
user_name: Optional[str] = None,
password: Optional[str] = None,
engine_kwargs: Dict[str, Any] = None,
) -> ConnectionMaker:
pass
def test_render_table_mysql(self):
users, addresses = self.tables.users, self.tables.addresses
self.assert_compile(
users.update().\
values(name='newname').\
where(users.c.id==addresses.c.user_id).\
where(addresses.c.email_address=='e1'),
"UPDATE users, addresses SET users.name=%s "
"WHERE users.id = addresses.user_id AND "
"addresses.email_address = %s",
checkparams={u'email_address_1': 'e1', 'name': 'newname'},
dialect=mysql.dialect()
)
def test_update_from_join_mysql_no_whereclause_two(self):
users, addresses = self.tables.users, self.tables.addresses
j = users.join(addresses)
self.assert_compile(
update(j).values({users.c.name: addresses.c.email_address}),
""
"UPDATE users "
"INNER JOIN addresses ON users.id = addresses.user_id "
"SET users.name=addresses.email_address",
checkparams={},
dialect=mysql.dialect(),
)
def test_update_from_join_mysql_no_whereclause_one(self):
users, addresses = self.tables.users, self.tables.addresses
j = users.join(addresses)
self.assert_compile(
update(j).values(name="newname"),
""
"UPDATE users "
"INNER JOIN addresses ON users.id = addresses.user_id "
"SET users.name=%s",
checkparams={"name": "newname"},
dialect=mysql.dialect(),
)
def test_repr_plain_sqla_type(self):
type_ = Integer()
autogen_context = {
'opts': {
'sqlalchemy_module_prefix': 'sa.',
'alembic_module_prefix': 'op.',
},
'dialect': mysql.dialect()
}
eq_ignore_whitespace(
autogenerate.render._repr_type(type_, autogen_context),
"sa.Integer()")
def test_repr_plain_sqla_type(self):
type_ = Integer()
autogen_context = {
'opts': {
'sqlalchemy_module_prefix': 'sa.',
'alembic_module_prefix': 'op.',
},
'dialect': mysql.dialect()
}
eq_ignore_whitespace(
autogenerate.render._repr_type(type_, autogen_context),
"sa.Integer()"
)
def test_mysql_variants(self):
self.assertEqual(
"LONGTEXT",
str(
types.JsonEncodedDict(mysql_as_long=True).compile(
dialect=mysql.dialect())
)
)
self.assertEqual(
"MEDIUMTEXT",
str(
types.JsonEncodedDict(mysql_as_medium=True).compile(
dialect=mysql.dialect())
)
)
self.assertRaises(
TypeError,
lambda: types.JsonEncodedDict(
mysql_as_long=True,
mysql_as_medium=True)
)
def test_prefix_with(self):
table1 = self.tables.mytable
stmt = table1.insert().\
prefix_with('A', 'B', dialect='mysql').\
prefix_with('C', 'D')
self.assert_compile(stmt,
'INSERT C D INTO mytable (myid, name, description) '
'VALUES (:myid, :name, :description)')
self.assert_compile(stmt,
'INSERT A B C D INTO mytable (myid, name, description) '
'VALUES (%s, %s, %s)', dialect=mysql.dialect())
def test_prefix_with(self):
table1 = self.tables.mytable
stmt = table1.update().\
prefix_with('A', 'B', dialect='mysql').\
prefix_with('C', 'D')
self.assert_compile(stmt,
'UPDATE C D mytable SET myid=:myid, name=:name, '
'description=:description')
self.assert_compile(stmt,
'UPDATE A B C D mytable SET myid=%s, name=%s, description=%s',
dialect=mysql.dialect())
def test_prefix_with(self):
table1 = self.tables.mytable
stmt = (
table1.delete()
.prefix_with("A", "B", dialect="mysql")
.prefix_with("C", "D")
)
self.assert_compile(stmt, "DELETE C D FROM mytable")
self.assert_compile(
stmt, "DELETE A B C D FROM mytable", dialect=mysql.dialect()
)
def test_repr_dialect_type(self):
from sqlalchemy.dialects.mysql import VARCHAR
type_ = VARCHAR(20, charset="utf8", national=True)
autogen_context = {
"opts": {"sqlalchemy_module_prefix": "sa.", "alembic_module_prefix": "op.", "user_module_prefix": None},
"imports": set(),
"dialect": mysql.dialect(),
}
eq_ignore_whitespace(
autogenerate.render._repr_type(type_, autogen_context),
"mysql.VARCHAR(charset='utf8', national=True, length=20)",
)
eq_(autogen_context["imports"], set(["from sqlalchemy.dialects import mysql"]))
def test_repr_user_type_user_prefix_present(self):
from sqlalchemy.types import UserDefinedType
class MyType(UserDefinedType):
def get_col_spec(self):
return "MYTYPE"
type_ = MyType()
autogen_context = {
"opts": {"sqlalchemy_module_prefix": "sa.", "alembic_module_prefix": "op.", "user_module_prefix": "user."},
"dialect": mysql.dialect(),
}
eq_ignore_whitespace(autogenerate.render._repr_type(type_, autogen_context), "user.MyType()")
def test_prefix_with(self):
table1 = self.tables.mytable
stmt = table1.insert().\
prefix_with('A', 'B', dialect='mysql').\
prefix_with('C', 'D')
self.assert_compile(stmt,
'INSERT C D INTO mytable (myid, name, description) '
'VALUES (:myid, :name, :description)')
self.assert_compile(stmt,
'INSERT A B C D INTO mytable (myid, name, description) '
'VALUES (%s, %s, %s)', dialect=mysql.dialect())
def test_mysql_variants(self):
self.assertEqual(
"LONGTEXT",
str(
types.JsonEncodedDict(mysql_as_long=True).compile(
dialect=mysql.dialect())
)
)
self.assertEqual(
"MEDIUMTEXT",
str(
types.JsonEncodedDict(mysql_as_medium=True).compile(
dialect=mysql.dialect())
)
)
self.assertRaises(
TypeError,
lambda: types.JsonEncodedDict(
mysql_as_long=True,
mysql_as_medium=True)
)
def test_mariadb_normalized_version(self):
for expected, version in [
((10, 2, 7), (10, 2, 7, "MariaDB")),
((10, 2, 7), (5, 6, 15, 10, 2, 7, "MariaDB")),
((10, 2, 10), (10, 2, 10, "MariaDB")),
((5, 7, 20), (5, 7, 20)),
((5, 6, 15), (5, 6, 15)),
(
(10, 2, 6),
(10, 2, 6, "MariaDB", 10, 2, "6+maria~stretch", "log"),
),
]:
dialect = mysql.dialect()
dialect.server_version_info = version
eq_(dialect._mariadb_normalized_version_info, expected)
def setup_class(cls):
cls.autogen_context = {
'opts': {
'sqlalchemy_module_prefix': 'sa.',
'alembic_module_prefix': 'op.',
},
'dialect': mysql.dialect()
}
cls.pg_autogen_context = {
'opts': {
'sqlalchemy_module_prefix': 'sa.',
'alembic_module_prefix': 'op.',
},
'dialect': postgresql.dialect()
}
def test_mariadb_normalized_version(self):
for expected, version in [
((10, 2, 7), (10, 2, 7, "MariaDB")),
((10, 2, 7), (5, 6, 15, 10, 2, 7, "MariaDB")),
((10, 2, 10), (10, 2, 10, "MariaDB")),
((5, 7, 20), (5, 7, 20)),
((5, 6, 15), (5, 6, 15)),
(
(10, 2, 6),
(10, 2, 6, "MariaDB", 10, 2, "6+maria~stretch", "log"),
),
]:
dialect = mysql.dialect()
dialect.server_version_info = version
eq_(dialect._mariadb_normalized_version_info, expected)
def test_update_from_join_mysql(self):
users, addresses = self.tables.users, self.tables.addresses
j = users.join(addresses)
self.assert_compile(
update(j).
values(name='newname').
where(addresses.c.email_address == 'e1'),
""
'UPDATE users '
'INNER JOIN addresses ON users.id = addresses.user_id '
'SET users.name=%s '
'WHERE '
'addresses.email_address = %s',
checkparams={'email_address_1': 'e1', 'name': 'newname'},
dialect=mysql.dialect())
def test_render_table_mysql(self):
users, addresses = self.tables.users, self.tables.addresses
self.assert_compile(
users.update()
.values(name="newname")
.where(users.c.id == addresses.c.user_id)
.where(addresses.c.email_address == "e1"),
"UPDATE users, addresses "
"SET users.name=%s "
"WHERE "
"users.id = addresses.user_id AND "
"addresses.email_address = %s",
checkparams={"email_address_1": "e1", "name": "newname"},
dialect=mysql.dialect(),
)
def test_render_table_mysql(self):
users, addresses = self.tables.users, self.tables.addresses
self.assert_compile(
users.update()
.values(name="newname")
.where(users.c.id == addresses.c.user_id)
.where(addresses.c.email_address == "e1"),
"UPDATE users, addresses "
"SET users.name=%s "
"WHERE "
"users.id = addresses.user_id AND "
"addresses.email_address = %s",
checkparams={"email_address_1": "e1", "name": "newname"},
dialect=mysql.dialect(),
)
def compile_query(query):
dialect = mysql_dialetct.dialect()
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
comp.compile()
enc = dialect.encoding
comp_params = comp.params
params = []
for k in comp.positiontup:
v = comp_params[k]
if six.PY2 and isinstance(
v, six.string_types) and not isinstance(v, six.text_type):
v = v.decode("utf8")
v = escape_item(v, conversions, encoders)
params.append(v)
return (comp.string % tuple(params))
def test_column_datatype_to_string(self):
test_cases = (
(DATE(), "DATE"),
(VARCHAR(length=255), "VARCHAR(255)"),
(
VARCHAR(length=255, charset="latin1", collation="utf8mb4_general_ci"),
"VARCHAR(255)",
),
(NVARCHAR(length=128), "NATIONAL VARCHAR(128)"),
(TEXT(), "TEXT"),
)
for original, expected in test_cases:
actual = MySQLEngineSpec.column_datatype_to_string(
original, mysql.dialect()
)
self.assertEqual(actual, expected)
def _compile_query(query):
comp = query.statement.compile(dialect=mysql.dialect())
comp_params = comp.params
params = []
for k in comp.positiontup:
v = comp_params[k]
params.append(escape_item(v, conversions, encoders))
comp = comp.string
if "equity_preferred_stock" in comp:
if "equity_prefer_stock" in comp:
comp = comp.replace("fundamental_view.equity_prefer_stock,", "")
comp = comp.replace("equity_preferred_stock", "equity_prefer_stock as equity_preferred_stock")
elif "equity_prefer_stock" in comp:
warnings.warn("'equity_prefer_stock' has been deprecated, please use 'equity_preferred_stock'.")
return comp % tuple(params)
def run2(self, tags, *args, **kvargs):
"""Make query. Use base_smtp2
:param list tags: list of permission tags
:param args: custom args
:param kvargs: custom kvargs
"""
start = time()
if self.with_perm_tag is True:
self.logger.debug2('Authorization with permission tags ENABLED')
else:
self.logger.debug2('Authorization with permission tags DISABLED')
if tags is None or len(tags) == 0:
tags = ['']
# make query
if self.size > 0:
# count all records
stmp = self.base_stmp2(count=True)
total = self.session.query('count').from_statement(stmp).params(
tags=tags, **kvargs).first()[0]
stmp = self.base_stmp2()
# set query entities
entities = [self.entity]
entities.extend(self.other_entities)
query = self.session.query(*entities).from_statement(stmp).params(
tags=tags, **kvargs)
self.logger.debug2('stmp: %s' %
query.statement.compile(dialect=mysql.dialect()))
self.logger.debug2('kvargs: %s' % truncate(kvargs))
self.logger.debug2('tags: %s' % truncate(tags))
res = query.all()
if self.size == 0 or self.size == -1:
total = len(res)
elapsed = round(time() - start, 3)
self.logger.debug2(
'Get %ss (total:%s): %s [%s]' %
(self.entity.__tablename__, total, truncate(res), elapsed))
return res, total
def test_update_from_join_mysql(self):
users, addresses = self.tables.users, self.tables.addresses
j = users.join(addresses)
self.assert_compile(
update(j)
.values(name="newname")
.where(addresses.c.email_address == "e1"),
""
"UPDATE users "
"INNER JOIN addresses ON users.id = addresses.user_id "
"SET users.name=%s "
"WHERE "
"addresses.email_address = %s",
checkparams={"email_address_1": "e1", "name": "newname"},
dialect=mysql.dialect(),
)
def test_update_from_join_mysql(self):
users, addresses = self.tables.users, self.tables.addresses
j = users.join(addresses)
self.assert_compile(
update(j).values(name='newname').where(
addresses.c.email_address == 'e1'), ""
'UPDATE users '
'INNER JOIN addresses ON users.id = addresses.user_id '
'SET users.name=%s '
'WHERE '
'addresses.email_address = %s',
checkparams={
'email_address_1': 'e1',
'name': 'newname'
},
dialect=mysql.dialect())
class DummyResource(Resource):
def create_column_metadata_set(self, cursor: Cursor) -> List[ColumnMetadata]:
pass
DIALECT = mysql.dialect(paramstyle='named')
@classmethod
def create_connection_maker(
cls,
host: Optional[str] = None,
port: Optional[int] = None,
user_name: Optional[str] = None,
password: Optional[str] = None,
database: Optional[str] = None,
engine_kwargs: Dict[str, Any] = None,
) -> ConnectionMaker:
pass
def process_message(self, transaction, message_body):
"""If processing message task requires several queries to db or single query has extreme difficulty
then this method could be overridden.
In this case using of self.build_message_store_stmt method is not required
and could be overridden with pass statement
This method must return boolean (or interpretable as boolean) result which determines to ack or nack message
Also this method must be overridden in case of target database changed from mysql
"""
stmt = self.build_message_store_stmt(message_body)
if isinstance(stmt, SQLAlchemyExecutable):
stmt_compiled = stmt.compile(
dialect=mysql.dialect(),
compile_kwargs={"literal_binds": True})
transaction.execute(str(stmt_compiled))
# transaction.execute(str(stmt_compiled), stmt_compiled.params)
else:
transaction.execute(stmt)
return True
def test_update_from_join_mysql_no_whereclause_three(self):
users, addresses, dingalings = (
self.tables.users,
self.tables.addresses,
self.tables.dingalings,
)
j = users.join(addresses).join(dingalings)
self.assert_compile(
update(j).values({users.c.name: dingalings.c.id}),
""
"UPDATE users "
"INNER JOIN addresses ON users.id = addresses.user_id "
"INNER JOIN dingalings ON addresses.id = dingalings.address_id "
"SET users.name=dingalings.id",
checkparams={},
dialect=mysql.dialect(),
)
def test_repr_dialect_type(self):
from sqlalchemy.dialects.mysql import VARCHAR
type_ = VARCHAR(20, charset='utf8', national=True)
autogen_context = {
'opts': {
'sqlalchemy_module_prefix': 'sa.',
'alembic_module_prefix': 'op.',
'user_module_prefix': None,
},
'imports': set(),
'dialect': mysql.dialect()
}
eq_ignore_whitespace(
autogenerate.render._repr_type(type_, autogen_context),
"mysql.VARCHAR(charset='utf8', national=True, length=20)")
eq_(autogen_context['imports'],
set(['from sqlalchemy.dialects import mysql']))
def test_prefix_with(self):
table1 = self.tables.mytable
stmt = (table1.update().prefix_with("A", "B",
dialect="mysql").prefix_with(
"C", "D"))
self.assert_compile(
stmt,
"UPDATE C D mytable SET myid=:myid, name=:name, "
"description=:description",
)
self.assert_compile(
stmt,
"UPDATE A B C D mytable SET myid=%s, name=%s, description=%s",
dialect=mysql.dialect(),
)
def test_repr_user_type_user_prefix_None(self):
class MyType(UserDefinedType):
def get_col_spec(self):
return "MYTYPE"
type_ = MyType()
autogen_context = {
'opts': {
'sqlalchemy_module_prefix': 'sa.',
'alembic_module_prefix': 'op.',
'user_module_prefix': None
},
'dialect': mysql.dialect()
}
eq_ignore_whitespace(
autogenerate.render._repr_type(type_, autogen_context),
"tests.test_autogen_render.MyType()")