[Sqlalchemy-commits] sqlalchemy: Add PostgreSQL HStore type support
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-commits] sqlalchemy: Add PostgreSQL HStore type support
|
From: <co...@sq...> - 2012-11-18 01:46:32
|
details: http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/6254e9092f6d changeset: 8912:6254e9092f6d user: Audrius Ka?ukauskas <au...@ne...> date: Tue Nov 13 16:43:41 2012 +0200 description: Add PostgreSQL HStore type support Subject: sqlalchemy: Register HStore adapter and typecaster in psycopg2 dialect details: http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/0b56631873fc changeset: 8913:0b56631873fc user: Audrius Ka?ukauskas <au...@ne...> date: Thu Nov 15 16:03:45 2012 +0200 description: Register HStore adapter and typecaster in psycopg2 dialect Subject: sqlalchemy: Merged in audriusk/sqlalchemy_pg_hstore (pull request #26). will adjust some aspects of it, including replace userdefinedtype with typeengine, and move mutationdict to be part of sqlalchemy.ext.mutable details: http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/10324c6c2e40 changeset: 8914:10324c6c2e40 user: zzzeek <cl...@zz...> date: Sat Nov 17 18:53:23 2012 -0500 description: Merged in audriusk/sqlalchemy_pg_hstore (pull request #26). will adjust some aspects of it, including replace userdefinedtype with typeengine, and move mutationdict to be part of sqlalchemy.ext.mutable Subject: sqlalchemy: - hstore adjustments details: http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/ba4730cfbe34 changeset: 8915:ba4730cfbe34 user: Mike Bayer <mi...@zz...> date: Sat Nov 17 20:45:17 2012 -0500 description: - hstore adjustments Subject: sqlalchemy: migration doc details: http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/aa9574fbeb8f changeset: 8916:aa9574fbeb8f user: Mike Bayer <mi...@zz...> date: Sat Nov 17 20:46:13 2012 -0500 description: migration doc diffstat: doc/build/changelog/changelog_08.rst | 57 + doc/build/changelog/migration_08.rst | 5 +- doc/build/orm/extensions/mutable.rst | 3 + doc/build/orm/session.rst | 2 +- lib/sqlalchemy/dialects/mssql/base.py | 3 +- lib/sqlalchemy/dialects/postgresql/__init__.py | 13 +- lib/sqlalchemy/dialects/postgresql/base.py | 3 + lib/sqlalchemy/dialects/postgresql/hstore.py | 324 ++++++++++ lib/sqlalchemy/dialects/postgresql/psycopg2.py | 62 +- lib/sqlalchemy/dialects/sybase/base.py | 458 +++++++++++++- lib/sqlalchemy/engine/base.py | 83 +- lib/sqlalchemy/ext/mutable.py | 78 +- lib/sqlalchemy/schema.py | 41 +- lib/sqlalchemy/testing/exclusions.py | 8 +- lib/sqlalchemy/util/_collections.py | 11 +- test/base/test_utils.py | 737 ++++++++++++++++++++++-- test/dialect/test_postgresql.py | 320 ++++++++++- test/engine/test_bind.py | 25 +- test/engine/test_reflection.py | 47 +- test/ext/test_mutable.py | 52 +- test/requirements.py | 43 +- test/sql/test_query.py | 21 + test/sql/test_types.py | 3 +- 23 files changed, 2102 insertions(+), 297 deletions(-) diffs (truncated from 3531 to 300 lines): diff -r 4ae8f8bfc2f3 -r aa9574fbeb8f doc/build/changelog/changelog_08.rst --- a/doc/build/changelog/changelog_08.rst Mon Nov 12 16:36:20 2012 -0500 +++ b/doc/build/changelog/changelog_08.rst Sat Nov 17 20:46:13 2012 -0500 @@ -7,6 +7,63 @@ :version: 0.8.0b2 .. change:: + :tags: postgresql, hstore + :tickets: 2606 + + :class:`.HSTORE` is now available in the Postgresql dialect. + Will also use psycopg2's extensions if available. Courtesy + Audrius Kažukauskas. + + .. change:: + :tags: sybase, feature + :tickets: 1753 + + Reflection support has been added to the Sybase dialect. + Big thanks to Ben Trofatter for all the work developing and + testing this. + + .. change:: + :tags: engine, feature + + The :meth:`.Connection.connect` and :meth:`.Connection.contextual_connect` + methods now return a "branched" version so that the :meth:`.Connection.close` + method can be called on the returned connection without affecting the + original. Allows symmetry when using :class:`.Engine` and + :class:`.Connection` objects as context managers:: + + with conn.connect() as c: # leaves the Connection open + c.execute("...") + + with engine.connect() as c: # closes the Connection + c.execute("...") + + .. change:: + :tags: engine + + The "reflect=True" argument to :class:`MetaData` is deprecated. + Please use the :meth:`.MetaData.reflect` method. + + .. change:: + :tags: engine, bug + :tickets: 2604 + + Fixed :meth:`.MetaData.reflect` to correctly use + the given :class:`.Connection`, if given, without + opening a second connection from that connection's + :class:`.Engine`. Also in 0.7.10. + + .. change:: + :tags: mssql, bug + :tickets: 2607 + + Fixed bug whereby using "key" with Column + in conjunction with "schema" for the owning + Table would fail to locate result rows due + to the MSSQL dialect's "schema rendering" + logic's failure to take .key into account. + Also in 0.7.10. + + .. change:: :tags: sql, bug :tickets: 2603 diff -r 4ae8f8bfc2f3 -r aa9574fbeb8f doc/build/changelog/migration_08.rst --- a/doc/build/changelog/migration_08.rst Mon Nov 12 16:36:20 2012 -0500 +++ b/doc/build/changelog/migration_08.rst Sat Nov 17 20:46:13 2012 -0500 @@ -651,8 +651,7 @@ New features which have come from this immediately include -support for Postgresql's HSTORE type, which is ready to go -in a separate library which may be merged, as well as new +support for Postgresql's HSTORE type, as well as new operations associated with Postgresql's ARRAY type. It also paves the way for existing types to acquire lots more operators that are specific to those types, such @@ -662,7 +661,7 @@ :ref:`types_operators` - `Postgresql HSTORE <https://bitbucket.org/audriusk/hstore>`_ - support for HSTORE in SQLAlchemy + :class:`.HSTORE` :ticket:`2547` diff -r 4ae8f8bfc2f3 -r aa9574fbeb8f doc/build/orm/extensions/mutable.rst --- a/doc/build/orm/extensions/mutable.rst Mon Nov 12 16:36:20 2012 -0500 +++ b/doc/build/orm/extensions/mutable.rst Sat Nov 17 20:46:13 2012 -0500 @@ -19,6 +19,9 @@ :show-inheritance: :members: +.. autoclass:: MutableDict + :show-inheritance: + :members: diff -r 4ae8f8bfc2f3 -r aa9574fbeb8f doc/build/orm/session.rst --- a/doc/build/orm/session.rst Mon Nov 12 16:36:20 2012 -0500 +++ b/doc/build/orm/session.rst Sat Nov 17 20:46:13 2012 -0500 @@ -42,7 +42,7 @@ objects to re-access the database in order to keep synchronized. It is possible to "detach" objects from a :class:`.Session`, and to continue using them, though this practice has its caveats. It's intended that -usually, you'd re-associate detached objects another :class:`.Session` when you +usually, you'd re-associate detached objects with another :class:`.Session` when you want to work with them again, so that they can resume their normal task of representing database state. diff -r 4ae8f8bfc2f3 -r aa9574fbeb8f lib/sqlalchemy/dialects/mssql/base.py --- a/lib/sqlalchemy/dialects/mssql/base.py Mon Nov 12 16:36:20 2012 -0500 +++ b/lib/sqlalchemy/dialects/mssql/base.py Sat Nov 17 20:46:13 2012 -0500 @@ -855,12 +855,11 @@ if t is not None: converted = expression._corresponding_column_or_error( t, column) - if add_to_result_map is not None: add_to_result_map( column.name, column.name, - (column, ), + (column, column.name, column.key), column.type ) diff -r 4ae8f8bfc2f3 -r aa9574fbeb8f lib/sqlalchemy/dialects/postgresql/__init__.py --- a/lib/sqlalchemy/dialects/postgresql/__init__.py Mon Nov 12 16:36:20 2012 -0500 +++ b/lib/sqlalchemy/dialects/postgresql/__init__.py Sat Nov 17 20:46:13 2012 -0500 @@ -9,12 +9,15 @@ base.dialect = psycopg2.dialect from .base import \ - INTEGER, BIGINT, SMALLINT, VARCHAR, CHAR, TEXT, NUMERIC, FLOAT, REAL, INET, \ - CIDR, UUID, BIT, MACADDR, DOUBLE_PRECISION, TIMESTAMP, TIME,\ + INTEGER, BIGINT, SMALLINT, VARCHAR, CHAR, TEXT, NUMERIC, FLOAT, REAL, \ + INET, CIDR, UUID, BIT, MACADDR, DOUBLE_PRECISION, TIMESTAMP, TIME, \ DATE, BYTEA, BOOLEAN, INTERVAL, ARRAY, ENUM, dialect, array +from .hstore import HSTORE, hstore, HStoreSyntaxError __all__ = ( -'INTEGER', 'BIGINT', 'SMALLINT', 'VARCHAR', 'CHAR', 'TEXT', 'NUMERIC', 'FLOAT', 'REAL', 'INET', -'CIDR', 'UUID', 'BIT', 'MACADDR', 'DOUBLE_PRECISION', 'TIMESTAMP', 'TIME', -'DATE', 'BYTEA', 'BOOLEAN', 'INTERVAL', 'ARRAY', 'ENUM', 'dialect', 'array' + 'INTEGER', 'BIGINT', 'SMALLINT', 'VARCHAR', 'CHAR', 'TEXT', 'NUMERIC', + 'FLOAT', 'REAL', 'INET', 'CIDR', 'UUID', 'BIT', 'MACADDR', + 'DOUBLE_PRECISION', 'TIMESTAMP', 'TIME', 'DATE', 'BYTEA', 'BOOLEAN', + 'INTERVAL', 'ARRAY', 'ENUM', 'dialect', 'array', 'HSTORE', 'hstore', + 'HStoreSyntaxError' ) diff -r 4ae8f8bfc2f3 -r aa9574fbeb8f lib/sqlalchemy/dialects/postgresql/base.py --- a/lib/sqlalchemy/dialects/postgresql/base.py Mon Nov 12 16:36:20 2012 -0500 +++ b/lib/sqlalchemy/dialects/postgresql/base.py Sat Nov 17 20:46:13 2012 -0500 @@ -968,6 +968,9 @@ def visit_BIGINT(self, type_): return "BIGINT" + def visit_HSTORE(self, type_): + return "HSTORE" + def visit_datetime(self, type_): return self.visit_TIMESTAMP(type_) diff -r 4ae8f8bfc2f3 -r aa9574fbeb8f lib/sqlalchemy/dialects/postgresql/hstore.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/lib/sqlalchemy/dialects/postgresql/hstore.py Sat Nov 17 20:46:13 2012 -0500 @@ -0,0 +1,324 @@ +# postgresql/hstore.py +# Copyright (C) 2005-2012 the SQLAlchemy authors and contributors <see AUTHORS file> +# +# This module is part of SQLAlchemy and is released under +# the MIT License: http://www.opensource.org/licenses/mit-license.php + +import re + +from .base import ARRAY +from ... import types as sqltypes +from ...sql import functions as sqlfunc +from ...sql.operators import custom_op +from ...exc import SQLAlchemyError + +__all__ = ('HStoreSyntaxError', 'HSTORE', 'hstore') + +# My best guess at the parsing rules of hstore literals, since no formal +# grammar is given. This is mostly reverse engineered from PG's input parser +# behavior. +HSTORE_PAIR_RE = re.compile(r""" +( + "(?P<key> (\\ . | [^"])* )" # Quoted key +) +[ ]* => [ ]* # Pair operator, optional adjoining whitespace +( + (?P<value_null> NULL ) # NULL value + | "(?P<value> (\\ . | [^"])* )" # Quoted value +) +""", re.VERBOSE) + +HSTORE_DELIMITER_RE = re.compile(r""" +[ ]* , [ ]* +""", re.VERBOSE) + + +class HStoreSyntaxError(SQLAlchemyError): + """Indicates an error unmarshalling an hstore value.""" + + def __init__(self, hstore_str, pos): + self.hstore_str = hstore_str + self.pos = pos + + ctx = 20 + hslen = len(hstore_str) + + parsed_tail = hstore_str[max(pos - ctx - 1, 0):min(pos, hslen)] + residual = hstore_str[min(pos, hslen):min(pos + ctx + 1, hslen)] + + if len(parsed_tail) > ctx: + parsed_tail = '[...]' + parsed_tail[1:] + if len(residual) > ctx: + residual = residual[:-1] + '[...]' + + super(HStoreSyntaxError, self).__init__( + "After %r, could not parse residual at position %d: %r" % + (parsed_tail, pos, residual) + ) + + +def _parse_hstore(hstore_str): + """Parse an hstore from it's literal string representation. + + Attempts to approximate PG's hstore input parsing rules as closely as + possible. Although currently this is not strictly necessary, since the + current implementation of hstore's output syntax is stricter than what it + accepts as input, the documentation makes no guarantees that will always + be the case. + + Throws HStoreSyntaxError if parsing fails. + + """ + result = {} + pos = 0 + pair_match = HSTORE_PAIR_RE.match(hstore_str) + + while pair_match is not None: + key = pair_match.group('key') + if pair_match.group('value_null'): + value = None + else: + value = pair_match.group('value').replace(r'\"', '"') + result[key] = value + + pos += pair_match.end() + + delim_match = HSTORE_DELIMITER_RE.match(hstore_str[pos:]) + if delim_match is not None: + pos += delim_match.end() + + pair_match = HSTORE_PAIR_RE.match(hstore_str[pos:]) + + if pos != len(hstore_str): + raise HStoreSyntaxError(hstore_str, pos) + + return result + + +def _serialize_hstore(val): + """Serialize a dictionary into an hstore literal. Keys and values must + both be strings (except None for values). + + """ + def esc(s, position): + if position == 'value' and s is None: + return 'NULL' + elif isinstance(s, basestring): + return '"%s"' % s.replace('"', r'\"') + else: + raise ValueError("%r in %s position is not a string." % + (s, position)) + + return ', '.join('%s=>%s' % (esc(k, 'key'), esc(v, 'value')) + for k, v in val.iteritems()) + + +class HSTORE(sqltypes.Concatenable, sqltypes.TypeEngine): + """Represent the Postgresql HSTORE type. + + The :class:`.HSTORE` type stores dictionaries containing strings, e.g.:: + + data_table = Table('data_table', metadata, + Column('id', Integer, primary_key=True), + Column('data', HSTORE) + ) + + with engine.connect() as conn: + conn.execute( + data_table.insert(), |