Sqlalchemy returning bools instead of ints

user_5339255 0
Viewed 68

Not really sure what's going on here.

What the table looks like:

class LogTable(Base):
    __tablename__ = 'log_table'

    id = Column(INTEGER(11), primary_key=True)
    version = Column(VARCHAR(100))
    status = Column(VARCHAR(20))

The 'pretty' sql alch query.:

query = session.query(LogTable.version,
                      label('total_jobs', func.count(LogTable.version)),
                      label('success_jobs', func.sum(LogTable.status == 'SUCCESS')),
                      label('fail_jobs', func.sum(LogTable.status == 'FAILURE'))
                      ).group_by(JobLog.version)

The value of query before the '.all()' method is called. ()

print(query)
SELECT log_table.version AS log_table_version, 
count(log_table.version) AS total_jobs,
sum(log_table.status = %(status_1)s) AS success_jobs,
sum(log_table.status = %(status_2)s) AS fail_jobs
FROM job_logs GROUP BY log_table.version

What the query should be returning (and does return from the SQL console assuming you fill in %(status_)s with the correct values.):

version, total_jobs, success_jobs, fail_jobs
0421f178c39df89890f97663509ce2a931a44fba,206,181,25
07e3ee0af26603c9a2543d1644742abcde664391,208,187,21
08eaf7ec41c022d77d04d108d3844fca2fa426a0,78,53,25
0cce212ddf351acfbdfae795ce0c5402e1a24540,484,314,170
15223fb4dcc4312bceac75755662a7d2eeb4fbb3,2345,1463,882
1940ad381c10fd3e7fce3e9f6e13210807e95eb8,208,164,44

What SQL alch is returning for some reason:

print(query.all())
[('0421f178c39df89890f97663509ce2a931a44fba', 206, True, True), 
('07e3ee0af26603c9a2543d1644742abcde664391', 208, True, True), 
('08eaf7ec41c022d77d04d108d3844fca2fa426a0', 78, True, True), 
('0cce212ddf351acfbdfae795ce0c5402e1a24540', 484, True, True), 
('15223fb4dcc4312bceac75755662a7d2eeb4fbb3', 2345, True, True),]
sqlalchemy mysql python
1 Answers

After some time in the #sqlalchemy freenode. I was informed that I needed to overwrite the type since I was using a bool as the argument.

Changes to the location of the label code can be ignored.

Here is the working solution.

from sqlalchemy.types import Integer
query = session.query(LogTable.version,
            func.count(LogTable.version).label('total_jobs'),
            func.sum(LogTable.status == 'SUCCESS', type_=Integer).label('success_jobs'),
            func.sum(LogTable.status == 'FAILURE', type_=Integer).label('fail_jobs'))
           .group_by(LogTable.version).order_by(desc(LogTable.create_time))
user_53392550
Related