Python sql.get_schema方法代码示例

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import get_schema [as 别名]
def test_get_schema_create_table(self):
        # Use a dataframe without a bool column, since MySQL converts bool to
        # TINYINT (which read_sql_table returns as an int and causes a dtype
        # mismatch)

        self._load_test3_data()
        tbl = 'test_get_schema_create_table'
        create_sql = sql.get_schema(self.test_frame3, tbl, con=self.conn)
        blank_test_df = self.test_frame3.iloc[:0]

        self.drop_table(tbl)
        self.conn.execute(create_sql)
        returned_df = sql.read_sql_table(tbl, self.conn)
        tm.assert_frame_equal(returned_df, blank_test_df,
                              check_index_type=False)
        self.drop_table(tbl) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import get_schema [as 别名]
def test_write_row_by_row(self):
        frame = tm.makeTimeDataFrame()
        frame.iloc[0, 0] = np.nan
        drop_sql = "DROP TABLE IF EXISTS test"
        create_sql = sql.get_schema(frame, 'test')
        cur = self.conn.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)
            tquery(fmt_sql, cur=cur)

        self.conn.commit()

        result = sql.read_sql("select * from test", con=self.conn)
        result.index = frame.index
        tm.assert_frame_equal(result, frame, check_less_precise=True) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import get_schema [as 别名]
def test_execute(self):
        frame = tm.makeTimeDataFrame()
        drop_sql = "DROP TABLE IF EXISTS test"
        create_sql = sql.get_schema(frame, 'test')
        cur = self.conn.cursor()
        with warnings.catch_warnings():
            warnings.filterwarnings("ignore", "Unknown table.*")
            cur.execute(drop_sql)
        cur.execute(create_sql)
        ins = "INSERT INTO test VALUES (%s, %s, %s, %s)"

        row = frame.iloc[0].values.tolist()
        sql.execute(ins, self.conn, params=tuple(row))
        self.conn.commit()

        result = sql.read_sql("select * from test", self.conn)
        result.index = frame.index[:1]
        tm.assert_frame_equal(result, frame[:1]) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import get_schema [as 别名]
def test_schema(self):
        frame = tm.makeTimeDataFrame()
        create_sql = sql.get_schema(frame, 'test')
        lines = create_sql.splitlines()
        for l in lines:
            tokens = l.split(' ')
            if len(tokens) == 2 and tokens[0] == 'A':
                assert tokens[1] == 'DATETIME'

        frame = tm.makeTimeDataFrame()
        drop_sql = "DROP TABLE IF EXISTS test"
        create_sql = sql.get_schema(frame, 'test', keys=['A', 'B'])
        lines = create_sql.splitlines()
        assert 'PRIMARY KEY (`A`, `B`)' in create_sql
        cur = self.conn.cursor()
        cur.execute(drop_sql)
        cur.execute(create_sql) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import get_schema [as 别名]
def test_write_row_by_row(self):

        _skip_if_no_pymysql()
        frame = tm.makeTimeDataFrame()
        frame.iloc[0, 0] = np.nan
        drop_sql = "DROP TABLE IF EXISTS test"
        create_sql = sql.get_schema(frame, 'test')
        cur = self.conn.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)
            tquery(fmt_sql, cur=cur)

        self.conn.commit()

        result = sql.read_sql("select * from test", con=self.conn)
        result.index = frame.index
        tm.assert_frame_equal(result, frame, check_less_precise=True) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import get_schema [as 别名]
def test_execute(self):
        _skip_if_no_pymysql()
        frame = tm.makeTimeDataFrame()
        drop_sql = "DROP TABLE IF EXISTS test"
        create_sql = sql.get_schema(frame, 'test')
        cur = self.conn.cursor()
        with warnings.catch_warnings():
            warnings.filterwarnings("ignore", "Unknown table.*")
            cur.execute(drop_sql)
        cur.execute(create_sql)
        ins = "INSERT INTO test VALUES (%s, %s, %s, %s)"

        row = frame.iloc[0].values.tolist()
        sql.execute(ins, self.conn, params=tuple(row))
        self.conn.commit()

        result = sql.read_sql("select * from test", self.conn)
        result.index = frame.index[:1]
        tm.assert_frame_equal(result, frame[:1]) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import get_schema [as 别名]
def test_schema(self):
        _skip_if_no_pymysql()
        frame = tm.makeTimeDataFrame()
        create_sql = sql.get_schema(frame, 'test')
        lines = create_sql.splitlines()
        for l in lines:
            tokens = l.split(' ')
            if len(tokens) == 2 and tokens[0] == 'A':
                assert tokens[1] == 'DATETIME'

        frame = tm.makeTimeDataFrame()
        drop_sql = "DROP TABLE IF EXISTS test"
        create_sql = sql.get_schema(frame, 'test', keys=['A', 'B'])
        lines = create_sql.splitlines()
        assert 'PRIMARY KEY (`A`, `B`)' in create_sql
        cur = self.conn.cursor()
        cur.execute(drop_sql)
        cur.execute(create_sql) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import get_schema [as 别名]
def test_write_row_by_row(self):
        frame = tm.makeTimeDataFrame()
        frame.ix[0, 0] = np.nan
        create_sql = sql.get_schema(frame, 'test', 'sqlite')
        cur = self.db.cursor()
        cur.execute(create_sql)

        cur = self.db.cursor()

        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) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import get_schema [as 别名]
def test_execute(self):
        _skip_if_no_MySQLdb()
        frame = tm.makeTimeDataFrame()
        drop_sql = "DROP TABLE IF EXISTS test"
        create_sql = sql.get_schema(frame, 'test', 'mysql')
        cur = self.db.cursor()
        with warnings.catch_warnings():
            warnings.filterwarnings("ignore", "Unknown table.*")
            cur.execute(drop_sql)
        cur.execute(create_sql)
        ins = "INSERT INTO test VALUES (%s, %s, %s, %s)"

        row = frame.ix[0]
        sql.execute(ins, self.db, params=tuple(row))
        self.db.commit()

        result = sql.read_frame("select * from test", self.db)
        result.index = frame.index[:1]
        tm.assert_frame_equal(result, frame[:1]) 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import get_schema [as 别名]
def test_get_schema(self):
        create_sql = sql.get_schema(self.test_frame1, 'test', con=self.conn)
        assert 'CREATE' in create_sql 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import get_schema [as 别名]
def test_get_schema_dtypes(self):
        float_frame = DataFrame({'a': [1.1, 1.2], 'b': [2.1, 2.2]})
        dtype = sqlalchemy.Integer if self.mode == 'sqlalchemy' else 'INTEGER'
        create_sql = sql.get_schema(float_frame, 'test',
                                    con=self.conn, dtype={'b': dtype})
        assert 'CREATE' in create_sql
        assert 'INTEGER' in create_sql 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import get_schema [as 别名]
def test_get_schema_keys(self):
        frame = DataFrame({'Col1': [1.1, 1.2], 'Col2': [2.1, 2.2]})
        create_sql = sql.get_schema(frame, 'test', con=self.conn, keys='Col1')
        constraint_sentence = 'CONSTRAINT test_pk PRIMARY KEY ("Col1")'
        assert constraint_sentence in create_sql

        # multiple columns as key (GH10385)
        create_sql = sql.get_schema(self.test_frame1, 'test',
                                    con=self.conn, keys=['A', 'B'])
        constraint_sentence = 'CONSTRAINT test_pk PRIMARY KEY ("A", "B")'
        assert constraint_sentence in create_sql 

# 需要导入模块: from pandas.io import sql [as 别名]
# 或者: from pandas.io.sql import get_schema [as 别名]
def test_get_schema2(self):
        # without providing a connection object (available for backwards comp)
        create_sql = sql.get_schema(self.test_frame1, 'test')
        assert 'CREATE' in create_sql