I am using SQLAlchemy to work with a remote database that uses a strange timestamp format--it stores timestamps as double-precision milliseconds since epoch. I'd like to work with python datetime objects, so I wrote getter/setter methods in my model, following this gist:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import synonym
from sqlalchemy.dialects.mysql import DOUBLE
import datetime
Base = declarative_base()
class Table(Base):
__tablename__ = "table"
id = Column(Integer, primary_key=True)
_timestamp = Column("timestamp", DOUBLE(asdecimal=False))
@property
def timestamp(self):
return datetime.datetime.utcfromtimestamp(float(self._timestamp)/1000.)
@timestamp.setter
def timestamp(self, dt):
self._timestamp = float(dt.strftime("%s"))*1000.
timestamp = synonym('_timestamp', descriptor=timestamp)
This works great for inserting new rows into the table and working with objects from the table:
>>> table = session.query(Table).first()
<Table id=1>
>>> table.timestamp
datetime.datetime(2016, 6, 27, 16, 9, 3, 320000)
>>> table._timestamp
1467043743320.0
However, it breaks down when I try to use a datetime in a filter expression:
>>> july = datetime.datetime(2016, 7, 1)
>>> old = session.query(Table).filter(Table.timestamp < july).first()
/lib/python2.7/site-packages/sqlalchemy/engine/default.py:450: Warning: Truncated incorrect DOUBLE value: '2016-07-01 00:00:00'
>>> july_flt = float(july.strftime("%s"))*1000.
>>> old = session.query(Table).filter(Table.timestamp < july_flt).first()
<Table id=1>
I assume this is because my getter/setter methods apply to instances of the table class, but don't change the behavior of the class itself. I've tried rewriting using a hybrid property instead of a synonym:
from sqlalchemy.ext.hybrid import hybrid_property
class Table(Base):
__tablename__ = "table"
id = Column(Integer, primary_key=True)
_timestamp = Column("timestamp", DOUBLE(asdecimal=False))
@hybrid_property
def timestamp(self):
return datetime.datetime.utcfromtimestamp(float(self._timestamp)/1000.)
@timestamp.setter
def timestamp(self, dt):
self._timestamp = float(dt.strftime("%s"))*1000.
Again, this works with Table instances, but fails on a query--now it's hitting my getter method when I run the query:
>>> july = datetime.datetime(2016, 7, 1)
>>> old = session.query(Table).filter(Table.timestamp < july).first()
Traceback:
File "models.py", line 42, in timestamp
return datetime.datetime.utcfromtimestamp(float(self._timestamp)/1000.)
TypeError: float() argument must be a string or a number
With the debugger, I can see that the getter is receiving the Table._timestamp class (not a specific Table._timestamp, and not 'july').
I see that I could use the hybrid_property.expression decorator to define a SQL expression for converting timestamps into datetime, but what I'd really like is to convert the datetime into a timestamp on the python side, then run the query using timestamps. In other words, I'd like to use datetimes everywhere (including in queries), but have everything done with the microsecond timestamps on the SQL side. How can I do this?
first() applies a limit of one within the generated SQL, so that only one primary entity row is generated on the server side (note this may consist of multiple result rows if join-loaded collections are present). Calling Query. first() results in an execution of the underlying query.
All SELECT statements generated by SQLAlchemy ORM are constructed by Query object. It provides a generative interface, hence successive calls return a new Query object, a copy of the former with additional criteria and options associated with it.
Lazy parameter determines how the related objects get loaded when querying through relationships. Below listed are the four main lazy parameters. Typically when you query the database, the data get loaded at once; however, lazy parameter allows you to alternate the way they get loaded. lazy = 'select' (or True)
The grouping is done with the group_by() query method, which takes the column to use for the grouping as an argument, same as the GROUP BY counterpart in SQL. The statement ends by calling subquery() , which tells SQLAlchemy that our intention for this query is to use it inside a bigger query instead of on its own.
You have to use a custom type, which isn't as scary as it sounds.
from sqlalchemy.types import TypeDecorator
class DoubleTimestamp(TypeDecorator):
impl = DOUBLE
def __init__(self):
TypeDecorator.__init__(self, as_decimal=False)
def process_bind_param(self, value, dialect):
return value.replace(tzinfo=datetime.timezone.utc).timestamp() * 1000
def process_result_value(self, value, dialect):
return datetime.datetime.utcfromtimestamp(value / 1000)
Then Table
becomes:
class Table(Base):
__tablename__ = "table"
id = Column(Integer, primary_key=True)
timestamp = Column(DoubleTimestamp)
And then everything you mention works. You insert, select and compare with datetime
s but it's stored as a DOUBLE
.
Here I've used different logic for converting between timestamps since strftime('%s')
isn't the correct solution. It's a different question which has been answered correctly here. Oh and I noticed you said microseconds but only convert to milliseconds in the code you posted, unless it was a slip of the tongue 😉.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With