<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Using SQLAlchemy to Talk to a Database
SqlAlchemy helps you use a database to store and retrieve information from python.  It abstracts the specific storage engine from te way you use it - so it doesn't care if you end up using MySQL, SQLite, or whatever else. In addition, you can use core and the object-relational mapper (ORM) to avoid writing any SQL at all.  The SQLAlchemy homepage has lots of good examples and full documentation.
Step1:  Basic SQL Generation
The core library generates SQL for you.  Read more about it on their expression language tutorial page. Below are some basic examples.
Creating a Table
Read more about defining and creating tables.
Step2:  Inserting Data
Read more about generating SQL insert statements.
Step3:  Retrieving Data
Read more about using SQL select statments.
Step4:  ORM
You can use their ORM library to handle the translation into full-fledged python objects.  This can help you build the Model for you MVC solution.
Step5:  Creating a class mapping
Read more about creating a mapping.
Step6:  Creating a connection and session
Read more about creating this stuff.
Step7:  Inserting Data
Read more about inserting data with an ORM.
Step8:  Retrieving Data
Read more about retrieving data from the db via an ORM class.
<ASSISTANT_TASK:>
Python Code:
from sqlalchemy import *
import datetime
Explanation: Using SQLAlchemy to Talk to a Database
SqlAlchemy helps you use a database to store and retrieve information from python.  It abstracts the specific storage engine from te way you use it - so it doesn't care if you end up using MySQL, SQLite, or whatever else. In addition, you can use core and the object-relational mapper (ORM) to avoid writing any SQL at all.  The SQLAlchemy homepage has lots of good examples and full documentation.
End of explanation
# add `echo=True` to see log statements of all the SQL that is generated
engine = create_engine('sqlite:///:memory:',echo=True) # just save the db in memory for now (ie. not on disk)
metadata = MetaData()
# define a table to use
queries = Table('query', metadata,
    Column('id', Integer, primary_key=True),
    Column('keywords', String(400), nullable=False),
    Column('timestamp', DateTime, default=datetime.datetime.now),
)
metadata.create_all(engine) # and create the tables in the database
Explanation: Basic SQL Generation
The core library generates SQL for you.  Read more about it on their expression language tutorial page. Below are some basic examples.
Creating a Table
Read more about defining and creating tables.
End of explanation
insert_stmt = queries.insert()
str(insert_stmt) # see an example of what this will do
insert_stmt = queries.insert().values(keywords="puppies")
str(insert_stmt)
db_conn = engine.connect()
result = db_conn.execute(insert_stmt)
result.inserted_primary_key # print out the primary key it was assigned
insert_stmt = queries.insert().values(keywords="kittens")
result = db_conn.execute(insert_stmt)
result.inserted_primary_key # print out the primary key it was assigned
Explanation: Inserting Data
Read more about generating SQL insert statements.
End of explanation
from sqlalchemy.sql import select
select_stmt = select([queries])
results = db_conn.execute(select_stmt)
for row in results:
    print row
select_stmt = select([queries]).where(queries.c.id==1)
for row in db_conn.execute(select_stmt):
    print row
select_stmt = select([queries]).where(queries.c.keywords.like('p%'))
for row in db_conn.execute(select_stmt):
    print row
Explanation: Retrieving Data
Read more about using SQL select statments.
End of explanation
import datetime
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
Explanation: ORM
You can use their ORM library to handle the translation into full-fledged python objects.  This can help you build the Model for you MVC solution.
End of explanation
class Query(Base):
    __tablename__ = 'queries'
    id = Column(Integer, primary_key=True)
    keywords = Column(String(400))
    timestamp = Column(DateTime,default=datetime.datetime.now)
    def __repr__(self):
        return "<Query(keywords='%s')>" % (self.keywords)
Query.__table__
Explanation: Creating a class mapping
Read more about creating a mapping.
End of explanation
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()
Explanation: Creating a connection and session
Read more about creating this stuff.
End of explanation
query = Query(keywords="iguana")
query.keywords
my_session.add(query)
my_session.commit()
query.id
Explanation: Inserting Data
Read more about inserting data with an ORM.
End of explanation
for q in my_session.query(Query).order_by(Query.timestamp):
    print q
query1 = Query(keywords="robot")
query2 = Query(keywords="puppy")
my_session.add_all([query1,query2])
my_session.commit()
for q in my_session.query(Query).order_by(Query.timestamp):
    print q
for q in my_session.query(Query).filter(Query.keywords.like('r%')):
    print q
import mediacloud, datetime
mc = mediacloud.api.MediaCloud('48d9fd877527e6c8c5ff25553c124b1634b8b2926310e2d259d6518221e2a5f2')
agreement = mc.sentenceCount('( colombia AND peace AND agreement)', solr_filter=[mc.publish_date_query( datetime.date( 2015, 10, 1), datetime.date( 2016, 12, 2) ), 'tags_id_media:1' ])
print agreement['count'] 
Explanation: Retrieving Data
Read more about retrieving data from the db via an ORM class.
End of explanation
<END_TASK>