diff options
| -rw-r--r-- | CHANGES | 11 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/sqlite/base.py | 19 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/sqlite/pysqlite.py | 69 | ||||
| -rw-r--r-- | test/dialect/test_sqlite.py | 25 |
4 files changed, 114 insertions, 10 deletions
@@ -65,7 +65,16 @@ CHANGES - mssql - Re-established initial support for pymssql. - + +- sqlite + - Added "native_datetime=True" flag to create_engine(). + This will cause the DATE and TIMESTAMP types to skip + all bind parameter and result row processing, under + the assumption that PARSE_DECLTYPES has been enabled + on the connection. Note that this is not entirely + compatible with the "func.current_date()", which + will be returned as a string. [ticket:1685] + 0.6beta1 ======== - Major Release diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index 93e62ec24..696f65a6c 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -76,19 +76,16 @@ class _SLNumeric(_NumericMixin, sqltypes.Numeric): class _SLFloat(_NumericMixin, sqltypes.Float): pass -# since SQLite has no date types, we're assuming that SQLite via ODBC -# or JDBC would similarly have no built in date support, so the "string" based logic -# would apply to all implementing dialects. class _DateTimeMixin(object): _reg = None _storage_format = None - + def __init__(self, storage_format=None, regexp=None, **kwargs): if regexp is not None: self._reg = re.compile(regexp) if storage_format is not None: self._storage_format = storage_format - + def _result_processor(self, fn): rmatch = self._reg.match # Even on python2.6 datetime.strptime is both slower than this code @@ -342,7 +339,7 @@ class SQLiteDialect(default.DefaultDialect): supports_default_values = True supports_empty_insert = False supports_cast = True - + default_paramstyle = 'qmark' statement_compiler = SQLiteCompiler ddl_compiler = SQLiteDDLCompiler @@ -352,7 +349,7 @@ class SQLiteDialect(default.DefaultDialect): colspecs = colspecs isolation_level = None - def __init__(self, isolation_level=None, **kwargs): + def __init__(self, isolation_level=None, native_datetime=False, **kwargs): default.DefaultDialect.__init__(self, **kwargs) if isolation_level and isolation_level not in ('SERIALIZABLE', 'READ UNCOMMITTED'): @@ -360,7 +357,13 @@ class SQLiteDialect(default.DefaultDialect): "Valid isolation levels for sqlite are 'SERIALIZABLE' and " "'READ UNCOMMITTED'.") 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 ?) + self.native_datetime = native_datetime + def visit_pool(self, pool): if self.isolation_level is not None: class SetIsolationLevel(object): diff --git a/lib/sqlalchemy/dialects/sqlite/pysqlite.py b/lib/sqlalchemy/dialects/sqlite/pysqlite.py index a1873f33a..3a9cdcda3 100644 --- a/lib/sqlalchemy/dialects/sqlite/pysqlite.py +++ b/lib/sqlalchemy/dialects/sqlite/pysqlite.py @@ -55,6 +55,37 @@ The sqlite ``:memory:`` identifier is the default if no filepath is present. Sp # in-memory database e = create_engine('sqlite://') +Compatibility with sqlite3 "native" date and datetime types +----------------------------------------------------------- + +The pysqlite driver includes the sqlite3.PARSE_DECLTYPES and +sqlite3.PARSE_COLNAMES options, which have the effect of any column +or expression explicitly cast as "date" or "timestamp" will be converted +to a Python date or datetime object. The date and datetime types provided +with the pysqlite dialect are not currently compatible with these options, +since they render the ISO date/datetime including microseconds, which +pysqlite's driver does not. Additionally, SQLAlchemy does not at +this time automatically render the "cast" syntax required for the +freestanding functions "current_timestamp" and "current_date" to return +datetime/date types natively. Unfortunately, pysqlite +does not provide the standard DBAPI types in `cursor.description`, +leaving SQLAlchemy with no way to detect these types on the fly +without expensive per-row type checks. + +Usage of PARSE_DECLTYPES can be forced if one configures +"native_datetime=True" on create_engine():: + + engine = create_engine('sqlite://', + connect_args={'detect_types': sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES}, + native_datetime=True + ) + +With this flag enabled, the DATE and TIMESTAMP types (but note - not the DATETIME +or TIME types...confused yet ?) will not perform any bind parameter or result +processing. Execution of "func.current_date()" will return a string. +"func.current_timestamp()" is registered as returning a DATETIME type in +SQLAlchemy, so this function still receives SQLAlchemy-level result processing. + Threading Behavior ------------------ @@ -104,15 +135,50 @@ always represented by an actual database result string. """ -from sqlalchemy.dialects.sqlite.base import SQLiteDialect +from sqlalchemy.dialects.sqlite.base import SQLiteDialect, DATETIME, DATE from sqlalchemy import schema, exc, pool from sqlalchemy.engine import default from sqlalchemy import types as sqltypes from sqlalchemy import util + +class _SQLite_pysqliteTimeStamp(DATETIME): + def bind_processor(self, dialect): + if dialect.native_datetime: + return None + else: + return DATETIME.bind_processor(self, dialect) + + def result_processor(self, dialect, coltype): + if dialect.native_datetime: + return None + else: + return DATETIME.result_processor(self, dialect, coltype) + +class _SQLite_pysqliteDate(DATE): + def bind_processor(self, dialect): + if dialect.native_datetime: + return None + else: + return DATE.bind_processor(self, dialect) + + def result_processor(self, dialect, coltype): + if dialect.native_datetime: + return None + else: + return DATE.result_processor(self, dialect, coltype) + class SQLite_pysqlite(SQLiteDialect): default_paramstyle = 'qmark' poolclass = pool.SingletonThreadPool + + colspecs = util.update_copy( + SQLiteDialect.colspecs, + { + sqltypes.Date:_SQLite_pysqliteDate, + sqltypes.TIMESTAMP:_SQLite_pysqliteTimeStamp, + } + ) # Py3K #description_encoding = None @@ -135,6 +201,7 @@ class SQLite_pysqlite(SQLiteDialect): self.supports_default_values = False self.supports_cast = (self.dbapi is None or vers(self.dbapi.sqlite_version) >= vers("3.2.3")) + @classmethod def dbapi(cls): try: diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py index 2aca6b776..0012a8acb 100644 --- a/test/dialect/test_sqlite.py +++ b/test/dialect/test_sqlite.py @@ -51,7 +51,32 @@ class TestTypes(TestBase, AssertsExecutionResults): rp = sldt.result_processor(None, None) eq_(rp(bp(dt)), dt) + + def test_native_datetime(self): + dbapi = testing.db.dialect.dbapi + + connect_args={'detect_types': dbapi.PARSE_DECLTYPES|dbapi.PARSE_COLNAMES} + engine = engines.testing_engine(options={'connect_args':connect_args, 'native_datetime':True}) + t = Table('datetest', MetaData(), + Column('id', Integer, primary_key=True), + Column('d1', Date), + Column('d2', TIMESTAMP) + ) + t.create(engine) + try: + engine.execute(t.insert(), {'d1':datetime.date(2010, 5, 10), 'd2':datetime.datetime(2010, 5, 10, 12, 15, 25)}) + row = engine.execute(t.select()).first() + eq_(row, (1, datetime.date(2010, 5, 10), datetime.datetime(2010, 5, 10, 12, 15, 25))) + + r = engine.execute(func.current_date()).scalar() + assert isinstance(r, basestring) + + finally: + t.drop(engine) + engine.dispose() + + def test_no_convert_unicode(self): """test no utf-8 encoding occurs""" |