How to generate a file with DDL in the engine's SQL dialect in SQLAlchemy?

Suppose I have an engine pointing at MySQL database:

engine = create_engine('mysql://arthurdent:answer42@localhost/dtdb', echo=True)

I can populate dtdb with tables, FKs, etc by:


Is there an easy way to generate the SQL file that contains all the DDL statements instead of actually applying these DDL statements to dtdb?

So far I have resorted to capturing SQLAlchemy log output produced by echo=True, and editing it by hand. But that's just too painful.

It looks like SA has pretty elaborate schema management API, but I haven't seen examples of simply streaming the schema definitions as text.

Asked by: Audrey188 | Posted: 06-12-2021

Answer 1

The quick answer is in the SQLAlchemy 0.8 FAQ.

In SQLAlchemy 0.8 you need to do

engine = create_engine(
executor= lambda sql, *multiparams, **params: print (sql.compile(dialect=engine.dialect)))

In SQLAlchemy 0.9 the syntax is simplified.

engine = create_engine(
executor= lambda sql, *multiparams, **params: print (sql)

The longer answer is that capturing the output still has some slight issues. Like with the encoding of literals of types. But this hasn't been a big enough of an issue for anyone to step up and scratch their itch. You could always let SQLAlchemy programmatically create an empty database and dump the SQL from there.

The more difficult problem is the handling of schema migrations. This is where SQLAlchemy-migrate can help you.

Answered by: Sophia948 | Posted: 07-01-2022

Similar questions

python - (Only) generate SQL-Code with SqlAlchemy

Can i use the SqlAlchemy ORM-Mapper to only generate the SQL-Code? With simple tables i can use code like print print users_table.insert() print users_table.update() print users_table.delete() But with the ORM i have only found a way for SELECT-Statements: TestUser = User("John", "Doe") print session.query(User) How can i gene...

python - How to generate this query in sqlalchemy?

this query used to_char abd group_by select to_char(date_published, 'yyyymm') yo from blog group by yo; i have try In [3]: year_field = db.func.extract('year', ArticleModel.date_published) In [4]: month_field = db.func.extract('month', ArticleModel.date_published) In [5]: yonth_field = year_field * 100 + month_field In [6]: print db.session.query(yonth_field.label('yonth'), db.func.coun...

Generate Python Class and SQLAlchemy code from XSD to store XML on Postgres

I have some very complex XSD schemas to work with. By complex I mean that each of these XSD would correspont to about 20 classes / tables in a database, with each table having approximately 40 fields. And I have 18 different XSD like that to program. What I'm trying to achieve is: Get a XML file defined by the XSD and save all the data in a PostgreSQL database using SQLAlchemy. Basically I need a CRUD application t...

python - Sqlalchemy - how do I generate a query that returns only a field from a jsonb column

I'm trying to find the best way to generate a sql query that gets me a specific field from a jsonb column from my db. I want to generate a query like this: select images->'main' from scenes where id = 1; I found this reference for using with_entities, and this generates the query corre...

python - Use DB data model to generate SQLAlchemy models, schemas, and JSON response

Using Flask and SQLAlchemy for a Python webapp, my goal is to create a system in which I can: Import data models from an existing PostgreSQL DB, and map these to fields in corresponding SQLAlchemy models Use these SQLAlchemy models to automatically generate a schema. This schema will then be used to perform data validation on user-submitted data.(I'm currently trying to use Marshmallow, but am open t...

python - Generate schema SQL with SQLAlchemy

How does one generate the SQL/Migrate Code/Whatever with SQLAlchemy when using the Declarative Base? from sqlalchemy import * from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() engine = create_engine('mysql://root:password@localhost/mydb_dev', echo=True) metadata = MetaData(bind=engine) class User(Base): __table__ = Table("users",...

python - Generate objects from date range with sqlalchemy

I got the following python class: class CalendarMonth(Base, ...): __tablename__ = 'calendar_month' id = Column(UUID, primary_key=True, default=uuid.uuid4) month = Column(String(50)) ... I filled the database with all the months (Januari, etc..). So I have 12 rows in total in the database. What i want now is to have list function, which returns the mon...

python - Generate database schema using SQLAlchemy

This question already has answers here:

python - How to generate all days between two dates in SQLAlchemy?

I need a sequence of all days between two dates =, in raw PostgreSQL, I would use generate_series function. How to do this with SQLAlchemy?

python - Generate Excel with SQLALCHEMY and Pandas

I am trying to export the result of my query made with SQLALCHEMY. I am able to download the file, but the columns I receive, don't have the header names, and are not complete. Here I join an image of what I receive: But my database have this format:

python - How can I use UUIDs in SQLAlchemy?

Is there a way to define a column (primary key) as a UUID in SQLAlchemy if using PostgreSQL (Postgres)?

python - Getting random row through SQLAlchemy

How do I select one or more random rows from a table using SQLAlchemy?

python - How to add an automatic filter to a relation with SQLAlchemy?

I'm using SQLAlchemy 0.5rc, and I'd like to add an automatic filter to a relation, so that every time it tries to fetch records for that relation, it ignores the "remote" ones if they're flagged as "logically_deleted" (a boolean field of the child table) For example, if an object "parent" has a "children" relation that has 3 records, but one of them is logically deleted, when I query for "Parent" I'd like SQLA to f...

python - What is the sqlalchemy equivalent column type for 'money' and 'OID' in Postgres?

What is the sqlalchemy equivalent column type for 'money' and 'OID' column types in Postgres?

python - SQLAlchemy and empty columns

When I try to insert a new record into the database using SQLAlchemy and I don't fill out all values, it tries to insert them as "None" (instead of omitting them). It then complains about "can't be null" errors. Is there a way to have it just omit columns from the sql query if I also omitted them when declaring the instance?

python - SQLAlchemy DateTime timezone

SQLAlchemy's DateTime type allows for a timezone=True argument to save a non-naive datetime object to the database, and to return it as such. Is there any way to modify the timezone of the tzinfo that SQLAlchemy passes in so it could be, for instance, UTC? I realize that I could just use default=datetime.datetime.utcnow; however, this is a naive time that would happily ac...

python - How can I get all rows with keys provided in a list using SQLalchemy?

I have sequence of IDs I want to retrieve. It's simple: session.query(Record).filter( Is there a better way to do it?

python - How can I order objects according to some attribute of the child in sqlalchemy?

Here is the situation: I have a parent model say BlogPost. It has many Comments. What I want is the list of BlogPosts ordered by the creation date of its' Comments. I.e. the blog post which has the most newest comment should be on top of the list. Is this possible with SQLAlchemy?

python - SQLAlchemy - INSERT OR REPLACE equivalent

does anybody know what is the equivalent to SQL "INSERT OR REPLACE" clause in SQLAlchemy and its SQL expression language? Many thanks -- honzas

python - Defining a table with sqlalchemy with a mysql unix timestamp

Background, there are several ways to store dates in MySQ. As a string e.g. "09/09/2009". As integer using the function UNIX_TIMESTAMP() this is supposedly the traditional unix time representation (you know seconds since the epoch plus/minus leap seconds). As a MySQL TIMESTAMP, a mysql specific data type not the same than unix timestamps. As a MySQL Date field, another mysql spec...

Still can't find your answer? Check out these communities...

PySlackers | Full Stack Python | NHS Python | Pythonist Cafe | Hacker Earth | Discord Python