Sqlalchemy Returns Weird Data

I am trying to export a date from a table. I want to just pull the raw created object out. I am running the query below: return model.Session.query(cls).filter(cls.issue_id == issu

Solution 1:

SqlAlchemy queries for attributes (as opposed to the entire model) return KeyedTuples which behave like a namedtuple. Like normal Python tuples, a tuple with a single element is constructed by a trailing comma. This is why your result (datetime.datetime(2018, 7, 17, 20, 17, 9, 653809),) shows the datetime object followed by a comma.

>>> row = session.query(User.creation_timestamp).first()
>>> row
(datetime.datetime(2018, 4, 22, 9, 20, 56),)  # <- trailing comma because it's a tuple

Values can be accessed by index

>>>row[0]
datetime.datetime(2018, 4, 22, 9, 20, 56)

Or by name

>>>row.creation_timestamp
datetime.datetime(2018, 4, 22, 9, 20, 56)

The values are python objects with all their usual methods

>>> row.creation_timestamp.isoformat()
'2018-04-22T09:20:56'>>> str(row.creation_timestamp)
'2018-04-22 09:20:56'

It is a little confusing that the tutorial says that .first()

applies a limit of one and returns the first result as a scalar

and the example shows a single object being returned, like this:

>>> q = session.query(User).first()
>>> q
<User object at 0x7f97d8c6b590>

whereas calling first on your query by attribute returns a value wrapped in a tuple. It makes sense if you consider the tuple to be a row in the resultset. If you called .all() on your query you would get a list of tuples (rows). If you called .limit(1).all() on your query you would get a list containing a single tuple. So when you call .first() you are getting the first result from .limit(1).all(), which is a tuple.

>>>row = session.query(User.creation_timestamp).all()>>>row
[(datetime.datetime(2018, 4, 22, 9, 20, 56),), (datetime.datetime(2018, 4, 22, 9, 20, 56),), ...]

>>>row = session.query(User.creation_timestamp).limit(1).all()>>>row
[(datetime.datetime(2018, 4, 22, 9, 20, 56),)]

>>>row = session.query(User.creation_timestamp).first()>>>row
(datetime.datetime(2018, 4, 22, 9, 20, 56),)

Solution 2:

If the column is a datetime column, the raw value isn't a string, it's… well, a datetime.

In most database engines, under the covers, the actual raw value stored in the database for datetime columns is some kind of number. For example, IIRC, in Microsoft SQL Server, it's a 64-bit integer of decimicroseconds since 1901. But you don't want that number. What would you do with it?

If you want a string in some particular format, you can of course ask the database to format it for you to whatever string formats it accepts, but why?

Your engine and/or SQLAlchemy has represented the datetime value as a Python datetime object. That's smaller, faster to pass over the wire from the database, and more flexible (e.g., you can do comparisons and arithmetic with them) than a string.

And you can always format it to whatever string format you want. For example:

>>>d = datetime.datetime(2018, 7, 17, 20, 17, 9, 653809)>>>str(d)
'2018-07-17 20:17:09.653809'
>>>d.isoformat()
'2018-07-17T20:17:09.653809'
>>>d.strftime('%Y%m%d%H%M%S')
'20180717201709'
>>>d.strftime('%m/%d/%Y %I:%M %p%S')
'07/17/18 08:18 PM'

… and so on.