render_index method ignores reflected kwargs #439
Description
Things to check first
-
I have searched the existing issues and didn't find my bug already reported there
-
I have checked that my bug is still present in the latest release
Sqlacodegen version
sqlacodegen==3.2.0 (from git+https://github.com/agronholm/sqlacodegen.git@615d79c6f6fdcb776c891179eb46516b1e8fd6f5)
SQLAlchemy version
sqlalchemy>=2.0.44
RDBMS vendor
MySQL (or compatible)
What happened?
While generating SQLAlchemy models, I found that the render_index method ignores the kwargs of reflected indexes, which results in missing index attributes in the generated code. The current implementation only hard-codes the unique argument, and I’m not sure whether this was an intentional design choice for specific scenarios. I encountered the following two issues:
- When the database specifies an index length, the generated model omits this length. If the column’s length exceeds the database’s default index length, the generated DDL fails to create the table.
- Full-text index parameters are also stored in
kwargs, but the generated model ignores them. As a result, the generated DDL creates a regular index instead of a full-text index.
from sqlacodegen.generators import TablesGenerator
from sqlalchemy import (
Column,
Index,
Integer,
MetaData,
String,
Table,
create_engine,
create_mock_engine,
)
# Create a MySQL engine (no actual connection needed for DDL generation)
def dump(sql, *multiparams, **params):
print(sql.compile(dialect=engine.dialect))
# Real engine
engine = create_engine("mysql+pymysql://root:[email protected]:3306/test")
mock_engine = create_mock_engine(
"mysql+pymysql://root:[email protected]:3306/test", executor=dump
)
metadata = MetaData()
# Define a table and index
t = Table(
"t",
metadata,
Column("id", Integer, primary_key=True),
Column(
"name",
String(1000),
),
)
# Index with dialect options
idx = Index("idx_col1", t.c.name, mysql_prefix="FULLTEXT", mysql_with_parser="ngram")
idx2 = Index("idx_col2", t.c.name, mysql_length={"name": 100})
metadata.create_all(mock_engine) # show ddl
# Drop table if exists and create new one
metadata.drop_all(engine)
metadata.create_all(engine)
# Reflect the table back
metadata_reflected = MetaData()
metadata_reflected.reflect(bind=engine)
# Generator
generator = TablesGenerator(metadata_reflected, engine, [])
# Find the reflected table
t_reflected = metadata_reflected.tables["t"]
# Render
print("--- Reflected Indexes ---")
for index in t_reflected.indexes:
print(f"Index name: {index.name}")
print(f"Rendered: {generator.render_index(index)}")
print(f"Kwargs: {dict(index.kwargs)}")
print("-" * 20)
# output
# CREATE TABLE t (
# id INTEGER NOT NULL AUTO_INCREMENT,
# name VARCHAR(1000),
# PRIMARY KEY (id)
# )
# CREATE INDEX idx_col2 ON t (name(100))
# CREATE FULLTEXT INDEX idx_col1 ON t (name) WITH PARSER ngram
# --- Reflected Indexes ---
# Index name: idx_col1
# Rendered: Index('idx_col1', 'name')
# Kwargs: {'mysql_prefix': 'FULLTEXT', 'mysql_with_parser': 'ngram'}
# --------------------
# Index name: idx_col2
# Rendered: Index('idx_col2', 'name')
# Kwargs: {'mysql_length': {'name': 100}}
# --------------------Additionally, while investigating this issue, I noticed that another built-in method, render_callable, has a similar problem: when kwargs contain string values, the generated code does not include the necessary quotation marks, which causes syntax errors. The relevant code snippet is as follows (omitted):
output = render_callable(
"func1",
"arg1",
"arg2",
kwargs={
"kwarg1": 1,
# args += tuple(f"{key}={value}" for key, value in kwargs.items())
"kwarg2": "value2",
"kwarg3": repr("value3"),
},
indentation=" ",
)
print(output)Database schema for reproducing the bug
CREATE TABLE t (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(1000),
PRIMARY KEY (id)
)
CREATE INDEX idx_col2 ON t (name(100))
CREATE FULLTEXT INDEX idx_col1 ON t (name) WITH PARSER ngram