search

ibmdb / python-ibmdbsa

Automatically exported from code.google.com/p/ibm-db.ibm-db-sa
Apache License 2.0
40 stars 59 forks source link

Error attempting to write dataframe to table and specifying replace #68

Closed marc-mclean1 closed 2 years ago

marc-mclean1 commented 5 years ago

I have tried the following engine strings:

from sqlalchemy import create_engine
#e = create_engine("db2+ibm_db://user:pass@host[:port]/database")
#e = create_engine("ibm_db_sa://user:pass@host[:port]/database")

retrieving data from table into dataframe is no problem:

import pandas as pd
dff = pd.read_sql("select * from model_output_1 limit 10", con=e)

writing data from a dataframe to a table is no problem in append mode:

dff.to_sql(name="model_output_4", con=e, schema=schema, if_exists="append", index=False)

as soon as I switch the if_exists parameter to "replace", I get the following error:

dff.to_sql(name="model_output_4", con=e, schema=schema, if_exists="replace", index=False)

TypeError Traceback (most recent call last)

in () ----> 1 dff.to_sql(name='model_output_4', con=e, schema='DASH5294', if_exists='replace', index=False) /opt/conda/lib/python3.6/site-packages/pandas/core/generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method) 2529 sql.to_sql(self, name, con, schema=schema, if_exists=if_exists, 2530 index=index, index_label=index_label, chunksize=chunksize, -> 2531 dtype=dtype, method=method) 2532 2533 def to_pickle(self, path, compression='infer', /opt/conda/lib/python3.6/site-packages/pandas/io/sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method) 458 pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index, 459 index_label=index_label, schema=schema, --> 460 chunksize=chunksize, dtype=dtype, method=method) 461 462 /opt/conda/lib/python3.6/site-packages/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method) 1171 if_exists=if_exists, index_label=index_label, 1172 schema=schema, dtype=dtype) -> 1173 table.create() 1174 table.insert(chunksize, method=method) 1175 if (not name.isdigit() and not name.islower()): /opt/conda/lib/python3.6/site-packages/pandas/io/sql.py in create(self) 575 "Table '{name}' already exists.".format(name=self.name)) 576 elif self.if_exists == 'replace': --> 577 self.pd_sql.drop_table(self.name, self.schema) 578 self._execute_create() 579 elif self.if_exists == 'append': /opt/conda/lib/python3.6/site-packages/pandas/io/sql.py in drop_table(self, table_name, schema) 1220 schema = schema or self.meta.schema 1221 if self.has_table(table_name, schema): -> 1222 self.meta.reflect(only=[table_name], schema=schema) 1223 self.get_table(table_name, schema).drop() 1224 self.meta.clear() /opt/conda/lib/python3.6/site-packages/sqlalchemy/sql/schema.py in reflect(self, bind, schema, views, only, extend_existing, autoload_replace, resolve_fks, **dialect_kwargs) 4197 4198 available = util.OrderedSet( -> 4199 bind.engine.table_names(schema, connection=conn) 4200 ) 4201 if views: /opt/conda/lib/python3.6/site-packages/sqlalchemy/engine/base.py in table_names(self, schema, connection) 2241 if not schema: 2242 schema = self.dialect.default_schema_name -> 2243 return self.dialect.get_table_names(conn, schema) 2244 2245 def has_table(self, table_name, schema=None): /opt/conda/lib/python3.6/site-packages/ibm_db_sa/base.py in get_table_names(self, connection, schema, **kw) 721 722 def get_table_names(self, connection, schema=None, **kw): --> 723 return self._reflector.get_table_names(connection, schema=schema, **kw) 724 725 def get_view_names(self, connection, schema=None, **kw): in get_table_names(self, connection, schema, **kw) /opt/conda/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py in cache(fn, self, con, *args, **kw) 41 info_cache = kw.get("info_cache", None) 42 if info_cache is None: ---> 43 return fn(self, con, *args, **kw) 44 key = ( 45 fn.__name__, /opt/conda/lib/python3.6/site-packages/ibm_db_sa/reflection.py in get_table_names(self, connection, schema, **kw) 208 where(systbl.c.tabschema == current_schema).\ 209 order_by(systbl.c.tabname) --> 210 return [self.normalize_name(r[0]) for r in connection.execute(query)] 211 212 @reflection.cache /opt/conda/lib/python3.6/site-packages/ibm_db_sa/reflection.py in (.0) 208 where(systbl.c.tabschema == current_schema).\ 209 order_by(systbl.c.tabname) --> 210 return [self.normalize_name(r[0]) for r in connection.execute(query)] 211 212 @reflection.cache TypeError: expected bytes, str found
abhi7436 commented 5 years ago

I am looking into it. Will update you shortly.

marc-mclean1 commented 4 years ago

it is also interesting to note, the following causes an error:

# does not work
e = create_engine("db2+ibm_db://user:pass@host[:port]/database")
e.connect()

but if I force a connection to be made via the read_sql() method, then the connect() call works fine.

# works fine
e = create_engine("db2+ibm_db://user:pass@host[:port]/database")
dff = pd.read_sql("select * from model_output_1 limit 10", con=e)
e.connect()
amukherjee28 commented 2 years ago

With the latest version this works fine. Sqlalchemy : 1.4.35 ibm_db_sa : 0.3.7 ibm_db : 3.1.1 python : 3.10.1

dff = pd.read_sql("select * from \"demoTab1\"",con=engine)
print(dff)

dff.to_sql(name='testTab1',con=engine, schema='NEWTON', if_exists='replace',index=False)

q = text("select * from \"testTab1\"")
conn = engine.connect()
result = conn.execute(q)
row = result.fetchall()
print(row)
2022-04-26 06:25:42,406 INFO sqlalchemy.engine.Engine SELECT "SYSCAT"."TABLES"."TABNAME"
FROM "SYSCAT"."TABLES"
WHERE "SYSCAT"."TABLES"."TABSCHEMA" = ? AND "SYSCAT"."TABLES"."TABNAME" = ?
2022-04-26 06:25:42,407 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa+ibm_db_sa does not support caching 0.00034s] (b'NEWTON', b'select * from "demoTab1"')
2022-04-26 06:25:42,410 INFO sqlalchemy.engine.Engine select * from "demoTab1"
2022-04-26 06:25:42,410 INFO sqlalchemy.engine.Engine [raw sql] ()
   id    Name         Address
0   1   Arnab  Kolkata-700094
1   2   Akhil    Bangalore-43
2   3  Aniket    Bangalore-52
3   4  Arghya      Kolkata-94
2022-04-26 06:25:42,433 INFO sqlalchemy.engine.Engine SELECT "SYSCAT"."TABLES"."TABNAME"
FROM "SYSCAT"."TABLES"
WHERE "SYSCAT"."TABLES"."TABSCHEMA" = ? AND "SYSCAT"."TABLES"."TABNAME" = ?
2022-04-26 06:25:42,435 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa+ibm_db_sa does not support caching 0.00127s] (b'NEWTON', b'testTab1')
2022-04-26 06:25:42,437 INFO sqlalchemy.engine.Engine SELECT "SYSCAT"."TABLES"."TABNAME"
FROM "SYSCAT"."TABLES"
WHERE "SYSCAT"."TABLES"."TABSCHEMA" = ? AND "SYSCAT"."TABLES"."TABNAME" = ?
2022-04-26 06:25:42,443 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa+ibm_db_sa does not support caching 0.00549s] (b'NEWTON', b'testTab1')
2022-04-26 06:25:42,449 INFO sqlalchemy.engine.Engine SELECT "SYSCAT"."TABLES"."TABNAME"
FROM "SYSCAT"."TABLES"
WHERE "SYSCAT"."TABLES"."TYPE" = ? AND "SYSCAT"."TABLES"."TABSCHEMA" = ? ORDER BY "SYSCAT"."TABLES"."TABNAME"
2022-04-26 06:25:42,489 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa+ibm_db_sa does not support caching 0.03998s] (b'T', b'NEWTON')
2022-04-26 06:25:42,494 INFO sqlalchemy.engine.Engine SELECT "SYSCAT"."COLUMNS"."COLNAME", "SYSCAT"."COLUMNS"."TYPENAME", "SYSCAT"."COLUMNS"."DEFAULT", "SYSCAT"."COLUMNS"."NULLS", "SYSCAT"."COLUMNS"."LENGTH", "SYSCAT"."COLUMNS"."SCALE", "SYSCAT"."COLUMNS"."IDENTITY", "SYSCAT"."COLUMNS"."GENERATED", "SYSCAT"."COLUMNS"."REMARKS"
FROM "SYSCAT"."COLUMNS"
WHERE "SYSCAT"."COLUMNS"."TABSCHEMA" = ? AND "SYSCAT"."COLUMNS"."TABNAME" = ? ORDER BY "SYSCAT"."COLUMNS"."COLNO"
2022-04-26 06:25:42,496 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa+ibm_db_sa does not support caching 0.00152s] (b'NEWTON', b'testTab1')
2022-04-26 06:25:42,520 INFO sqlalchemy.engine.Engine SELECT "SYSCAT"."INDEXES"."COLNAMES", "SYSCAT"."INDEXES"."INDNAME"
FROM "SYSCAT"."INDEXES"
WHERE "SYSCAT"."INDEXES"."TABSCHEMA" = ? AND "SYSCAT"."INDEXES"."TABNAME" = ? AND "SYSCAT"."INDEXES"."UNIQUERULE" = ? ORDER BY "SYSCAT"."INDEXES"."TABSCHEMA", "SYSCAT"."INDEXES"."TABNAME"
2022-04-26 06:25:42,520 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa+ibm_db_sa does not support caching 0.00042s] (b'NEWTON', b'testTab1', b'P')
2022-04-26 06:25:42,614 INFO sqlalchemy.engine.Engine SELECT "SYSIBM"."SQLFOREIGNKEYS"."FK_NAME", "SYSIBM"."SQLFOREIGNKEYS"."FKTABLE_SCHEM", "SYSIBM"."SQLFOREIGNKEYS"."FKTABLE_NAME", "SYSIBM"."SQLFOREIGNKEYS"."FKCOLUMN_NAME", "SYSIBM"."SQLFOREIGNKEYS"."PK_NAME", "SYSIBM"."SQLFOREIGNKEYS"."PKTABLE_SCHEM", "SYSIBM"."SQLFOREIGNKEYS"."PKTABLE_NAME", "SYSIBM"."SQLFOREIGNKEYS"."PKCOLUMN_NAME"
FROM "SYSIBM"."SQLFOREIGNKEYS"
WHERE "SYSIBM"."SQLFOREIGNKEYS"."FKTABLE_SCHEM" = ? AND "SYSIBM"."SQLFOREIGNKEYS"."FKTABLE_NAME" = ? ORDER BY "SYSIBM"."SQLFOREIGNKEYS"."KEY_SEQ"
2022-04-26 06:25:42,614 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa+ibm_db_sa does not support caching 0.00050s] (b'NEWTON', b'testTab1')
2022-04-26 06:25:42,857 INFO sqlalchemy.engine.Engine SELECT "SYSCAT"."INDEXES"."INDNAME", "SYSCAT"."INDEXES"."COLNAMES", "SYSCAT"."INDEXES"."UNIQUERULE", "SYSCAT"."INDEXES"."SYSTEM_REQUIRED"
FROM "SYSCAT"."INDEXES"
WHERE "SYSCAT"."INDEXES"."TABSCHEMA" = ? AND "SYSCAT"."INDEXES"."TABNAME" = ? ORDER BY "SYSCAT"."INDEXES"."TABNAME"
2022-04-26 06:25:42,857 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa+ibm_db_sa does not support caching 0.00042s] (b'NEWTON', b'testTab1')
2022-04-26 06:25:42,869 INFO sqlalchemy.engine.Engine SELECT "SYSCAT"."KEYCOLUSE"."CONSTNAME", "SYSCAT"."KEYCOLUSE"."COLNAME"
FROM "SYSCAT"."KEYCOLUSE", "SYSCAT"."TABCONST"
WHERE "SYSCAT"."KEYCOLUSE"."CONSTNAME" = "SYSCAT"."TABCONST"."CONSTNAME" AND "SYSCAT"."TABCONST"."TABNAME" = ? AND "SYSCAT"."TABCONST"."TABSCHEMA" = ? AND "SYSCAT"."TABCONST"."TYPE" = ? ORDER BY "SYSCAT"."KEYCOLUSE"."CONSTNAME"
2022-04-26 06:25:42,869 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa+ibm_db_sa does not support caching 0.00045s] (b'testTab1', b'NEWTON', b'U')
2022-04-26 06:25:42,877 INFO sqlalchemy.engine.Engine SELECT "SYSCAT"."TABLES"."REMARKS"
FROM "SYSCAT"."TABLES"
WHERE "SYSCAT"."TABLES"."TYPE" = ? AND "SYSCAT"."TABLES"."TABSCHEMA" = ? AND "SYSCAT"."TABLES"."TABNAME" = ?
2022-04-26 06:25:42,878 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa+ibm_db_sa does not support caching 0.00034s] (b'T', b'NEWTON', b'testTab1')
2022-04-26 06:25:42,886 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-04-26 06:25:42,888 INFO sqlalchemy.engine.Engine
DROP TABLE "NEWTON"."testTab1"
2022-04-26 06:25:42,891 INFO sqlalchemy.engine.Engine [no key 0.00372s] ()
2022-04-26 06:25:42,987 INFO sqlalchemy.engine.Engine COMMIT
2022-04-26 06:25:43,008 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-04-26 06:25:43,010 INFO sqlalchemy.engine.Engine
CREATE TABLE "NEWTON"."testTab1" (
        id BIGINT,
        "Name" CLOB,
        "Address" CLOB
)

2022-04-26 06:25:43,015 INFO sqlalchemy.engine.Engine [no key 0.00532s] ()
2022-04-26 06:25:43,243 INFO sqlalchemy.engine.Engine COMMIT
2022-04-26 06:25:43,252 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-04-26 06:25:43,254 INFO sqlalchemy.engine.Engine INSERT INTO "NEWTON"."testTab1" (id, "Name", "Address") VALUES (?, ?, ?)
2022-04-26 06:25:43,254 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa+ibm_db_sa does not support caching 0.00042s] ((1, 'Arnab', 'Kolkata-700094'), (2, 'Akhil', 'Bangalore-43'), (3, 'Aniket', 'Bangalore-52'), (4, 'Arghya', 'Kolkata-94'))
2022-04-26 06:25:43,261 INFO sqlalchemy.engine.Engine COMMIT
2022-04-26 06:25:43,268 INFO sqlalchemy.engine.Engine SELECT "SYSCAT"."TABLES"."TABNAME"
FROM "SYSCAT"."TABLES"
WHERE "SYSCAT"."TABLES"."TYPE" = ? AND "SYSCAT"."TABLES"."TABSCHEMA" = ? ORDER BY "SYSCAT"."TABLES"."TABNAME"
2022-04-26 06:25:43,268 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa+ibm_db_sa does not support caching 0.00038s] (b'T', b'NEWTON')
2022-04-26 06:25:43,271 INFO sqlalchemy.engine.Engine select * from "testTab1"
2022-04-26 06:25:43,275 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa+ibm_db_sa does not support caching 0.00405s] ()
[(1, 'Arnab', 'Kolkata-700094'), (2, 'Akhil', 'Bangalore-43'), (3, 'Aniket', 'Bangalore-52'), (4, 'Arghya', 'Kolkata-94')]

No change required.

Works as expected.