Esempio n. 1
1
    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)
Esempio n. 2
0
    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)
Esempio n. 3
0
    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
Esempio n. 5
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__
Esempio n. 6
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__
Esempio n. 7
0
    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__
Esempio n. 8
0
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 = []
Esempio n. 9
0
 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'])
Esempio n. 10
0
 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'])
Esempio n. 11
0
    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)