# see http://pynash.org/2013/03/06/timing-and-profiling.html for setup of profiling magics

import sys
sys.path.insert(0, '../src')
import petl; print petl.VERSION
from petl.fluent import etl
import psycopg2
import MySQLdb

tbl_dummy_data = etl().dummytable(100000)
tbl_dummy_data.look()

%memit print tbl_dummy_data.nrows()

psql_connection = psycopg2.connect(host='localhost', dbname='petl', user='petl', password='petl')

cursor = psql_connection.cursor()
cursor.execute('DROP TABLE IF EXISTS issue_219;')
cursor.execute('CREATE TABLE issue_219 (foo INTEGER, bar TEXT, baz FLOAT);')

%memit -r1 tbl_dummy_data.progress(10000).todb(psql_connection, 'issue_219')

# memory usage using default cursor
%memit print etl.fromdb(psql_connection, 'select * from issue_219 order by foo').look(2)

# memory usage using server-side cursor
%memit print etl.fromdb(lambda: psql_connection.cursor(name='server-side'), 'select * from issue_219 order by foo').look(2)

mysql_connection = MySQLdb.connect(host='127.0.0.1', db='petl', user='petl', passwd='petl')

cursor = mysql_connection.cursor()
cursor.execute('SET SQL_MODE=ANSI_QUOTES')
cursor.execute('DROP TABLE IF EXISTS issue_219;')
cursor.execute('CREATE TABLE issue_219 (foo INTEGER, bar TEXT, baz FLOAT);')

%memit -r1 tbl_dummy_data.progress(10000).todb(mysql_connection, 'issue_219')

# memory usage with default cursor
%memit print etl.fromdb(mysql_connection, 'select * from issue_219 order by foo').look(2)

# memory usage with server-side cursor
%memit print etl.fromdb(lambda: mysql_connection.cursor(MySQLdb.cursors.SSCursor), 'select * from issue_219 order by foo').look(2)