Counting rows by date in sql alchemy

Question

I am executing some code in python, but need to access some data from my database. This is my first time I have used sql alchemy

I have a table in my database called reports.bigjoin it has columns with the following types

id (varchar)
id2 (varchar)
ts_min (int4)
ts_local_min (int4)
10_meter_cell (int8)
ds (date)
ds_log (date)
ds_local (date)

I need to know the number of rows for a given set of dates. For example, within python I want to execute

x= select count(*) from reports.bigjoin where (ds>='2016-01-01' and ds<='2016-01-04')

My attempt so far has been

from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy import Integer, String,Date
from sqlalchemy.orm import  sessionmaker

engine = sqlalchemy.create_engine(url).connect()
Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()
metadata = sqlalchemy.MetaData(engine)

moz_bookmarks = Table('reports.bigjoin', metadata, 
                      Column('id', String, primary_key=True),
                      Column('id2', String),
                      Column('ts_min', Integer),
                      Column('ts_local', Integer),
                      Column('10m_cell', Integer),
                      Column('ds', Date),
                      Column('ds_log', Date),
                      Column('ds_local', Date)
                      )


 x = session.query(moz_bookmarks).filter(
    (moz_bookmarks.ds >= '2016-01-01', moz_bookmarks.ds <= '2016-01-04')).count()

this has failed. Any help would be greatly appreciated.

@lc123 The error is. 'Table' object has no attribute 'ds'. Am I connecting to that table correctly? — mikeL, May 29 '16 at 20:24

score 0 · Answer 1 · edited May 23 '17 at 12:22

0

After searching a bit and applying

How to use variables in SQL statement in Python?

I found that

connection = create_engine('url').connect()

result = connection.execute("select count(*) from  reports.bigjoin where (ds>= %s and ds<= %s)", (x,y))

solved the problem

edited May 23 '17 at 12:22

Community

1
1

answered May 29 '16 at 21:18

mikeL

1,094
2
12
24

Although it works, it is not really utilising *sqlalchemy*. – van May 30 '16 at 04:11

score 0 · Accepted Answer · answered May 30 '16 at 04:10

0

cnt = (
    session
    .query(func.count('*').label('cnt'))
    .filter(moz_bookmarks.c.ds >= '2016-01-01')
    .filter(moz_bookmarks.c.ds <= '2016-01-04')
)

print(cnt)

answered May 30 '16 at 04:10

van

74,297
13
168
171

Counting rows by date in sql alchemy

2 Answers2