Closed Xnot closed 6 months ago
Hello @Xnot Can you please give me self contained example to reproduce this issue. As I tried below example and I'm not getting any warning. The "sample_table", I'm creating contains Boolean column as well.
import datetime
import decimal
import ibm_db
import ibm_db_sa
from sqlalchemy import create_engine, Table, Column, Integer, Float, String, Boolean, DateTime, Date, Numeric, MetaData, \
select, text
# Replace the connection parameters with your own
conn_str = 'ibm_db_sa://userID:Password@host:port/database'
# Create a SQLAlchemy engine
engine = create_engine(conn_str)
# Define the metadata for the sample_table
metadata = MetaData()
sample_table = Table('sample_table', metadata,
Column('id', Integer, primary_key=True),
Column('float_col', Float),
Column('int_col', Integer),
Column('string_col', String(50)),
Column('bool_col', Boolean),
Column('datetime_col', DateTime),
Column('date_col', Date),
Column('numeric_col', Numeric(10, 2))
)
# Create the sample_table
metadata.create_all(engine)
# Insert some data into the sample_table
with engine.connect() as conn:
conn.execute(sample_table.insert().values(
id=1,
float_col=3.14,
int_col=42,
string_col='hello',
bool_col=True,
datetime_col=datetime.datetime.now(),
date_col=datetime.date.today(),
numeric_col=decimal.Decimal('1234.56')
))
conn.execute(sample_table.insert().values(
id=2,
float_col=3.14,
int_col=42,
string_col='hello',
bool_col=True,
datetime_col=datetime.datetime.now(),
date_col=datetime.date.today(),
numeric_col=decimal.Decimal('1234.56')
))
conn.commit()
# Retrieve the data from the sample_table
with engine.connect() as conn:
stmt = select(sample_table)
results = conn.execute(stmt).fetchall()
conn.commit()
# Display the results
for row in results:
print(
f"ID: {row[0]}, Float Col: {row[1]}, Int Col: {row[2]}, String Col: {row[3]}, Bool Col: {row[4]}, Datetime Col: {row[5]}, Date Col: {row[6]}, Numeric Col: {row[7]}")
with engine.connect() as conn:
query = "drop table sample_table"
conn.execute(text(query))
conn.commit()
The issue only occurs when automapping/reflecting the database. Additionally, the issue is not reproducible with your sample table example because this lib's compiler is mapping sqlalchemy's Boolean
to db2's SMALLINT
, so BOOL_COL
is not actually boolean:
If you'd like, I can add a fix for that as well or make a separate PR for it.
Here's a reproducible example where the warning occurs:
import ibm_db
import ibm_db_sa
from sqlalchemy import create_engine, text
from sqlalchemy.ext.automap import automap_base
# Replace the connection parameters with your own
conn_str = f"ibm_db_sa://userID:Password@host:port/database"
# Create a SQLAlchemy engine
engine = create_engine(conn_str)
with engine.connect() as conn:
conn.execute(text("create table sample_schema.sample_table (bool_col boolean)"))
conn.commit()
base = automap_base()
base.prepare(engine, schema="sample_schema")
@Xnot Thank you for your response Yes you can raise a separate PR, If you want to add any fix.
This fixes a warning about boolean type not being recognized when loading a table that contains a boolean column.