In [299]:

    

from sqlalchemy import *
import datetime
import mediacloud
mc = mediacloud.api.MediaCloud('MEDIA_CLOUD_KEY')


    

In [300]:

    

# add `echo=True` to see log statements of all the SQL that is generated
engine = create_engine('sqlite:///:memory:') # just save the db in memory for now (ie. not on disk)
metadata = MetaData()

def callMediaCloud(SearchPhrase):
    return mc.sentenceCount('('+SearchPhrase+')', solr_filter=[mc.publish_date_query( 
                datetime.date( 2010, 1, 1), datetime.date( 2015, 1, 1) ), 'media_sets_id:1' ])['count']

# define a table to use
queries = Table('queries', metadata,
    Column('id', Integer, primary_key=True),
    Column('keywords', String(400), nullable=False),
    Column('timestamp', DateTime, default=datetime.datetime.now),
    Column('count', Integer),
)
metadata.create_all(engine) # and create the tables in the database


    

In [301]:

    

insert_stmt = queries.insert()
str(insert_stmt) # see an example of what this will do


    

    
Out[301]:





'INSERT INTO queries (id, keywords, timestamp, count) VALUES (:id, :keywords, :timestamp, :count)'

In [302]:

    

insert_stmt = queries.insert().values(keywords="forest AND fire", count=callMediaCloud("forest AND fire"))
str(insert_stmt)


    

    
Out[302]:





'INSERT INTO queries (keywords, timestamp, count) VALUES (:keywords, :timestamp, :count)'

In [303]:

    

db_conn = engine.connect()
result = db_conn.execute(insert_stmt)
result.inserted_primary_key # print out the primary key it was assigned


    

    
Out[303]:





[1]

In [304]:

    

insert_stmt = queries.insert().values(keywords="deforestation", count=callMediaCloud("deforestation"))
result = db_conn.execute(insert_stmt)
result.inserted_primary_key # print out the primary key it was assigned


    

    
Out[304]:





[2]

In [305]:

    

from sqlalchemy.sql import select
select_stmt = select([queries])
results = db_conn.execute(select_stmt)
for row in results:
    print row


    

    




(1, u'forest AND fire', datetime.datetime(2015, 12, 16, 0, 50, 12, 41310), 7664)
(2, u'deforestation', datetime.datetime(2015, 12, 16, 0, 50, 13, 78109), 6985)

In [306]:

    

select_stmt = select([queries]).where(queries.c.id==1)
for row in db_conn.execute(select_stmt):
    print row


    

    




(1, u'forest AND fire', datetime.datetime(2015, 12, 16, 0, 50, 12, 41310), 7664)

In [307]:

    

select_stmt = select([queries]).where(queries.c.keywords.like('p%'))
for row in db_conn.execute(select_stmt):
    print row


    

In [308]:

    

counter = 0
select_stmt = select([queries])
results = db_conn.execute(select_stmt)
for row in results:
    counter+=row['count']

print counter


    

    




14649

In [309]:

    

import datetime
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.hybrid import hybrid_property
Base = declarative_base()


    

In [310]:

    

class Query(Base):
    __tablename__ = 'queries'
    id = Column(Integer, primary_key=True)
    keywords = Column(String(400))
    timestamp = Column(DateTime,default=datetime.datetime.now)
    count = Column(Integer)
    
    @hybrid_property
    def count(self):
        return callMediaCloud(self.keywords)

    def __repr__(self):
        return "<Query(keywords='%s' count='%d')>" % (self.keywords, self.count)
Query.__table__


    

    
Out[310]:





Table('queries', MetaData(bind=None), Column('id', Integer(), table=<queries>, primary_key=True, nullable=False), Column('keywords', String(length=400), table=<queries>), Column('timestamp', DateTime(), table=<queries>, default=ColumnDefault(<function <lambda> at 0x7f67d0c37230>)), schema=None)

In [311]:

    

engine = create_engine('sqlite:///:memory:') # just save the db in memory for now (ie. not on disk)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
my_session = Session()


    

In [312]:

    

query0 = Query(keywords="wherefore")
query1 = Query(keywords="insulate")
query2 = Query(keywords="electromagnetism")
query3 = Query(keywords="jejune")
query4 = Query(keywords="provocateur")
my_session.add_all([query0,query1,query2,query3,query4])
my_session.commit()
totalCount=0
for q in my_session.query(Query).order_by(Query.timestamp):
    print q
    totalCount+=q.count
print "Total Count is: " + str(totalCount)


    

    




<Query(keywords='wherefore' count='865')>
<Query(keywords='insulate' count='3473')>
<Query(keywords='electromagnetism' count='333')>
<Query(keywords='jejune' count='196')>
<Query(keywords='provocateur' count='3109')>
Total Count is: 7976

In [ ]:

    




    

In [ ]: