Python pandas.read_sql_query() Examples

The following are 30 code examples for showing how to use pandas.read_sql_query(). These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example.

You may want to check out the right sidebar which shows the related API usage.

You may also want to check out all available functions/classes of the module pandas , or try the search function .

Example 1
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 6 votes vote down vote up
def test_datetime(self):
        df = DataFrame({'A': date_range('2013-01-01 09:00:00', periods=3),
                        'B': np.arange(3.0)})
        df.to_sql('test_datetime', self.conn)

        # with read_table -> type information from schema used
        result = sql.read_sql_table('test_datetime', self.conn)
        result = result.drop('index', axis=1)
        tm.assert_frame_equal(result, df)

        # with read_sql -> no type information -> sqlite has no native
        result = sql.read_sql_query('SELECT * FROM test_datetime', self.conn)
        result = result.drop('index', axis=1)
        if self.flavor == 'sqlite':
            assert isinstance(result.loc[0, 'A'], string_types)
            result['A'] = to_datetime(result['A'])
            tm.assert_frame_equal(result, df)
        else:
            tm.assert_frame_equal(result, df) 
Example 2
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 6 votes vote down vote up
def test_datetime_NaT(self):
        df = DataFrame({'A': date_range('2013-01-01 09:00:00', periods=3),
                        'B': np.arange(3.0)})
        df.loc[1, 'A'] = np.nan
        df.to_sql('test_datetime', self.conn, index=False)

        # with read_table -> type information from schema used
        result = sql.read_sql_table('test_datetime', self.conn)
        tm.assert_frame_equal(result, df)

        # with read_sql -> no type information -> sqlite has no native
        result = sql.read_sql_query('SELECT * FROM test_datetime', self.conn)
        if self.flavor == 'sqlite':
            assert isinstance(result.loc[0, 'A'], string_types)
            result['A'] = to_datetime(result['A'], errors='coerce')
            tm.assert_frame_equal(result, df)
        else:
            tm.assert_frame_equal(result, df) 
Example 3
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 6 votes vote down vote up
def test_datetime_time(self):
        # test support for datetime.time
        df = DataFrame([time(9, 0, 0), time(9, 1, 30)], columns=["a"])
        df.to_sql('test_time', self.conn, index=False)
        res = read_sql_table('test_time', self.conn)
        tm.assert_frame_equal(res, df)

        # GH8341
        # first, use the fallback to have the sqlite adapter put in place
        sqlite_conn = TestSQLiteFallback.connect()
        sql.to_sql(df, "test_time2", sqlite_conn, index=False)
        res = sql.read_sql_query("SELECT * FROM test_time2", sqlite_conn)
        ref = df.applymap(lambda _: _.strftime("%H:%M:%S.%f"))
        tm.assert_frame_equal(ref, res)  # check if adapter is in place
        # then test if sqlalchemy is unaffected by the sqlite adapter
        sql.to_sql(df, "test_time3", self.conn, index=False)
        if self.flavor == 'sqlite':
            res = sql.read_sql_query("SELECT * FROM test_time3", self.conn)
            ref = df.applymap(lambda _: _.strftime("%H:%M:%S.%f"))
            tm.assert_frame_equal(ref, res)
        res = sql.read_sql_table("test_time3", self.conn)
        tm.assert_frame_equal(df, res) 
Example 4
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 6 votes vote down vote up
def test_connectable_issue_example(self):
        # This tests the example raised in issue
        # https://github.com/pandas-dev/pandas/issues/10104

        def foo(connection):
            query = 'SELECT test_foo_data FROM test_foo_data'
            return sql.read_sql_query(query, con=connection)

        def bar(connection, data):
            data.to_sql(name='test_foo_data',
                        con=connection, if_exists='append')

        def main(connectable):
            with connectable.connect() as conn:
                with conn.begin():
                    foo_data = conn.run_callable(foo)
                    conn.run_callable(bar, foo_data)

        DataFrame({'test_foo_data': [0, 1, 2]}).to_sql(
            'test_foo_data', self.conn)
        main(self.conn) 
Example 5
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 6 votes vote down vote up
def test_temporary_table(self):
        test_data = u'Hello, World!'
        expected = DataFrame({'spam': [test_data]})
        Base = declarative.declarative_base()

        class Temporary(Base):
            __tablename__ = 'temp_test'
            __table_args__ = {'prefixes': ['TEMPORARY']}
            id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
            spam = sqlalchemy.Column(sqlalchemy.Unicode(30), nullable=False)

        Session = sa_session.sessionmaker(bind=self.conn)
        session = Session()
        with session.transaction:
            conn = session.connection()
            Temporary.__table__.create(conn)
            session.add(Temporary(spam=test_data))
            session.flush()
            df = sql.read_sql_query(
                sql=sqlalchemy.select([Temporary.spam]),
                con=conn,
            )

        tm.assert_frame_equal(df, expected) 
Example 6
Project: vnpy_crypto   Author: birforce   File: test_sql.py    License: MIT License 6 votes vote down vote up
def test_datetime(self):
        df = DataFrame({'A': date_range('2013-01-01 09:00:00', periods=3),
                        'B': np.arange(3.0)})
        df.to_sql('test_datetime', self.conn)

        # with read_table -> type information from schema used
        result = sql.read_sql_table('test_datetime', self.conn)
        result = result.drop('index', axis=1)
        tm.assert_frame_equal(result, df)

        # with read_sql -> no type information -> sqlite has no native
        result = sql.read_sql_query('SELECT * FROM test_datetime', self.conn)
        result = result.drop('index', axis=1)
        if self.flavor == 'sqlite':
            assert isinstance(result.loc[0, 'A'], string_types)
            result['A'] = to_datetime(result['A'])
            tm.assert_frame_equal(result, df)
        else:
            tm.assert_frame_equal(result, df) 
Example 7
Project: vnpy_crypto   Author: birforce   File: test_sql.py    License: MIT License 6 votes vote down vote up
def test_datetime_NaT(self):
        df = DataFrame({'A': date_range('2013-01-01 09:00:00', periods=3),
                        'B': np.arange(3.0)})
        df.loc[1, 'A'] = np.nan
        df.to_sql('test_datetime', self.conn, index=False)

        # with read_table -> type information from schema used
        result = sql.read_sql_table('test_datetime', self.conn)
        tm.assert_frame_equal(result, df)

        # with read_sql -> no type information -> sqlite has no native
        result = sql.read_sql_query('SELECT * FROM test_datetime', self.conn)
        if self.flavor == 'sqlite':
            assert isinstance(result.loc[0, 'A'], string_types)
            result['A'] = to_datetime(result['A'], errors='coerce')
            tm.assert_frame_equal(result, df)
        else:
            tm.assert_frame_equal(result, df) 
Example 8
Project: vnpy_crypto   Author: birforce   File: test_sql.py    License: MIT License 6 votes vote down vote up
def test_datetime_time(self):
        # test support for datetime.time
        df = DataFrame([time(9, 0, 0), time(9, 1, 30)], columns=["a"])
        df.to_sql('test_time', self.conn, index=False)
        res = read_sql_table('test_time', self.conn)
        tm.assert_frame_equal(res, df)

        # GH8341
        # first, use the fallback to have the sqlite adapter put in place
        sqlite_conn = TestSQLiteFallback.connect()
        sql.to_sql(df, "test_time2", sqlite_conn, index=False)
        res = sql.read_sql_query("SELECT * FROM test_time2", sqlite_conn)
        ref = df.applymap(lambda _: _.strftime("%H:%M:%S.%f"))
        tm.assert_frame_equal(ref, res)  # check if adapter is in place
        # then test if sqlalchemy is unaffected by the sqlite adapter
        sql.to_sql(df, "test_time3", self.conn, index=False)
        if self.flavor == 'sqlite':
            res = sql.read_sql_query("SELECT * FROM test_time3", self.conn)
            ref = df.applymap(lambda _: _.strftime("%H:%M:%S.%f"))
            tm.assert_frame_equal(ref, res)
        res = sql.read_sql_table("test_time3", self.conn)
        tm.assert_frame_equal(df, res) 
Example 9
Project: vnpy_crypto   Author: birforce   File: test_sql.py    License: MIT License 6 votes vote down vote up
def test_connectable_issue_example(self):
        # This tests the example raised in issue
        # https://github.com/pandas-dev/pandas/issues/10104

        def foo(connection):
            query = 'SELECT test_foo_data FROM test_foo_data'
            return sql.read_sql_query(query, con=connection)

        def bar(connection, data):
            data.to_sql(name='test_foo_data',
                        con=connection, if_exists='append')

        def main(connectable):
            with connectable.connect() as conn:
                with conn.begin():
                    foo_data = conn.run_callable(foo)
                    conn.run_callable(bar, foo_data)

        DataFrame({'test_foo_data': [0, 1, 2]}).to_sql(
            'test_foo_data', self.conn)
        main(self.conn) 
Example 10
Project: vnpy_crypto   Author: birforce   File: test_sql.py    License: MIT License 6 votes vote down vote up
def test_temporary_table(self):
        test_data = u'Hello, World!'
        expected = DataFrame({'spam': [test_data]})
        Base = declarative.declarative_base()

        class Temporary(Base):
            __tablename__ = 'temp_test'
            __table_args__ = {'prefixes': ['TEMPORARY']}
            id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
            spam = sqlalchemy.Column(sqlalchemy.Unicode(30), nullable=False)

        Session = sa_session.sessionmaker(bind=self.conn)
        session = Session()
        with session.transaction:
            conn = session.connection()
            Temporary.__table__.create(conn)
            session.add(Temporary(spam=test_data))
            session.flush()
            df = sql.read_sql_query(
                sql=sqlalchemy.select([Temporary.spam]),
                con=conn,
            )

        tm.assert_frame_equal(df, expected) 
Example 11
Project: grizli   Author: gbrammer   File: db.py    License: MIT License 6 votes vote down vote up
def add_to_charge():

    engine = grizli_db.get_db_engine()

    p = pd.read_sql_query('select distinct p_root from photometry_apcorr', engine)
    f = pd.read_sql_query('select distinct field_root from charge_fields', engine)

    new_fields = []
    for root in p['p_root'].values:
        if root not in f['field_root'].values:
            print(root)
            new_fields.append(root)

    df = pd.DataFrame()
    df['field_root'] = new_fields
    df['comment'] = 'CANDELS'
    ix = df['field_root'] == 'j214224m4420'
    df['comment'][ix] = 'Rafelski UltraDeep'

    df.to_sql('charge_fields', engine, index=False, if_exists='append', method='multi') 
Example 12
Project: grizli   Author: gbrammer   File: db.py    License: MIT License 6 votes vote down vote up
def count_sources_for_bad_persistence():
    """
    Count the number of extracted objects for each id and look for fields
    with few objects, which are usually problems with the persistence mask
    """

    import pandas as pd
    from grizli.aws import db as grizli_db
    from grizli import utils
    engine = grizli_db.get_db_engine(echo=False)

    # Number of matches per field
    counts = pd.read_sql_query("select root, COUNT(root) as n from redshift_fit, photometry_apcorr where phot_root = p_root AND id = p_id AND bic_diff > 5 AND mag_auto < 24 group by root;", engine)

    counts = utils.GTable.from_pandas(counts)
    so = np.argsort(counts['n'])

    sh = """
    BUCKET=grizli-v
    root=j113812m1134

    aws s3 rm --recursive s3://grizli-v1/Pipeline/${root}/ --include "*"
    grism_run_single.sh ${root} --run_fine_alignment=True --extra_filters=g800l --bucket=grizli-v1 --preprocess_args.skip_single_optical_visits=True --mask_spikes=True --persistence_args.err_threshold=1
    """ 
Example 13
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_read_sql_iris(self):
        iris_frame = sql.read_sql_query(
            "SELECT * FROM iris", self.conn)
        self._check_iris_loaded_frame(iris_frame) 
Example 14
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_read_sql_view(self):
        iris_frame = sql.read_sql_query(
            "SELECT * FROM iris_view", self.conn)
        self._check_iris_loaded_frame(iris_frame) 
Example 15
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_to_sql_series(self):
        s = Series(np.arange(5, dtype='int64'), name='series')
        sql.to_sql(s, "test_series", self.conn, index=False)
        s2 = sql.read_sql_query("SELECT * FROM test_series", self.conn)
        tm.assert_frame_equal(s.to_frame(), s2) 
Example 16
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_roundtrip(self):
        sql.to_sql(self.test_frame1, 'test_frame_roundtrip',
                   con=self.conn)
        result = sql.read_sql_query(
            'SELECT * FROM test_frame_roundtrip',
            con=self.conn)

        # HACK!
        result.index = self.test_frame1.index
        result.set_index('level_0', inplace=True)
        result.index.astype(int)
        result.index.name = None
        tm.assert_frame_equal(result, self.test_frame1) 
Example 17
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_roundtrip_chunksize(self):
        sql.to_sql(self.test_frame1, 'test_frame_roundtrip', con=self.conn,
                   index=False, chunksize=2)
        result = sql.read_sql_query(
            'SELECT * FROM test_frame_roundtrip',
            con=self.conn)
        tm.assert_frame_equal(result, self.test_frame1) 
Example 18
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_date_and_index(self):
        # Test case where same column appears in parse_date and index_col

        df = sql.read_sql_query("SELECT * FROM types_test_data", self.conn,
                                index_col='DateCol',
                                parse_dates=['DateCol', 'IntDateCol'])

        assert issubclass(df.index.dtype.type, np.datetime64)
        assert issubclass(df.IntDateCol.dtype.type, np.datetime64) 
Example 19
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_timedelta(self):

        # see #6921
        df = to_timedelta(
            Series(['00:00:01', '00:00:03'], name='foo')).to_frame()
        with tm.assert_produces_warning(UserWarning):
            df.to_sql('test_timedelta', self.conn)
        result = sql.read_sql_query('SELECT * FROM test_timedelta', self.conn)
        tm.assert_series_equal(result['foo'], df['foo'].astype('int64')) 
Example 20
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_to_sql_index_label(self):
        temp_frame = DataFrame({'col1': range(4)})

        # no index name, defaults to 'index'
        sql.to_sql(temp_frame, 'test_index_label', self.conn)
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == 'index'

        # specifying index_label
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace', index_label='other_label')
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == "other_label"

        # using the index name
        temp_frame.index.name = 'index_name'
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace')
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == "index_name"

        # has index name, but specifying index_label
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace', index_label='other_label')
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == "other_label"

        # index name is integer
        temp_frame.index.name = 0
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace')
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == "0"

        temp_frame.index.name = None
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace', index_label=0)
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == "0" 
Example 21
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_to_sql_index_label_multiindex(self):
        temp_frame = DataFrame({'col1': range(4)},
                               index=MultiIndex.from_product(
                                   [('A0', 'A1'), ('B0', 'B1')]))

        # no index name, defaults to 'level_0' and 'level_1'
        sql.to_sql(temp_frame, 'test_index_label', self.conn)
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == 'level_0'
        assert frame.columns[1] == 'level_1'

        # specifying index_label
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace', index_label=['A', 'B'])
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[:2].tolist() == ['A', 'B']

        # using the index name
        temp_frame.index.names = ['A', 'B']
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace')
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[:2].tolist() == ['A', 'B']

        # has index name, but specifying index_label
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace', index_label=['C', 'D'])
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[:2].tolist() == ['C', 'D']

        # wrong length of index_label
        pytest.raises(ValueError, sql.to_sql, temp_frame,
                      'test_index_label', self.conn, if_exists='replace',
                      index_label='C') 
Example 22
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_multiindex_roundtrip(self):
        df = DataFrame.from_records([(1, 2.1, 'line1'), (2, 1.5, 'line2')],
                                    columns=['A', 'B', 'C'], index=['A', 'B'])

        df.to_sql('test_multiindex_roundtrip', self.conn)
        result = sql.read_sql_query('SELECT * FROM test_multiindex_roundtrip',
                                    self.conn, index_col=['A', 'B'])
        tm.assert_frame_equal(df, result, check_index_type=True) 
Example 23
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_categorical(self):
        # GH8624
        # test that categorical gets written correctly as dense column
        df = DataFrame(
            {'person_id': [1, 2, 3],
             'person_name': ['John P. Doe', 'Jane Dove', 'John P. Doe']})
        df2 = df.copy()
        df2['person_name'] = df2['person_name'].astype('category')

        df2.to_sql('test_categorical', self.conn, index=False)
        res = sql.read_sql_query('SELECT * FROM test_categorical', self.conn)

        tm.assert_frame_equal(res, df) 
Example 24
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_escaped_table_name(self):
        # GH 13206
        df = DataFrame({'A': [0, 1, 2], 'B': [0.2, np.nan, 5.6]})
        df.to_sql('d1187b08-4943-4c8d-a7f6', self.conn, index=False)

        res = sql.read_sql_query('SELECT * FROM `d1187b08-4943-4c8d-a7f6`',
                                 self.conn)

        tm.assert_frame_equal(res, df) 
Example 25
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_read_sql_delegate(self):
        iris_frame1 = sql.read_sql_query(
            "SELECT * FROM iris", self.conn)
        iris_frame2 = sql.read_sql(
            "SELECT * FROM iris", self.conn)
        tm.assert_frame_equal(iris_frame1, iris_frame2)

        iris_frame1 = sql.read_sql_table('iris', self.conn)
        iris_frame2 = sql.read_sql('iris', self.conn)
        tm.assert_frame_equal(iris_frame1, iris_frame2) 
Example 26
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_not_reflect_all_tables(self):
        # create invalid table
        qry = """CREATE TABLE invalid (x INTEGER, y UNKNOWN);"""
        self.conn.execute(qry)
        qry = """CREATE TABLE other_table (x INTEGER, y INTEGER);"""
        self.conn.execute(qry)

        with warnings.catch_warnings(record=True) as w:
            # Cause all warnings to always be triggered.
            warnings.simplefilter("always")
            # Trigger a warning.
            sql.read_sql_table('other_table', self.conn)
            sql.read_sql_query('SELECT * FROM other_table', self.conn)
            # Verify some things
            assert len(w) == 0 
Example 27
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_database_uri_string(self):

        # Test read_sql and .to_sql method with a database URI (GH10654)
        test_frame1 = self.test_frame1
        # db_uri = 'sqlite:///:memory:' # raises
        # sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near
        # "iris": syntax error [SQL: 'iris']
        with tm.ensure_clean() as name:
            db_uri = 'sqlite:///' + name
            table = 'iris'
            test_frame1.to_sql(table, db_uri, if_exists='replace', index=False)
            test_frame2 = sql.read_sql(table, db_uri)
            test_frame3 = sql.read_sql_table(table, db_uri)
            query = 'SELECT * FROM iris'
            test_frame4 = sql.read_sql_query(query, db_uri)
        tm.assert_frame_equal(test_frame1, test_frame2)
        tm.assert_frame_equal(test_frame1, test_frame3)
        tm.assert_frame_equal(test_frame1, test_frame4)

        # using driver that will not be installed on Travis to trigger error
        # in sqlalchemy.create_engine -> test passing of this error to user
        try:
            # the rest of this test depends on pg8000's being absent
            import pg8000  # noqa
            pytest.skip("pg8000 is installed")
        except ImportError:
            pass

        db_uri = "postgresql+pg8000://user:pass@host/dbname"
        with pytest.raises(ImportError, match="pg8000"):
            sql.read_sql("select * from table", db_uri) 
Example 28
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_read_sql_delegate(self):
        iris_frame1 = sql.read_sql_query("SELECT * FROM iris", self.conn)
        iris_frame2 = sql.read_sql("SELECT * FROM iris", self.conn)
        tm.assert_frame_equal(iris_frame1, iris_frame2)

        pytest.raises(sql.DatabaseError, sql.read_sql, 'iris', self.conn) 
Example 29
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_datetime_with_timezone_roundtrip(self):
        # GH 9086
        # Write datetimetz data to a db and read it back
        # For dbs that support timestamps with timezones, should get back UTC
        # otherwise naive data should be returned
        expected = DataFrame({'A': date_range(
            '2013-01-01 09:00:00', periods=3, tz='US/Pacific'
        )})
        expected.to_sql('test_datetime_tz', self.conn, index=False)

        if self.flavor == 'postgresql':
            # SQLAlchemy "timezones" (i.e. offsets) are coerced to UTC
            expected['A'] = expected['A'].dt.tz_convert('UTC')
        else:
            # Otherwise, timestamps are returned as local, naive
            expected['A'] = expected['A'].dt.tz_localize(None)

        result = sql.read_sql_table('test_datetime_tz', self.conn)
        tm.assert_frame_equal(result, expected)

        result = sql.read_sql_query(
            'SELECT * FROM test_datetime_tz', self.conn
        )
        if self.flavor == 'sqlite':
            # read_sql_query does not return datetime type like read_sql_table
            assert isinstance(result.loc[0, 'A'], string_types)
            result['A'] = to_datetime(result['A'])
        tm.assert_frame_equal(result, expected) 
Example 30
Project: recruit   Author: Frank-qlu   File: test_sql.py    License: Apache License 2.0 5 votes vote down vote up
def test_nan_fullcolumn(self):
        # full NaN column (numeric float column)
        df = DataFrame({'A': [0, 1, 2], 'B': [np.nan, np.nan, np.nan]})
        df.to_sql('test_nan', self.conn, index=False)

        # with read_table
        result = sql.read_sql_table('test_nan', self.conn)
        tm.assert_frame_equal(result, df)

        # with read_sql -> not type info from table -> stays None
        df['B'] = df['B'].astype('object')
        df['B'] = None
        result = sql.read_sql_query('SELECT * FROM test_nan', self.conn)
        tm.assert_frame_equal(result, df)