CODEWITHSUNDEEP
pythonsqlalchemy
soniccool
soniccool

Reputation: 6058

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 == issue_id).order_by(desc("created")).first()

But it returns the entire record as so and I have no idea how to export created out.

<IssueComment id=32 comment=ay user_id=578042a3-d879-4b32-96a9-10f9aabe152c issue_id=19 created=2018-07-17 20:17:09.653809 visibility=visible abuse_status=0>

If i try calling created in cls.created as so

return model.Session.query(cls.created).filter(cls.issue_id == issue_id).order_by(desc("created")).first()

and it exports a weird datetime version. I just want the raw version

(datetime.datetime(2018, 7, 17, 20, 17, 9, 653809),)

How can i export the value for created out as the raw string?

Upvotes: 1

Views: 1805

Answers (2)

snakecharmerb
snakecharmerb

Reputation: 55620

SqlAlchemy queries for attributes (as opposed to the entire model) return KeyedTuples (or Row objects in SQLAlchemy 1.4) 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),)

Upvotes: 7

abarnert
abarnert

Reputation: 365697

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.

Upvotes: 4

Related Questions