From c188526a74486596bfaef2dadbaeff915ec34812 Mon Sep 17 00:00:00 2001 From: Erich Blume Date: Thu, 30 Jan 2014 18:08:33 -0800 Subject: PEP-8 compliance for dialects/sqlite/base.py --- lib/sqlalchemy/dialects/sqlite/__init__.py | 16 +- lib/sqlalchemy/dialects/sqlite/base.py | 256 ++++++++++++++--------------- 2 files changed, 129 insertions(+), 143 deletions(-) (limited to 'lib/sqlalchemy/dialects/sqlite') diff --git a/lib/sqlalchemy/dialects/sqlite/__init__.py b/lib/sqlalchemy/dialects/sqlite/__init__.py index a9b23575b..80846c9ec 100644 --- a/lib/sqlalchemy/dialects/sqlite/__init__.py +++ b/lib/sqlalchemy/dialects/sqlite/__init__.py @@ -9,13 +9,11 @@ from sqlalchemy.dialects.sqlite import base, pysqlite # default dialect base.dialect = pysqlite.dialect - -from sqlalchemy.dialects.sqlite.base import \ - BLOB, BOOLEAN, CHAR, DATE, DATETIME, DECIMAL, FLOAT, INTEGER, REAL,\ - NUMERIC, SMALLINT, TEXT, TIME, TIMESTAMP, VARCHAR, dialect - -__all__ = ( - 'BLOB', 'BOOLEAN', 'CHAR', 'DATE', 'DATETIME', 'DECIMAL', 'FLOAT', - 'INTEGER', 'NUMERIC', 'SMALLINT', 'TEXT', 'TIME', 'TIMESTAMP', 'VARCHAR', - 'REAL', 'dialect' +from sqlalchemy.dialects.sqlite.base import ( + BLOB, BOOLEAN, CHAR, DATE, DATETIME, DECIMAL, FLOAT, INTEGER, REAL, + NUMERIC, SMALLINT, TEXT, TIME, TIMESTAMP, VARCHAR, dialect, ) + +__all__ = ('BLOB', 'BOOLEAN', 'CHAR', 'DATE', 'DATETIME', 'DECIMAL', + 'FLOAT', 'INTEGER', 'NUMERIC', 'SMALLINT', 'TEXT', 'TIME', + 'TIMESTAMP', 'VARCHAR', 'REAL', 'dialect') diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index d8aa58c2c..258b7944f 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -12,15 +12,15 @@ Date and Time Types ------------------- -SQLite does not have built-in DATE, TIME, or DATETIME types, and pysqlite -does not provide out of the box functionality for translating values between -Python `datetime` objects and a SQLite-supported format. SQLAlchemy's own +SQLite does not have built-in DATE, TIME, or DATETIME types, and pysqlite does +not provide out of the box functionality for translating values between Python +`datetime` objects and a SQLite-supported format. SQLAlchemy's own :class:`~sqlalchemy.types.DateTime` and related types provide date formatting and parsing functionality when SQlite is used. The implementation classes are :class:`~.sqlite.DATETIME`, :class:`~.sqlite.DATE` and :class:`~.sqlite.TIME`. These types represent dates and times as ISO formatted strings, which also -nicely support ordering. There's no reliance on typical "libc" internals -for these functions so historical dates are fully supported. +nicely support ordering. There's no reliance on typical "libc" internals for +these functions so historical dates are fully supported. Auto Incrementing Behavior -------------------------- @@ -30,15 +30,15 @@ Background on SQLite's autoincrement is at: http://sqlite.org/autoinc.html Two things to note: * The AUTOINCREMENT keyword is **not** required for SQLite tables to - generate primary key values automatically. AUTOINCREMENT only means that - the algorithm used to generate ROWID values should be slightly different. + generate primary key values automatically. AUTOINCREMENT only means that the + algorithm used to generate ROWID values should be slightly different. * SQLite does **not** generate primary key (i.e. ROWID) values, even for one column, if the table has a composite (i.e. multi-column) primary key. This is regardless of the AUTOINCREMENT keyword being present or not. -To specifically render the AUTOINCREMENT keyword on the primary key -column when rendering DDL, add the flag ``sqlite_autoincrement=True`` -to the Table construct:: +To specifically render the AUTOINCREMENT keyword on the primary key column when +rendering DDL, add the flag ``sqlite_autoincrement=True`` to the Table +construct:: Table('sometable', metadata, Column('id', Integer, primary_key=True), @@ -47,47 +47,46 @@ to the Table construct:: Transaction Isolation Level --------------------------- -:func:`.create_engine` accepts an ``isolation_level`` parameter which -results in the command ``PRAGMA read_uncommitted `` being invoked for -every new connection. Valid values for this parameter are ``SERIALIZABLE`` -and ``READ UNCOMMITTED`` corresponding to a value of 0 and 1, respectively. -See the section :ref:`pysqlite_serializable` for an important workaround -when using serializable isolation with Pysqlite. +:func:`.create_engine` accepts an ``isolation_level`` parameter which results +in the command ``PRAGMA read_uncommitted `` being invoked for every new +connection. Valid values for this parameter are ``SERIALIZABLE`` and ``READ +UNCOMMITTED`` corresponding to a value of 0 and 1, respectively. See the +section :ref:`pysqlite_serializable` for an important workaround when using +serializable isolation with Pysqlite. Database Locking Behavior / Concurrency --------------------------------------- -Note that SQLite is not designed for a high level of concurrency. The -database itself, being a file, is locked completely during write operations -and within transactions, meaning exactly one connection has exclusive access -to the database during this period - all other connections will be blocked -during this time. +Note that SQLite is not designed for a high level of concurrency. The database +itself, being a file, is locked completely during write operations and within +transactions, meaning exactly one connection has exclusive access to the +database during this period - all other connections will be blocked during this +time. The Python DBAPI specification also calls for a connection model that is always -in a transaction; there is no BEGIN method, only commit and rollback. This +in a transaction; there is no BEGIN method, only commit and rollback. This implies that a SQLite DBAPI driver would technically allow only serialized -access to a particular database file at all times. The pysqlite driver -attempts to ameliorate this by deferring the actual BEGIN statement until -the first DML (INSERT, UPDATE, or DELETE) is received within a -transaction. While this breaks serializable isolation, it at least delays -the exclusive locking inherent in SQLite's design. - -SQLAlchemy's default mode of usage with the ORM is known -as "autocommit=False", which means the moment the :class:`.Session` begins to -be used, a transaction is begun. As the :class:`.Session` is used, the -autoflush feature, also on by default, will flush out pending changes to the -database before each query. The effect of this is that a :class:`.Session` -used in its default mode will often emit DML early on, long before the -transaction is actually committed. This again will have the effect of -serializing access to the SQLite database. If highly concurrent reads are -desired against the SQLite database, it is advised that the autoflush feature -be disabled, and potentially even that autocommit be re-enabled, which has -the effect of each SQL statement and flush committing changes immediately. - -For more information on SQLite's lack of concurrency by design, please -see `Situations Where Another RDBMS May Work Better - High -Concurrency `_ near the bottom of -the page. +access to a particular database file at all times. The pysqlite driver attempts +to ameliorate this by deferring the actual BEGIN statement until the first DML +(INSERT, UPDATE, or DELETE) is received within a transaction. While this breaks +serializable isolation, it at least delays the exclusive locking inherent in +SQLite's design. + +SQLAlchemy's default mode of usage with the ORM is known as "autocommit=False", +which means the moment the :class:`.Session` begins to be used, a transaction +is begun. As the :class:`.Session` is used, the autoflush feature, also on by +default, will flush out pending changes to the database before each query. The +effect of this is that a :class:`.Session` used in its default mode will often +emit DML early on, long before the transaction is actually committed. This +again will have the effect of serializing access to the SQLite database. If +highly concurrent reads are desired against the SQLite database, it is advised +that the autoflush feature be disabled, and potentially even that autocommit be +re-enabled, which has the effect of each SQL statement and flush committing +changes immediately. + +For more information on SQLite's lack of concurrency by design, please see +`Situations Where Another RDBMS May Work Better - High Concurrency +`_ near the bottom of the page. .. _sqlite_foreign_keys: @@ -95,8 +94,8 @@ Foreign Key Support ------------------- SQLite supports FOREIGN KEY syntax when emitting CREATE statements for tables, -however by default these constraints have no effect on the operation -of the table. +however by default these constraints have no effect on the operation of the +table. Constraint checking on SQLite has three prerequisites: @@ -106,8 +105,8 @@ Constraint checking on SQLite has three prerequisites: * The ``PRAGMA foreign_keys = ON`` statement must be emitted on all connections before use. -SQLAlchemy allows for the ``PRAGMA`` statement to be emitted automatically -for new connections through the usage of events:: +SQLAlchemy allows for the ``PRAGMA`` statement to be emitted automatically for +new connections through the usage of events:: from sqlalchemy.engine import Engine from sqlalchemy import event @@ -120,8 +119,8 @@ for new connections through the usage of events:: .. seealso:: - `SQLite Foreign Key Support `_ - - on the SQLite web site. + `SQLite Foreign Key Support `_ - on + the SQLite web site. :ref:`event_toplevel` - SQLAlchemy event API. @@ -130,16 +129,15 @@ for new connections through the usage of events:: import datetime import re +from ... import processors from ... import sql, exc -from ...engine import default, reflection from ... import types as sqltypes, schema as sa_schema from ... import util +from ...engine import default, reflection from ...sql import compiler -from ... import processors -from ...types import BIGINT, BLOB, BOOLEAN, CHAR,\ - DECIMAL, FLOAT, REAL, INTEGER, NUMERIC, SMALLINT, TEXT,\ - TIMESTAMP, VARCHAR +from ...types import (BLOB, BOOLEAN, CHAR, DATE, DECIMAL, FLOAT, INTEGER, REAL, + NUMERIC, SMALLINT, TEXT, TIMESTAMP, VARCHAR) class _DateTimeMixin(object): @@ -190,14 +188,14 @@ class DATETIME(_DateTimeMixin, sqltypes.DateTime): regexp=r"(\d+)/(\d+)/(\d+) (\d+)-(\d+)-(\d+)" ) - :param storage_format: format string which will be applied to the - dict with keys year, month, day, hour, minute, second, and microsecond. + :param storage_format: format string which will be applied to the dict with + keys year, month, day, hour, minute, second, and microsecond. - :param regexp: regular expression which will be applied to - incoming result rows. If the regexp contains named groups, the - resulting match dict is applied to the Python datetime() constructor - as keyword arguments. Otherwise, if positional groups are used, the - the datetime() constructor is called with positional arguments via + :param regexp: regular expression which will be applied to incoming result + rows. If the regexp contains named groups, the resulting match dict is + applied to the Python datetime() constructor as keyword arguments. + Otherwise, if positional groups are used, the the datetime() constructor + is called with positional arguments via ``*map(int, match_obj.groups(0))``. """ @@ -219,7 +217,6 @@ class DATETIME(_DateTimeMixin, sqltypes.DateTime): "%(hour)02d:%(minute)02d:%(second)02d" ) - def bind_processor(self, dialect): datetime_datetime = datetime.datetime datetime_date = datetime.date @@ -344,15 +341,14 @@ class TIME(_DateTimeMixin, sqltypes.Time): regexp=re.compile("(\d+)-(\d+)-(\d+)-(?:-(\d+))?") ) - :param storage_format: format string which will be applied to the - dict with keys hour, minute, second, and microsecond. + :param storage_format: format string which will be applied to the dict with + keys hour, minute, second, and microsecond. - :param regexp: regular expression which will be applied to - incoming result rows. If the regexp contains named groups, the - resulting match dict is applied to the Python time() constructor - as keyword arguments. Otherwise, if positional groups are used, the - the time() constructor is called with positional arguments via - ``*map(int, match_obj.groups(0))``. + :param regexp: regular expression which will be applied to incoming result + rows. If the regexp contains named groups, the resulting match dict is + applied to the Python time() constructor as keyword arguments. Otherwise, + if positional groups are used, the the time() constructor is called with + positional arguments via ``*map(int, match_obj.groups(0))``. """ _storage_format = "%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d" @@ -427,17 +423,17 @@ class SQLiteCompiler(compiler.SQLCompiler): extract_map = util.update_copy( compiler.SQLCompiler.extract_map, { - 'month': '%m', - 'day': '%d', - 'year': '%Y', - 'second': '%S', - 'hour': '%H', - 'doy': '%j', - 'minute': '%M', - 'epoch': '%s', - 'dow': '%w', - 'week': '%W' - }) + 'month': '%m', + 'day': '%d', + 'year': '%Y', + 'second': '%S', + 'hour': '%H', + 'doy': '%j', + 'minute': '%M', + 'epoch': '%s', + 'dow': '%w', + 'week': '%W', + }) def visit_now_func(self, fn, **kw): return "CURRENT_TIMESTAMP" @@ -500,11 +496,11 @@ class SQLiteDDLCompiler(compiler.DDLCompiler): colspec += " NOT NULL" if (column.primary_key and - column.table.dialect_options['sqlite']['autoincrement'] and - len(column.table.primary_key.columns) == 1 and - issubclass(column.type._type_affinity, sqltypes.Integer) and - not column.foreign_keys): - colspec += " PRIMARY KEY AUTOINCREMENT" + column.table.dialect_options['sqlite']['autoincrement'] and + len(column.table.primary_key.columns) == 1 and + issubclass(column.type._type_affinity, sqltypes.Integer) and + not column.foreign_keys): + colspec += " PRIMARY KEY AUTOINCREMENT" return colspec @@ -514,14 +510,14 @@ class SQLiteDDLCompiler(compiler.DDLCompiler): # with the column itself. if len(constraint.columns) == 1: c = list(constraint)[0] - if c.primary_key and \ - c.table.dialect_options['sqlite']['autoincrement'] and \ - issubclass(c.type._type_affinity, sqltypes.Integer) and \ - not c.foreign_keys: + if (c.primary_key and + c.table.dialect_options['sqlite']['autoincrement'] and + issubclass(c.type._type_affinity, sqltypes.Integer) and + not c.foreign_keys): return None - return super(SQLiteDDLCompiler, self).\ - visit_primary_key_constraint(constraint) + return super(SQLiteDDLCompiler, self).visit_primary_key_constraint( + constraint) def visit_foreign_key_constraint(self, constraint): @@ -531,7 +527,8 @@ class SQLiteDDLCompiler(compiler.DDLCompiler): if local_table.schema != remote_table.schema: return None else: - return super(SQLiteDDLCompiler, self).visit_foreign_key_constraint(constraint) + return super(SQLiteDDLCompiler, self).visit_foreign_key_constraint( + constraint) def define_constraint_remote_table(self, constraint, table, preparer): """Format the remote table clause of a CREATE CONSTRAINT clause.""" @@ -539,8 +536,8 @@ class SQLiteDDLCompiler(compiler.DDLCompiler): return preparer.format_table(table, use_schema=False) def visit_create_index(self, create): - return super(SQLiteDDLCompiler, self).\ - visit_create_index(create, include_table_schema=False) + return super(SQLiteDDLCompiler, self).visit_create_index( + create, include_table_schema=False) class SQLiteTypeCompiler(compiler.GenericTypeCompiler): @@ -577,10 +574,10 @@ class SQLiteIdentifierPreparer(compiler.IdentifierPreparer): name = index.name result = self.quote(name, index.quote) if (not self.omit_schema and - use_schema and - getattr(index.table, "schema", None)): - result = self.quote_schema( - index.table.schema, index.table.quote_schema) + "." + result + use_schema and + getattr(index.table, "schema", None)): + result = self.quote_schema(index.table.schema, + index.table.quote_schema) + "." + result return result @@ -590,11 +587,9 @@ class SQLiteExecutionContext(default.DefaultExecutionContext): return self.execution_options.get("sqlite_raw_colnames", False) def _translate_colname(self, colname): - # adjust for dotted column names. SQLite - # in the case of UNION may store col names as - # "tablename.colname" - # in cursor.description - if not self._preserve_raw_colnames and "." in colname: + # adjust for dotted column names. SQLite in the case of UNION may store + # col names as "tablename.colname" in cursor.description + if not self._preserve_raw_colnames and "." in colname: return colname.split(".")[1], colname else: return colname, None @@ -636,30 +631,27 @@ class SQLiteDialect(default.DefaultDialect): default.DefaultDialect.__init__(self, **kwargs) self.isolation_level = isolation_level - # this flag used by pysqlite dialect, and perhaps others in the - # future, to indicate the driver is handling date/timestamp - # conversions (and perhaps datetime/time as well on some - # hypothetical driver ?) + # this flag used by pysqlite dialect, and perhaps others in the future, + # to indicate the driver is handling date/timestamp conversions (and + # perhaps datetime/time as well on some hypothetical driver ?) self.native_datetime = native_datetime if self.dbapi is not None: - self.supports_default_values = \ - self.dbapi.sqlite_version_info >= (3, 3, 8) - self.supports_cast = \ - self.dbapi.sqlite_version_info >= (3, 2, 3) - self.supports_multivalues_insert = \ - self.dbapi.sqlite_version_info >= (3, 7, 11) - # http://www.sqlite.org/releaselog/3_7_11.html - + self.supports_default_values = ( + self.dbapi.sqlite_version_info >= (3, 3, 8)) + self.supports_cast = ( + self.dbapi.sqlite_version_info >= (3, 2, 3)) + self.supports_multivalues_insert = ( + # http://www.sqlite.org/releaselog/3_7_11.html + self.dbapi.sqlite_version_info >= (3, 7, 11)) # see http://www.sqlalchemy.org/trac/ticket/2568 # as well as http://www.sqlite.org/src/info/600482d161 - self._broken_fk_pragma_quotes = \ - self.dbapi.sqlite_version_info < (3, 6, 14) - + self._broken_fk_pragma_quotes = ( + self.dbapi.sqlite_version_info < (3, 6, 14)) _isolation_lookup = { 'READ UNCOMMITTED': 1, - 'SERIALIZABLE': 0 + 'SERIALIZABLE': 0, } def set_isolation_level(self, connection, level): @@ -743,7 +735,7 @@ class SQLiteDialect(default.DefaultDialect): while not cursor.closed and cursor.fetchone() is not None: pass - return (row is not None) + return row is not None @reflection.cache def get_view_names(self, connection, schema=None, **kw): @@ -769,7 +761,6 @@ class SQLiteDialect(default.DefaultDialect): @reflection.cache def get_view_definition(self, connection, view_name, schema=None, **kw): - quote = self.identifier_preparer.quote_identifier if schema is not None: qschema = self.identifier_preparer.quote_identifier(schema) master = '%s.sqlite_master' % qschema @@ -807,17 +798,14 @@ class SQLiteDialect(default.DefaultDialect): rows = c.fetchall() columns = [] for row in rows: - (name, type_, nullable, default, primary_key) = \ - (row[1], row[2].upper(), not row[3], - row[4], row[5]) + (name, type_, nullable, default, primary_key) = ( + row[1], row[2].upper(), not row[3], row[4], row[5]) columns.append(self._get_column_info(name, type_, nullable, - default, primary_key)) + default, primary_key)) return columns - def _get_column_info(self, name, type_, nullable, - default, primary_key): - + def _get_column_info(self, name, type_, nullable, default, primary_key): match = re.match(r'(\w+)(\(.*?\))?', type_) if match: coltype = match.group(1) @@ -844,7 +832,7 @@ class SQLiteDialect(default.DefaultDialect): 'nullable': nullable, 'default': default, 'autoincrement': default is None, - 'primary_key': primary_key + 'primary_key': primary_key, } @reflection.cache @@ -878,8 +866,8 @@ class SQLiteDialect(default.DefaultDialect): return fkeys def _parse_fk(self, fks, fkeys, numerical_id, rtbl, lcol, rcol): - # sqlite won't return rcol if the table - # was created with REFERENCES , no col + # sqlite won't return rcol if the table was created with REFERENCES + # , no col if rcol is None: rcol = lcol @@ -894,7 +882,7 @@ class SQLiteDialect(default.DefaultDialect): 'constrained_columns': [], 'referred_schema': None, 'referred_table': rtbl, - 'referred_columns': [] + 'referred_columns': [], } fkeys.append(fk) fks[numerical_id] = fk -- cgit v1.2.1 From e47f99450378a7b1ea29a8493c0692bcf8669da3 Mon Sep 17 00:00:00 2001 From: Erich Blume Date: Mon, 3 Feb 2014 16:55:00 -0800 Subject: SQLite dialect - support relection from affinity SQLite allows column types that aren't technically understood in sqlite by using 'data affinity', which is an algorithm for converting column types in to some sort of useful type that can be stored and retrieved from the db. Unfortunatly, this breaks reflection since we (previously) expected a sqlite db to reflect column types that we permit in the `ischema_names` for that dialect. This patch changes the logic for 'unknown' column types during reflection to instead run through SQLite's data affinity algorithm, and assigns appropriate types from that. It also expands the matching for column type to include column types with spaces (strongly discouraged but allowed by sqlite) and also completely empty column types (in which case the NullType is assigned, which sqlite will treat as a Blob - or rather, Blob is treated as NullType). These changes mean that SQLite will never raise an error for an unknown type during reflection - there will always be some 'useful' type returned, which follows the spirit of SQLite (accomodation before sanity!). --- lib/sqlalchemy/dialects/sqlite/base.py | 47 ++++++++++++++++++++++++++-------- 1 file changed, 36 insertions(+), 11 deletions(-) (limited to 'lib/sqlalchemy/dialects/sqlite') diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index 258b7944f..3357c81ca 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -403,6 +403,7 @@ ischema_names = { 'CHAR': sqltypes.CHAR, 'DATE': sqltypes.DATE, 'DATETIME': sqltypes.DATETIME, + 'DOUBLE': sqltypes.FLOAT, 'DECIMAL': sqltypes.DECIMAL, 'FLOAT': sqltypes.FLOAT, 'INT': sqltypes.INTEGER, @@ -806,22 +807,17 @@ class SQLiteDialect(default.DefaultDialect): return columns def _get_column_info(self, name, type_, nullable, default, primary_key): - match = re.match(r'(\w+)(\(.*?\))?', type_) + match = re.match(r'([\w ]+)(\(.*?\))?', type_) if match: coltype = match.group(1) args = match.group(2) else: - coltype = "VARCHAR" + coltype = '' args = '' - try: - coltype = self.ischema_names[coltype] - if args is not None: - args = re.findall(r'(\d+)', args) - coltype = coltype(*[int(a) for a in args]) - except KeyError: - util.warn("Did not recognize type '%s' of column '%s'" % - (coltype, name)) - coltype = sqltypes.NullType() + coltype = self._resolve_col_affinity(coltype) + if args is not None: + args = re.findall(r'(\d+)', args) + coltype = coltype(*[int(a) for a in args]) if default is not None: default = util.text_type(default) @@ -835,6 +831,35 @@ class SQLiteDialect(default.DefaultDialect): 'primary_key': primary_key, } + def _resolve_col_affinity(self, coltype): + """Return a data type from a reflected column, using affinity tules. + + SQLite's goal for universal compatability introduces some complexity + during reflection, as a column's defined type might not actually be a + type that SQLite understands - or indeed, my not be defined *at all*. + Internally, SQLite handles this with a 'data type affinity' for each + column definition, mapping to one of 'TEXT', 'NUMERIC', 'INTEGER', + 'REAL', or 'NONE' (raw bits). The algorithm that determines this is + listed in http://www.sqlite.org/datatype3.html section 2.1. + + This method allows SQLAlchemy to support that algorithm, while still + providing access to smarter reflection utilities by regcognizing + column definitions that SQLite only supports through affinity (like + DATE and DOUBLE). + """ + if coltype in self.ischema_names: + return self.ischema_names[coltype] + if 'INT' in coltype: + return sqltypes.INTEGER + elif 'CHAR' in coltype or 'CLOB' in coltype or 'TEXT' in coltype: + return sqltypes.TEXT, + elif 'BLOB' in coltype or not coltype: + return sqltypes.NullType + elif 'REAL' in coltype or 'FLOA' in coltype or 'DOUB' in coltype: + return sqltype.REAL + else: + return sqltypes.NUMERIC + @reflection.cache def get_pk_constraint(self, connection, table_name, schema=None, **kw): cols = self.get_columns(connection, table_name, schema, **kw) -- cgit v1.2.1