From f0d2e599b60317fc27f64f0abe7d2af65fba7e7b Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 9 Aug 2009 22:11:40 +0000 Subject: [PATCH] - PG: somewhat better support for % signs in table/column names; psycopg2 can't handle a bind parameter name of %(foobar)s however and SQLA doesn't want to add overhead just to treat that one non-existent use case. [ticket:1279] - MySQL: somewhat better support for % signs in table/column names; MySQLdb can't handle % signs in SQL when executemany() is used, and SQLA doesn't want to add overhead just to treat that one non-existent use case. [ticket:1279] --- CHANGES | 11 ++++ lib/sqlalchemy/dialects/mysql/base.py | 7 ++- lib/sqlalchemy/dialects/postgresql/base.py | 4 ++ test/sql/test_query.py | 68 +++++++++++++--------- 4 files changed, 61 insertions(+), 29 deletions(-) diff --git a/CHANGES b/CHANGES index c34751cc7b..cc005a2712 100644 --- a/CHANGES +++ b/CHANGES @@ -229,10 +229,21 @@ CHANGES - using new dialect.initialize() feature to set up version-dependent behavior. + - somewhat better support for % signs in table/column names; + psycopg2 can't handle a bind parameter name of + %(foobar)s however and SQLA doesn't want to add overhead + just to treat that one non-existent use case. + [ticket:1279] + - mysql - all the _detect_XXX() functions now run once underneath dialect.initialize() + - somewhat better support for % signs in table/column names; + MySQLdb can't handle % signs in SQL when executemany() is used, + and SQLA doesn't want to add overhead just to treat that one + non-existent use case. [ticket:1279] + - oracle - unit tests pass 100% with cx_oracle ! diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 1c5c251e54..570d1a79be 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -2514,6 +2514,10 @@ class _MySQLIdentifierPreparer(compiler.IdentifierPreparer): return tuple([self.quote_identifier(i) for i in ids if i is not None]) + def _escape_identifier(self, value): + value = value.replace('"', '""') + return value.replace('%', '%%') + class MySQLIdentifierPreparer(_MySQLIdentifierPreparer): """Traditional MySQL-specific schema identifier configuration.""" @@ -2522,7 +2526,8 @@ class MySQLIdentifierPreparer(_MySQLIdentifierPreparer): super(MySQLIdentifierPreparer, self).__init__(dialect, initial_quote="`") def _escape_identifier(self, value): - return value.replace('`', '``') + value = value.replace('`', '``') + return value.replace('%', '%%') def _unescape_identifier(self, value): return value.replace('``', '`') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index fbba8221bf..c89ae16bd1 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -431,6 +431,10 @@ class PGIdentifierPreparer(compiler.IdentifierPreparer): value = value[1:-1].replace('""','"') return value + def _escape_identifier(self, value): + value = value.replace('"', '""') + return value.replace('%', '%%') + class PGInspector(reflection.Inspector): def __init__(self, conn): diff --git a/test/sql/test_query.py b/test/sql/test_query.py index d10313d14f..934bdadbee 100644 --- a/test/sql/test_query.py +++ b/test/sql/test_query.py @@ -734,77 +734,89 @@ class PercentSchemaNamesTest(TestBase): """ @classmethod - @testing.crashes('mysql', 'mysqldb calls name % (params)') - @testing.crashes('postgresql', 'postgresql calls name % (params)') def setup_class(cls): global percent_table, metadata metadata = MetaData(testing.db) percent_table = Table('percent%table', metadata, Column("percent%", Integer), - Column("%(oneofthese)s", Integer), Column("spaces % more spaces", Integer), ) metadata.create_all() + def teardown(self): + percent_table.delete().execute() + @classmethod - @testing.crashes('mysql', 'mysqldb calls name % (params)') - @testing.crashes('postgresql', 'postgresql calls name % (params)') def teardown_class(cls): metadata.drop_all() - @testing.crashes('mysql', 'mysqldb calls name % (params)') - @testing.crashes('postgresql', 'postgresql calls name % (params)') - def test_roundtrip(self): + def test_single_roundtrip(self): + percent_table.insert().execute( + {'percent%':5, 'spaces % more spaces':12}, + ) + percent_table.insert().execute( + {'percent%':7, 'spaces % more spaces':11}, + ) + percent_table.insert().execute( + {'percent%':9, 'spaces % more spaces':10}, + ) + percent_table.insert().execute( + {'percent%':11, 'spaces % more spaces':9}, + ) + self._assert_table() + + @testing.crashes('mysql+mysqldb', 'MySQLdb handles executemany() inconsistently vs. execute()') + def test_executemany_roundtrip(self): percent_table.insert().execute( - {'percent%':5, '%(oneofthese)s':7, 'spaces % more spaces':12}, + {'percent%':5, 'spaces % more spaces':12}, ) percent_table.insert().execute( - {'percent%':7, '%(oneofthese)s':8, 'spaces % more spaces':11}, - {'percent%':9, '%(oneofthese)s':9, 'spaces % more spaces':10}, - {'percent%':11, '%(oneofthese)s':10, 'spaces % more spaces':9}, + {'percent%':7, 'spaces % more spaces':11}, + {'percent%':9, 'spaces % more spaces':10}, + {'percent%':11, 'spaces % more spaces':9}, ) + self._assert_table() + def _assert_table(self): for table in (percent_table, percent_table.alias()): eq_( - table.select().order_by(table.c['%(oneofthese)s']).execute().fetchall(), + table.select().order_by(table.c['percent%']).execute().fetchall(), [ - (5, 7, 12), - (7, 8, 11), - (9, 9, 10), - (11, 10, 9) + (5, 12), + (7, 11), + (9, 10), + (11, 9) ] ) eq_( table.select(). where(table.c['spaces % more spaces'].in_([9, 10])). - order_by(table.c['%(oneofthese)s']).execute().fetchall(), + order_by(table.c['percent%']).execute().fetchall(), [ - (9, 9, 10), - (11, 10, 9) + (9, 10), + (11, 9) ] ) - result = table.select().order_by(table.c['%(oneofthese)s']).execute() + result = table.select().order_by(table.c['percent%']).execute() row = result.fetchone() eq_(row[table.c['percent%']], 5) - eq_(row[table.c['%(oneofthese)s']], 7) eq_(row[table.c['spaces % more spaces']], 12) row = result.fetchone() eq_(row['percent%'], 7) - eq_(row['%(oneofthese)s'], 8) eq_(row['spaces % more spaces'], 11) result.close() - percent_table.update().values({percent_table.c['%(oneofthese)s']:9, percent_table.c['spaces % more spaces']:15}).execute() + percent_table.update().values({percent_table.c['spaces % more spaces']:15}).execute() eq_( percent_table.select().order_by(percent_table.c['percent%']).execute().fetchall(), [ - (5, 9, 15), - (7, 9, 15), - (9, 9, 15), - (11, 9, 15) + (5, 15), + (7, 15), + (9, 15), + (11, 15) ] ) -- 2.47.3