#!/usr/bin/env python

from sqlalchemy import create_engine

# Connection settings
settings = {
    'userName': "root",           # The name of the MySQL account to use (or empty for anonymous)
    'password': "root",           # The password for the MySQL account (or empty for anonymous)
    'serverName': "localhost",    # The name of the computer running MySQL
    'portNumber': 3306,           # The port of the MySQL server (default is 3306)
    'dbName': "test",             # The name of the database we are testing with (this default is installed with MySQL)
}
tableName = "DBConnection_TEST"           # The name of the table we are testing with

# Connect to the database
conn = create_engine('mysql://{0[userName]}:{0[password]}@{0[serverName]}:{0[portNumber]}/{0[dbName]}'.format(settings))
print 'Connected to database'

# Create a table
create_string = """
    CREATE TABLE {} (
    ID INTEGER NOT NULL,
    NAME varchar(40) NOT NULL,
    STREET varchar(40) NOT NULL,
    CITY varchar(20) NOT NULL,
    STATE char(2) NOT NULL,
    ZIP char(5),
    PRIMARY KEY (ID))
    """.format(tableName)
conn.execute(create_string)
print 'Created a table'

# Insert some records into the table
for i in range(10):
    insert_string = """
        INSERT INTO {0}
        (ID, NAME, STREET, CITY, STATE, ZIP)
        VALUES
        ({1}, 'test', 'test{1}', 'Boston', 'MA', 12345)
        """.format(tableName, i)
    conn.execute(insert_string)
print 'Inserted some data'

# Select data from the table
select_string = "SELECT name, city, state, zip FROM {}".format(tableName)
for (name, city, state, zipcode) in conn.execute(select_string):
    print name, city, state, zipcode

# Drop the table
drop_string = "DROP TABLE {}".format(tableName)
conn.execute(drop_string)
print 'Dropped the table'