def test_if_exists(self):
_skip_if_no_MySQLdb()
df_if_exists_1 = DataFrame({'col1': [1, 2], 'col2': ['A', 'B']})
df_if_exists_2 = DataFrame({'col1': [3, 4, 5], 'col2': ['C', 'D', 'E']})
table_name = 'table_if_exists'
sql_select = "SELECT * FROM %s" % table_name
def clean_up(test_table_to_drop):
"""
Drops tables created from individual tests
so no dependencies arise from sequential tests
"""
if sql.table_exists(test_table_to_drop, self.db, flavor='mysql'):
cur = self.db.cursor()
cur.execute("DROP TABLE %s" % test_table_to_drop)
cur.close()
# test if invalid value for if_exists raises appropriate error
self.assertRaises(ValueError,
sql.write_frame,
frame=df_if_exists_1,
con=self.db,
name=table_name,
flavor='mysql',
if_exists='notvalidvalue')
clean_up(table_name)
# test if_exists='fail'
sql.write_frame(frame=df_if_exists_1, con=self.db, name=table_name,
flavor='mysql', if_exists='fail')
self.assertRaises(ValueError,
sql.write_frame,
frame=df_if_exists_1,
con=self.db,
name=table_name,
flavor='mysql',
if_exists='fail')
# test if_exists='replace'
sql.write_frame(frame=df_if_exists_1, con=self.db, name=table_name,
flavor='mysql', if_exists='replace')
self.assertEqual(sql.tquery(sql_select, con=self.db),
[(1, 'A'), (2, 'B')])
sql.write_frame(frame=df_if_exists_2, con=self.db, name=table_name,
flavor='mysql', if_exists='replace')
self.assertEqual(sql.tquery(sql_select, con=self.db),
[(3, 'C'), (4, 'D'), (5, 'E')])
clean_up(table_name)
# test if_exists='append'
sql.write_frame(frame=df_if_exists_1, con=self.db, name=table_name,
flavor='mysql', if_exists='fail')
self.assertEqual(sql.tquery(sql_select, con=self.db),
[(1, 'A'), (2, 'B')])
sql.write_frame(frame=df_if_exists_2, con=self.db, name=table_name,
flavor='mysql', if_exists='append')
self.assertEqual(sql.tquery(sql_select, con=self.db),
[(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E')])
clean_up(table_name)
def test_write_row_by_row(self):
frame = tm.makeTimeDataFrame()
create_sql = sql.get_sqlite_schema(frame, 'test')
self.db.execute(create_sql)
cur = self.db.cursor()
ins = "INSERT INTO test VALUES (%s, %s, %s, %s)"
for idx, row in frame.iterrows():
fmt_sql = sql.format_query(ins, *row)
sql.tquery(fmt_sql, cur=cur)
self.db.commit()
result = sql.read_frame("select * from test", con=self.db)
result.index = frame.index
tm.assert_frame_equal(result, frame)
def test_write_row_by_row(self):
_skip_if_no_MySQLdb()
frame = tm.makeTimeDataFrame()
frame.ix[0, 0] = np.nan
drop_sql = "DROP TABLE IF EXISTS test"
create_sql = sql.get_schema(frame, 'test', 'mysql')
cur = self.db.cursor()
cur.execute(drop_sql)
cur.execute(create_sql)
ins = "INSERT INTO test VALUES (%s, %s, %s, %s)"
for idx, row in frame.iterrows():
fmt_sql = format_query(ins, *row)
sql.tquery(fmt_sql, cur=cur)
self.db.commit()
result = sql.read_frame("select * from test", con=self.db)
result.index = frame.index
tm.assert_frame_equal(result, frame)
def mytable_exists(name, con, flavor):
flavor_map = {
'sqlite': ("SELECT name FROM sqlite_master "
"WHERE type='table' AND name='%s';") % name,
'mysql' : "SHOW TABLES LIKE '%s'" % name,
'postgresql': "SELECT table_name FROM information_schema.tables WHERE table_name = '%s'" % name
}
query = flavor_map.get(flavor, None)
if query is None:
raise NotImplementedError
#print "query:%s".format(query)
return len(pandas_io_sql.tquery(query, con)) > 0
def test_tquery(self):
frame = tm.makeTimeDataFrame()
sql.write_frame(frame, name="test_table", con=self.db)
result = sql.tquery("select A from test_table", self.db)
expected = frame.A
result = Series(result, frame.index)
tm.assert_series_equal(result, expected)
try:
sys.stdout = StringIO()
self.assertRaises(sqlite3.OperationalError, sql.tquery, "select * from blah", con=self.db)
self.assertRaises(sqlite3.OperationalError, sql.tquery, "select * from blah", con=self.db, retry=True)
finally:
sys.stdout = sys.__stdout__
def test_tquery(self):
frame = tm.makeTimeDataFrame()
sql.write_frame(frame, name='test_table', con=self.db)
result = sql.tquery("select A from test_table", self.db)
expected = frame.A
result = Series(result, frame.index)
tm.assert_series_equal(result, expected)
try:
sys.stdout = StringIO()
self.assertRaises(sqlite3.OperationalError, sql.tquery,
'select * from blah', con=self.db)
self.assertRaises(sqlite3.OperationalError, sql.tquery,
'select * from blah', con=self.db, retry=True)
finally:
sys.stdout = sys.__stdout__
def test_tquery(self):
try:
import MySQLdb
except ImportError:
raise nose.SkipTest("no MySQLdb")
frame = tm.makeTimeDataFrame()
drop_sql = "DROP TABLE IF EXISTS test_table"
cur = self.db.cursor()
cur.execute(drop_sql)
sql.write_frame(frame, name='test_table', con=self.db, flavor='mysql')
result = sql.tquery("select A from test_table", self.db)
expected = frame.A
result = Series(result, frame.index)
tm.assert_series_equal(result, expected)
try:
sys.stdout = StringIO()
self.assertRaises(MySQLdb.ProgrammingError, sql.tquery,
'select * from blah', con=self.db)
self.assertRaises(MySQLdb.ProgrammingError, sql.tquery,
'select * from blah', con=self.db, retry=True)
finally:
sys.stdout = sys.__stdout__
from psycopg2 import connect
import pandas as pd
import numpy as np
import pandas.io.sql as psql
import matplotlib.pyplot as plt
from collections import Counter
###################
# # connect to db ##
###################
con = connect(user="******", host="localhost", database="EV")
# select all columns from database as lists
frame = pd.DataFrame(
{'idnum': np.array(psql.tquery('select id from ev_charging;', con=con)),
'startday' : np.array(psql.tquery('select startday from ev_charging;', con=con)),
'starttime' : np.array(psql.tquery('select starttime from ev_charging;', con=con)),
'timezone' : np.array(psql.tquery('select timezone from ev_charging;', con=con)),
'duration' : np.array(psql.tquery('select duration from ev_charging;', con=con)),
'energy' : np.array(psql.tquery('select energy from ev_charging;', con=con)),
'ghgsavings' : np.array(psql.tquery('select ghgsavings from ev_charging;', con=con)),
'englevel' : np.array(psql.tquery('select englevel from ev_charging;', con=con)),
'address' : np.array(psql.tquery('select address from ev_charging;', con=con))
})
# print data
# clean address, then specify city, state, zip code
city = []
statezip = []
state = []
def test_tquery(self):
iris_results = sql.tquery("SELECT * FROM iris",
con=self.conn,
flavor='sqlite')
row = iris_results[0]
tm.equalContents(row, [5.1, 3.5, 1.4, 0.2, 'Iris-setosa'])
def test_tquery(self):
iris_results = sql.tquery(
"SELECT * FROM iris", con=self.conn, flavor='sqlite')
row = iris_results[0]
tm.equalContents(row, [5.1, 3.5, 1.4, 0.2, 'Iris-setosa'])
def test_if_exists(self):
_skip_if_no_MySQLdb()
df_if_exists_1 = DataFrame({'col1': [1, 2], 'col2': ['A', 'B']})
df_if_exists_2 = DataFrame({
'col1': [3, 4, 5],
'col2': ['C', 'D', 'E']
})
table_name = 'table_if_exists'
sql_select = "SELECT * FROM %s" % table_name
def clean_up(test_table_to_drop):
"""
Drops tables created from individual tests
so no dependencies arise from sequential tests
"""
if sql.table_exists(test_table_to_drop, self.db, flavor='mysql'):
cur = self.db.cursor()
cur.execute("DROP TABLE %s" % test_table_to_drop)
cur.close()
# test if invalid value for if_exists raises appropriate error
self.assertRaises(ValueError,
sql.write_frame,
frame=df_if_exists_1,
con=self.db,
name=table_name,
flavor='mysql',
if_exists='notvalidvalue')
clean_up(table_name)
# test if_exists='fail'
sql.write_frame(frame=df_if_exists_1,
con=self.db,
name=table_name,
flavor='mysql',
if_exists='fail')
self.assertRaises(ValueError,
sql.write_frame,
frame=df_if_exists_1,
con=self.db,
name=table_name,
flavor='mysql',
if_exists='fail')
# test if_exists='replace'
sql.write_frame(frame=df_if_exists_1,
con=self.db,
name=table_name,
flavor='mysql',
if_exists='replace')
self.assertEqual(sql.tquery(sql_select, con=self.db), [(1, 'A'),
(2, 'B')])
sql.write_frame(frame=df_if_exists_2,
con=self.db,
name=table_name,
flavor='mysql',
if_exists='replace')
self.assertEqual(sql.tquery(sql_select, con=self.db), [(3, 'C'),
(4, 'D'),
(5, 'E')])
clean_up(table_name)
# test if_exists='append'
sql.write_frame(frame=df_if_exists_1,
con=self.db,
name=table_name,
flavor='mysql',
if_exists='fail')
self.assertEqual(sql.tquery(sql_select, con=self.db), [(1, 'A'),
(2, 'B')])
sql.write_frame(frame=df_if_exists_2,
con=self.db,
name=table_name,
flavor='mysql',
if_exists='append')
self.assertEqual(sql.tquery(sql_select, con=self.db), [(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E')])
clean_up(table_name)
