'SQLAlchemy mapping an existing table (IBM Db2 Issue)
I´ve been studying SQLAlchemy as a way to simplify some DB work that I have. The tables I work are previously created by other systems and I usually have read-only access.
I´ve read several questions here and some other sources to understand a little bit about SQLAlchemy, but unsuccessfully. I believe the error is something related to the table does not have a primary key, but I´ve tried several ways to bypass that without sucess.
To make it harder, the error log is really really hard to understand anything.
import sqlalchemy
engine = sqlalchemy.create_engine('ibm_db_sa://hjxXXXX:XXXXX.databases.appdomain.cloud:30756/BLUDB;security=SSL;', echo=True)
metadata = sqlalchemy.MetaData(bind=engine)
Up to here it works. I can even run some tests
insp = sqlalchemy.inspect(engine)
print(insp.get_table_names())
Output : 022-05-12 00:15:47,915 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-05-12 00:15:47,915 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa+ibm_db_sa does not support caching 0.00309s] (b'T', b'HJX16012')
['chicago_schools', 'chicago_socioeconomic_data', 'departments', 'employees', 'international_student_test_scores', 'jobs', 'job_history', 'locations', 'petsale', 'teste1']
But, when I try to map one table I :
Schools = sqlalchemy.Table('CHICAGO_SCHOOLS', metadata, sqlalchemy.Column('FakePK', sqlalchemy.Integer, primary_key= True) , autoload = True, autoload_with = engine)
I start to get some weird logs and doesn´t even know to to interpret them. Sorry to copy all log but I don´t know where to look!
2022-05-12 00:18:24,283 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"
FROM "SYSCAT"."COLUMNS"
WHERE "SYSCAT"."COLUMNS"."TABSCHEMA" = ? AND "SYSCAT"."COLUMNS"."TABNAME" = ? ORDER BY "SYSCAT"."COLUMNS"."COLNO"
2022-05-12 00:18:24,285 INFO sqlalchemy.engine.Engine [dialect ibm_db_sa+ibm_db_sa does not support caching 0.00265s] (b'HJX16012', b'CHICAGO_SCHOOLS')
---------------------------------------------------------------------------
NotImplementedError Traceback (most recent call last)
Input In [5], in <module>
----> 1 Schools = sqlalchemy.Table('CHICAGO_SCHOOLS', metadata, sqlalchemy.Column('School ID', sqlalchemy.Integer, primary_key= True) , autoload = True, autoload_with = engine)
File <string>:2, in __new__(cls, *args, **kw)
File c:\Users\fabio\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\deprecations.py:309, in deprecated_params.<locals>.decorate.<locals>.warned(fn, *args, **kwargs)
302 if m in kwargs:
303 _warn_with_version(
304 messages[m],
305 versions[m],
306 version_warnings[m],
307 stacklevel=3,
308 )
--> 309 return fn(*args, **kwargs)
File c:\Users\fabio\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\sql\schema.py:615, in Table.__new__(cls, *args, **kw)
613 return table
614 except Exception:
--> 615 with util.safe_reraise():
616 metadata._remove_table(name, schema)
File c:\Users\fabio\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\langhelpers.py:70, in safe_reraise.__exit__(self, type_, value, traceback)
68 self._exc_info = None # remove potential circular references
69 if not self.warn_only:
---> 70 compat.raise_(
71 exc_value,
72 with_traceback=exc_tb,
73 )
74 else:
75 if not compat.py3k and self._exc_info and self._exc_info[1]:
76 # emulate Py3K's behavior of telling us when an exception
77 # occurs in an exception handler.
File c:\Users\fabio\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\compat.py:207, in raise_(***failed resolving arguments***)
204 exception.__cause__ = replace_context
206 try:
--> 207 raise exception
208 finally:
209 # credit to
210 # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
211 # as the __traceback__ object creates a cycle
212 del exception, replace_context, from_, with_traceback
File c:\Users\fabio\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\sql\schema.py:611, in Table.__new__(cls, *args, **kw)
609 metadata._add_table(name, schema, table)
610 try:
--> 611 table._init(name, metadata, *args, **kw)
612 table.dispatch.after_parent_attach(table, metadata)
613 return table
File c:\Users\fabio\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\sql\schema.py:686, in Table._init(self, name, metadata, *args, **kwargs)
682 # load column definitions from the database if 'autoload' is defined
683 # we do it after the table is in the singleton dictionary to support
684 # circular foreign keys
685 if autoload:
--> 686 self._autoload(
687 metadata,
688 autoload_with,
689 include_columns,
690 _extend_on=_extend_on,
691 resolve_fks=resolve_fks,
692 )
694 # initialize all the column, etc. objects. done after reflection to
695 # allow user-overrides
697 self._init_items(
698 *args,
699 allow_replacements=extend_existing or keep_existing or autoload
700 )
File c:\Users\fabio\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\sql\schema.py:721, in Table._autoload(self, metadata, autoload_with, include_columns, exclude_columns, resolve_fks, _extend_on)
719 insp = inspection.inspect(autoload_with)
720 with insp._inspection_context() as conn_insp:
--> 721 conn_insp.reflect_table(
722 self,
723 include_columns,
724 exclude_columns,
725 resolve_fks,
726 _extend_on=_extend_on,
727 )
File c:\Users\fabio\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\reflection.py:791, in Inspector.reflect_table(self, table, include_columns, exclude_columns, resolve_fks, _extend_on)
788 if not found_table and not self.has_table(table_name, schema):
789 raise exc.NoSuchTableError(table_name)
--> 791 self._reflect_pk(
792 table_name, schema, table, cols_by_orig_name, exclude_columns
793 )
795 self._reflect_fk(
796 table_name,
797 schema,
(...)
803 reflection_options,
804 )
806 self._reflect_indexes(
807 table_name,
808 schema,
(...)
813 reflection_options,
814 )
File c:\Users\fabio\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\reflection.py:920, in Inspector._reflect_pk(self, table_name, schema, table, cols_by_orig_name, exclude_columns)
917 def _reflect_pk(
918 self, table_name, schema, table, cols_by_orig_name, exclude_columns
919 ):
--> 920 pk_cons = self.get_pk_constraint(
921 table_name, schema, **table.dialect_kwargs
922 )
923 if pk_cons:
924 pk_cols = [
925 cols_by_orig_name[pk]
926 for pk in pk_cons["constrained_columns"]
927 if pk in cols_by_orig_name and pk not in exclude_columns
928 ]
File c:\Users\fabio\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\reflection.py:528, in Inspector.get_pk_constraint(self, table_name, schema, **kw)
508 """Return information about primary key constraint on `table_name`.
509
510 Given a string `table_name`, and an optional string `schema`, return
(...)
525
526 """
527 with self._operation_context() as conn:
--> 528 return self.dialect.get_pk_constraint(
529 conn, table_name, schema, info_cache=self.info_cache, **kw
530 )
File c:\Users\fabio\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\interfaces.py:285, in Dialect.get_pk_constraint(self, connection, table_name, schema, **kw)
270 def get_pk_constraint(self, connection, table_name, schema=None, **kw):
271 """Return information about the primary key constraint on
272 table_name`.
273
(...)
283
284 """
--> 285 raise NotImplementedError()
NotImplementedError:
Solution 1:[1]
As snakecharmerb commented, this is a bug in the the IBM_DB_SA adapter:
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Ryan M |