Python pandas.io.sql.SQLDatabase() Examples

The following are 27 code examples of pandas.io.sql.SQLDatabase(). 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 also want to check out all available functions/classes of the module pandas.io.sql , or try the search function .
Example #1
Source File: test_sql.py    From twitter-stock-recommendation with MIT License 6 votes vote down vote up 
def setup_method(self, datapath):
        super(_EngineToConnMixin, self).setup_method(datapath)
        engine = self.conn
        conn = engine.connect()
        self.__tx = conn.begin()
        self.pandasSQL = sql.SQLDatabase(conn)
        self.__engine = engine
        self.conn = conn

        yield

        self.__tx.rollback()
        self.conn.close()
        self.conn = self.__engine
        self.pandasSQL = sql.SQLDatabase(self.__engine)
        # XXX:
        # super(_EngineToConnMixin, self).teardown_method(method)
Example #2
Source File: test_sql.py    From vnpy_crypto with MIT License 6 votes vote down vote up 
def setup_method(self, datapath):
        super(_EngineToConnMixin, self).setup_method(datapath)
        engine = self.conn
        conn = engine.connect()
        self.__tx = conn.begin()
        self.pandasSQL = sql.SQLDatabase(conn)
        self.__engine = engine
        self.conn = conn

        yield

        self.__tx.rollback()
        self.conn.close()
        self.conn = self.__engine
        self.pandasSQL = sql.SQLDatabase(self.__engine)
        # XXX:
        # super(_EngineToConnMixin, self).teardown_method(method)
Example #3
Source File: test_sql.py    From predictive-maintenance-using-machine-learning with Apache License 2.0 6 votes vote down vote up 
def setup_method(self, load_iris_data):
        super(_EngineToConnMixin, self).load_test_data_and_sql()
        engine = self.conn
        conn = engine.connect()
        self.__tx = conn.begin()
        self.pandasSQL = sql.SQLDatabase(conn)
        self.__engine = engine
        self.conn = conn

        yield

        self.__tx.rollback()
        self.conn.close()
        self.conn = self.__engine
        self.pandasSQL = sql.SQLDatabase(self.__engine)
        # XXX:
        # super(_EngineToConnMixin, self).teardown_method(method)
Example #4
Source File: test_sql.py    From twitter-stock-recommendation with MIT License 5 votes vote down vote up 
def test_create_table(self):
        temp_conn = self.connect()
        temp_frame = DataFrame(
            {'one': [1., 2., 3., 4.], 'two': [4., 3., 2., 1.]})

        pandasSQL = sql.SQLDatabase(temp_conn)
        pandasSQL.to_sql(temp_frame, 'temp_frame')

        assert temp_conn.has_table('temp_frame')
Example #5
Source File: test_sql.py    From recruit with Apache License 2.0 5 votes vote down vote up 
def drop_table(self, table_name):
        sql.SQLDatabase(self.conn).drop_table(table_name)
Example #6
Source File: test_sql.py    From twitter-stock-recommendation with MIT License 5 votes vote down vote up 
def setup_connect(self):
        try:
            self.conn = self.connect()
            self.pandasSQL = sql.SQLDatabase(self.conn)
            # to test if connection can be made:
            self.conn.connect()
        except sqlalchemy.exc.OperationalError:
            pytest.skip(
                "Can't connect to {0} server".format(self.flavor))
Example #7
Source File: test_sql.py    From twitter-stock-recommendation with MIT License 5 votes vote down vote up 
def test_sqlalchemy_type_mapping(self):

        # Test Timestamp objects (no datetime64 because of timezone) (GH9085)
        df = DataFrame({'time': to_datetime(['201412120154', '201412110254'],
                                            utc=True)})
        db = sql.SQLDatabase(self.conn)
        table = sql.SQLTable("test_type", db, frame=df)
        assert isinstance(table.table.c['time'].type, sqltypes.DateTime)
Example #8
Source File: test_sql.py    From twitter-stock-recommendation with MIT License 5 votes vote down vote up 
def drop_table(self, table_name):
        sql.SQLDatabase(self.conn).drop_table(table_name)
Example #9
Source File: test_sql.py    From elasticintel with GNU General Public License v3.0 5 votes vote down vote up 
def test_drop_table(self):
        temp_conn = self.connect()

        temp_frame = DataFrame(
            {'one': [1., 2., 3., 4.], 'two': [4., 3., 2., 1.]})

        pandasSQL = sql.SQLDatabase(temp_conn)
        pandasSQL.to_sql(temp_frame, 'temp_frame')

        assert temp_conn.has_table('temp_frame')

        pandasSQL.drop_table('temp_frame')

        assert not temp_conn.has_table('temp_frame')
Example #10
Source File: test_sql.py    From elasticintel with GNU General Public License v3.0 5 votes vote down vote up 
def setup_connect(self):
        try:
            self.conn = self.connect()
            self.pandasSQL = sql.SQLDatabase(self.conn)
            # to test if connection can be made:
            self.conn.connect()
        except sqlalchemy.exc.OperationalError:
            pytest.skip(
                "Can't connect to {0} server".format(self.flavor))
Example #11
Source File: test_sql.py    From elasticintel with GNU General Public License v3.0 5 votes vote down vote up 
def teardown_method(self, method):
        self.__tx.rollback()
        self.conn.close()
        self.conn = self.__engine
        self.pandasSQL = sql.SQLDatabase(self.__engine)
        super(_EngineToConnMixin, self).teardown_method(method)
Example #12
Source File: test_sql.py    From elasticintel with GNU General Public License v3.0 5 votes vote down vote up 
def setup_method(self, method):
        super(_EngineToConnMixin, self).setup_method(method)
        engine = self.conn
        conn = engine.connect()
        self.__tx = conn.begin()
        self.pandasSQL = sql.SQLDatabase(conn)
        self.__engine = engine
        self.conn = conn
Example #13
Source File: test_sql.py    From elasticintel with GNU General Public License v3.0 5 votes vote down vote up 
def test_sqlalchemy_type_mapping(self):

        # Test Timestamp objects (no datetime64 because of timezone) (GH9085)
        df = DataFrame({'time': to_datetime(['201412120154', '201412110254'],
                                            utc=True)})
        db = sql.SQLDatabase(self.conn)
        table = sql.SQLTable("test_type", db, frame=df)
        assert isinstance(table.table.c['time'].type, sqltypes.DateTime)
Example #14
Source File: test_sql.py    From elasticintel with GNU General Public License v3.0 5 votes vote down vote up 
def drop_table(self, table_name):
        sql.SQLDatabase(self.conn).drop_table(table_name)
Example #15
Source File: test_sql.py    From predictive-maintenance-using-machine-learning with Apache License 2.0 5 votes vote down vote up 
def test_create_table(self):
        temp_conn = self.connect()
        temp_frame = DataFrame(
            {'one': [1., 2., 3., 4.], 'two': [4., 3., 2., 1.]})

        pandasSQL = sql.SQLDatabase(temp_conn)
        pandasSQL.to_sql(temp_frame, 'temp_frame')

        assert temp_conn.has_table('temp_frame')
Example #16
Source File: test_sql.py    From predictive-maintenance-using-machine-learning with Apache License 2.0 5 votes vote down vote up 
def setup_connect(self):
        try:
            self.conn = self.connect()
            self.pandasSQL = sql.SQLDatabase(self.conn)
            # to test if connection can be made:
            self.conn.connect()
        except sqlalchemy.exc.OperationalError:
            pytest.skip(
                "Can't connect to {0} server".format(self.flavor))
Example #17
Source File: test_sql.py    From predictive-maintenance-using-machine-learning with Apache License 2.0 5 votes vote down vote up 
def test_sqlalchemy_type_mapping(self):

        # Test Timestamp objects (no datetime64 because of timezone) (GH9085)
        df = DataFrame({'time': to_datetime(['201412120154', '201412110254'],
                                            utc=True)})
        db = sql.SQLDatabase(self.conn)
        table = sql.SQLTable("test_type", db, frame=df)
        # GH 9086: TIMESTAMP is the suggested type for datetimes with timezones
        assert isinstance(table.table.c['time'].type, sqltypes.TIMESTAMP)
Example #18
Source File: test_sql.py    From predictive-maintenance-using-machine-learning with Apache License 2.0 5 votes vote down vote up 
def drop_table(self, table_name):
        sql.SQLDatabase(self.conn).drop_table(table_name)
Example #19
Source File: test_sql.py    From vnpy_crypto with MIT License 5 votes vote down vote up 
def test_create_table(self):
        temp_conn = self.connect()
        temp_frame = DataFrame(
            {'one': [1., 2., 3., 4.], 'two': [4., 3., 2., 1.]})

        pandasSQL = sql.SQLDatabase(temp_conn)
        pandasSQL.to_sql(temp_frame, 'temp_frame')

        assert temp_conn.has_table('temp_frame')
Example #20
Source File: test_sql.py    From vnpy_crypto with MIT License 5 votes vote down vote up 
def setup_connect(self):
        try:
            self.conn = self.connect()
            self.pandasSQL = sql.SQLDatabase(self.conn)
            # to test if connection can be made:
            self.conn.connect()
        except sqlalchemy.exc.OperationalError:
            pytest.skip(
                "Can't connect to {0} server".format(self.flavor))
Example #21
Source File: test_sql.py    From vnpy_crypto with MIT License 5 votes vote down vote up 
def test_sqlalchemy_type_mapping(self):

        # Test Timestamp objects (no datetime64 because of timezone) (GH9085)
        df = DataFrame({'time': to_datetime(['201412120154', '201412110254'],
                                            utc=True)})
        db = sql.SQLDatabase(self.conn)
        table = sql.SQLTable("test_type", db, frame=df)
        assert isinstance(table.table.c['time'].type, sqltypes.DateTime)
Example #22
Source File: test_sql.py    From vnpy_crypto with MIT License 5 votes vote down vote up 
def drop_table(self, table_name):
        sql.SQLDatabase(self.conn).drop_table(table_name)
Example #23
Source File: _pandas.py    From omniduct with MIT License 5 votes vote down vote up 
def to_sql(df, name, schema, con, index, if_exists, mode='default', **kwargs):
    """
    Override the default `pandas.to_sql` method to allow for insertion of
    multiple rows of data at once. This is derived from the upstream patch at
    https://github.com/pandas-dev/pandas/pull/21401, and can be deprecated
    once it is merged and released in a new version of `pandas`.
    """
    assert mode in ('default', 'multi'), 'unexpected `to_sql` mode {}'.format(mode)
    if mode == 'default':
        return df.to_sql(
            name=name, schema=schema, con=con, index=index, if_exists=if_exists, **kwargs
        )
    else:
        nrows = len(df)
        if nrows == 0:
            return

        chunksize = kwargs.get('chunksize', nrows)
        if chunksize == 0:
            raise ValueError('chunksize argument should be non-zero')
        chunks = int(nrows / chunksize) + 1

        pd_sql = SQLDatabase(con)
        pd_table = SQLTable(
            name, pd_sql, frame=df, index=index, if_exists=if_exists,
            index_label=kwargs.get('insert_label'), schema=schema, dtype=kwargs.get('dtype')
        )
        pd_table.create()
        keys, data_list = pd_table.insert_data()

        with pd_sql.run_transaction() as conn:
            for i in range(chunks):
                start_i = i * chunksize
                end_i = min((i + 1) * chunksize, nrows)
                if start_i >= end_i:
                    break

                chunk_iter = zip(*[arr[start_i:end_i] for arr in data_list])
                data = [{k: v for k, v in zip(keys, row)} for row in chunk_iter]
                conn.execute(pd_table.table.insert(data))  # multivalues insert
Example #24
Source File: test_sql.py    From recruit with Apache License 2.0 5 votes vote down vote up 
def test_create_table(self):
        temp_conn = self.connect()
        temp_frame = DataFrame(
            {'one': [1., 2., 3., 4.], 'two': [4., 3., 2., 1.]})

        pandasSQL = sql.SQLDatabase(temp_conn)
        pandasSQL.to_sql(temp_frame, 'temp_frame')

        assert temp_conn.has_table('temp_frame')
Example #25
Source File: test_sql.py    From recruit with Apache License 2.0 5 votes vote down vote up 
def setup_connect(self):
        try:
            self.conn = self.connect()
            self.pandasSQL = sql.SQLDatabase(self.conn)
            # to test if connection can be made:
            self.conn.connect()
        except sqlalchemy.exc.OperationalError:
            pytest.skip(
                "Can't connect to {0} server".format(self.flavor))
Example #26
Source File: test_sql.py    From recruit with Apache License 2.0 5 votes vote down vote up 
def setup_method(self, load_iris_data):
        super(_EngineToConnMixin, self).load_test_data_and_sql()
        engine = self.conn
        conn = engine.connect()
        self.__tx = conn.begin()
        self.pandasSQL = sql.SQLDatabase(conn)
        self.__engine = engine
        self.conn = conn

        yield

        self.__tx.rollback()
        self.conn.close()
        self.conn = self.__engine
        self.pandasSQL = sql.SQLDatabase(self.__engine)
        # XXX:
        # super(_EngineToConnMixin, self).teardown_method(method)
Example #27
Source File: test_sql.py    From recruit with Apache License 2.0 5 votes vote down vote up 
def test_sqlalchemy_type_mapping(self):

        # Test Timestamp objects (no datetime64 because of timezone) (GH9085)
        df = DataFrame({'time': to_datetime(['201412120154', '201412110254'],
                                            utc=True)})
        db = sql.SQLDatabase(self.conn)
        table = sql.SQLTable("test_type", db, frame=df)
        # GH 9086: TIMESTAMP is the suggested type for datetimes with timezones
        assert isinstance(table.table.c['time'].type, sqltypes.TIMESTAMP)