<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 SQL Alchemy Core Examples
This file contains SQLAlchemy core examples.
Test we have SQL Alchemy
Step1:  Fetch an SQLite engine and create an in memory database
Step2:  Now lets make a couple of tables and do some queries
Step3:  ... and let's add a couple of users.  First we make a statement for the insert, and then we would execute it.
Step4:  ... and then actually put them into the database
Step5:  ... and pull the data back out.
Step6:  So the results are an iterator which gives up tuples.
However, we can get a list of dictionaries from the iterator
Step7:  Lets add some item data
Step8:  Now lets now join the two tables together
Step9:  That's everything in both tables. But we have id_user in the second table.
Let's do a very simple filter
Step10:  Functions?
Step11:  That's great, but we have a convenience function as well
Step12:  Group by
Step13:  A final 'fun' query
<ASSISTANT_TASK:>
Python Code:
import sqlalchemy
sqlalchemy.__version__
Explanation: SQL Alchemy Core Examples
This file contains SQLAlchemy core examples.
Test we have SQL Alchemy
End of explanation
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
Explanation: Fetch an SQLite engine and create an in memory database
End of explanation
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
metadata = MetaData()
user = Table('user', metadata,
    Column('id_user', Integer, primary_key=True),
    Column('name', String),
    Column('age', Integer))
item = Table('item', metadata,
    Column('id_item', Integer, primary_key=True),
    Column('id_user', Integer, ForeignKey('user.id_user')),
    Column('thing', String))
metadata.create_all(engine)
Explanation: Now lets make a couple of tables and do some queries
End of explanation
people = [
    (1, 'Bob', '20'),
    (2, 'Sally', '25'),
    (3, 'John', '30')]
insert = user.insert()
print(insert)
for p in people:
    stmt = insert.values(p)
    print(stmt.compile().params)
Explanation: ... and let's add a couple of users.  First we make a statement for the insert, and then we would execute it.
End of explanation
connection = engine.connect()
for p in people:
    connection.execute(insert.values(p))
Explanation: ... and then actually put them into the database:
End of explanation
from sqlalchemy import select
users = connection.execute(select([user]))
print(users)
print(list(users))
Explanation: ... and pull the data back out.
End of explanation
users = connection.execute(select([user]))
for u in users:
    i = u.items()
#     print(i)
    print(dict(i))
Explanation: So the results are an iterator which gives up tuples.
However, we can get a list of dictionaries from the iterator:
End of explanation
items = (
    (1, 1, 'Peanuts'),
    (2, 1, 'VW'),
    (3, 1, 'iPad'),
    (4, 2, 'Raisins'),
    (5, 2, 'Fiat'),
    (6, 2, 'Nexus 10'),
    (7, 2, 'Timex'),
    (8, 3, 'Caviar'),
    (9, 3, 'Porche'),
    (10, 3, 'Surface Pro'),
    (11, 3, 'Rolex'),
    (12, 3, 'Boat'),
    (13, 3, 'Plane'))
insert = item.insert()
for i in items:
    connection.execute(insert.values(i))
for x in connection.execute(select([item])):
    print(x)
Explanation: Lets add some item data
End of explanation
stmt = select([user, item], use_labels=True)
for s in connection.execute(stmt):
    print(s)
#     print(s.items())
Explanation: Now lets now join the two tables together:
End of explanation
stmt = select([user, item]).where(user.c.id_user == item.c.id_user)
print(stmt)
for s in connection.execute(stmt):
    print(s)
Explanation: That's everything in both tables. But we have id_user in the second table.
Let's do a very simple filter:
End of explanation
from sqlalchemy import func
stmt = select([func.count(user.c.id_user)])
result = connection.execute(stmt)
print(tuple(result))
Explanation: Functions?
End of explanation
result = connection.execute(stmt).scalar()
print(result)
Explanation: That's great, but we have a convenience function as well:
End of explanation
stmt = (select([user, func.count(user.c.id_user).label('item_count')])
        .select_from(user.join(item))
        .group_by(user.c.id_user))
print(stmt)
for s in connection.execute(stmt):
    print(s.items())
Explanation: Group by
End of explanation
stmt1 = select([item.c.id_user]).where(item.c.thing.ilike('boat'))
print(connection.execute(stmt1).fetchone())
stmt2 = select([user]).where(user.c.id_user.in_(stmt1.alias()))
print(stmt2)
print(connection.execute(stmt2).fetchone())
Explanation: A final 'fun' query
End of explanation
<END_TASK>