compiler tutorial questions

29 views
Skip to first unread message

sumau

unread,
Apr 19, 2021, 2:09:30 PM4/19/21
to sqlalchemy
Hello

I'm  trying to understand compilation and working through the compiler tutorial: https://docs.sqlalchemy.org/en/14/core/compiler.html

I was hoping for some clarification :

1) What is the difference between starting your method with visit_XXX instead of compile_XX?

2) self.cmd is used in the init of AlterColumn but I can't see where it is used in the visit_alter_column method.

3) What is the purpose of the ... in the visit_alter_column method? 

4) What is the example usage for AlterColumn? I tried this:

from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData

engine = create_engine('postgresql://***:***@localhost:5432/postgres')
conn = engine.connect()

metadata = MetaData()
users = Table('users', metadata,
Column('id', Integer),
Column('name', String),
Column('fullname', String),
)

metadata.create_all(engine)
users.AlterColumn(users.c.name, 'type int')

without success.

5) How would I create an AddColumn function? Something like this perhaps? 

class AddColumn(DDLElement):
def __init__(self, column, column_type):
self.column = column
self.column_type = column_type

@compiles(AddColumn)
def visit_add_column(element, compiler, **kw):
return "ALTER TABLE %s ADD COLUMN %s ..." % (element.table.name,
element.column.name)

Regards
Soumaya

Mike Bayer

unread,
Apr 19, 2021, 6:24:57 PM4/19/21
to noreply-spamdigest via sqlalchemy


On Mon, Apr 19, 2021, at 2:09 PM, sumau wrote:
Hello

I'm  trying to understand compilation and working through the compiler tutorial: https://docs.sqlalchemy.org/en/14/core/compiler.html

I was hoping for some clarification :

1) What is the difference between starting your method with visit_XXX instead of compile_XX?

no difference.  the compiler extension does not rely upon naming conventions, just that the decorator is there.  you can name any function anything or even use a lambda.


2) self.cmd is used in the init of AlterColumn but I can't see where it is used in the visit_alter_column method.

the example is incomplete and "cmd" would represent some kind of alteration to the column, in the SQL where you see the ellipses...this is not a fully real world example.


3) What is the purpose of the ... in the visit_alter_column method? 

in the example there it's a function.   Assuming you mean the function, that's where you implement how to turn your AlterColumn object into a SQL string.



4) What is the example usage for AlterColumn? I tried this:

from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData

engine = create_engine('postgresql://***:***@localhost:5432/postgres')
conn = engine.connect()

metadata = MetaData()
users = Table('users', metadata,
Column('id', Integer),
Column('name', String),
Column('fullname', String),
)

metadata.create_all(engine)
users.AlterColumn(users.c.name, 'type int')

without success.

noting that this AlterColumn isn't "real" and the SQL it genreates isn't valid, you would execute it:

with engine.connect() as conn:
    conn.execute(AlterColumn(...))



5) How would I create an AddColumn function? Something like this perhaps? 

class AddColumn(DDLElement):
def __init__(self, column, column_type):
self.column = column
self.column_type = column_type

@compiles(AddColumn)
def visit_add_column(element, compiler, **kw):
return "ALTER TABLE %s ADD COLUMN %s ..." % (element.table.name,

AddColumn is a class but other than that, that's the idea sure!





Regards
Soumaya


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

Soumaya Mauthoor

unread,
Apr 20, 2021, 10:08:22 AM4/20/21
to sqlal...@googlegroups.com
Hello Mike

Thanks for the quick response. Would you mind helping me write a complete example? It would help me understand compilation better :-) I would also be happy to turn this into a merge request if you think other people will benefit from it? I have also converted this into a sqlite example so using add column instead of alter column, to make it more generic and follow the sql expression tutorial. Also in the example I pass in the column type as a string, how would I pass in the type as a sqlalchemy data type?

This is what I have so far:
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.schema import DDLElement

from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData

engine = create_engine('sqlite:///:memory:', echo=True)


metadata = MetaData()
users = Table('users', metadata,
              Column('id', Integer),
              )

metadata.create_all(engine)

class AddColumn(DDLElement):

    def __init__(self, column_name, column_type):
        self.column_name = column_name
        self.column_type = column_type

@compiles(AddColumn)
def visit_add_column(element, column, compiler, **kw):
    return "ALTER TABLE %s ADD COLUMN %s %s %s" % (element.table.name, AddColumn.column_name, AddColumn.column_type)

s = AddColumn(users,'col1', 'int')
print(str(s))

with engine.connect() as conn:
    conn.execute(AddColumn(users,'col1', 'int'))
    print(conn.execute(users.select()).fetchall())
Regards
Soumaya


To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/e1053691-8d35-4452-9826-c4f4df04dbda%40www.fastmail.com.

Mike Bayer

unread,
Apr 21, 2021, 11:19:54 AM4/21/21
to noreply-spamdigest via sqlalchemy


On Tue, Apr 20, 2021, at 10:08 AM, Soumaya Mauthoor wrote:
Hello Mike

Thanks for the quick response. Would you mind helping me write a complete example? It would help me understand compilation better :-)


it looks like what you have below is a complete example now.



I would also be happy to turn this into a merge request if you think other people will benefit from it?

it depends on what concepts here you feel are not already well covered.    I think in order to illustrate the use of the "type_compiler" i mention below, we can expand AlterColumn to be a little bit more real world.

I dont think the doc section here would have a full front-to-back program however.   Those are more appropriate in the examples section, and I'm not sure AlterColumn is a good candidate for that because people are normally using Alembic for that kind of construct where they are already available.

More useful would be if i could understand what made this documentation difficult to understand , as I would suspect it has to do with prerequisite concepts needing to be explained better, which here would be better accomplished through linking to those sections.   We could also expand the "synopsis" at the top of https://docs.sqlalchemy.org/en/14/core/compiler.html to illustrate actually executing the first select() statement to make it clear that we are building SQL constructs that get executed.





I have also converted this into a sqlite example so using add column instead of alter column, to make it more generic and follow the sql expression tutorial. Also in the example I pass in the column type as a string, how would I pass in the type as a sqlalchemy data type?

you can get the string representation of a datatype using compiler.dialect.type_compiler.process(some_type).  We *should* definitely add a section to explain this and this would be useful to have in the AlterColumn example.    Below suggests a change which would make the example specific to AlterColumnType and also would use MySQL instead of PostgreSQL.   AddColumn() is not as good for an example here because there's a lot of syntax that goes into AddColumn which is better handled by SQLAlchemy's own routines, even when Alembic does it.

proposed section:

Compilers can also be made dialect-specific. The appropriate compiler will
be invoked for the dialect in use:

    class AlterColumnType(DDLElement):

        def __init__(self, column, type):
            self.column = column
            self.type = type

    @compiles(AlterColumnType)
    def visit_alter_column(element, compiler, **kw):
        return "ALTER TABLE %s ALTER COLUMN %s TYPE %s" % (
            element.column.table.name,
            element.column.name,
            compiler.dialect.type_compiler.process(element.type)
        )

    @compiles(AlterColumnType, 'mysql')
    def visit_alter_column(element, compiler, **kw):
        return "ALTER TABLE %s MODIFY %s %s" % (
            element.column.table.name,
            element.column.name,
            compiler.dialect.type_compiler.process(element.type)
        )


The second visit_alter_table will be invoked when any MySQL dialect is used,
such as::

    some_table = Table(
        "some_table", metadata,
        Column("q", Integer)
    )

    with engine.begin() as conn:
        conn.execute(AlterColumnType(
            some_table.c.q,
            String(50)
        ))

Would emit on MySQL::

    ALTER TABLE some_table MODIFY q VARCHAR(50)



Soumaya Mauthoor

unread,
Apr 21, 2021, 5:10:31 PM4/21/21
to sqlal...@googlegroups.com
Hello Mike

Your example was really helpful thanks! I hadn't realised you should access the variables defined in AlterColumn through the element variable, so my working example I posted before failed but but now works. 

If you wanted to expand the synopsis and make it more newbie-friendly I would include the following:
-add a link to explanation of compilation (in the first line)
-explanation of callable (as opposed to method)
-how the subclass needs to inherit from one of the classes defined in the "subclassing guidelines"  
-how the function that defines the compilation could be called anything as long as has the @compiles decorator, but recommendation is to start with compile_ or visit_
-explanation of each parameter in compile_mycolumn and what is their purpose
-how compile_mycolumn inherits the name attribute from ColumnClause so you don't need to specify additional attributes in MyColumn class
-if you did need to specify additional attributes, you should specify them in the class construct, as in the AlterColumn example
-replace print(str(s)) with print(s) (unless there's some difference I'm not aware off)

Let me know if you would like me to raise a merge request with all these suggestions (including the example you posted) :-)

Regards
Soumaya

Mike Bayer

unread,
Apr 21, 2021, 5:53:59 PM4/21/21
to noreply-spamdigest via sqlalchemy


On Wed, Apr 21, 2021, at 5:10 PM, Soumaya Mauthoor wrote:
Hello Mike

Your example was really helpful thanks! I hadn't realised you should access the variables defined in AlterColumn through the element variable, so my working example I posted before failed but but now works. 

If you wanted to expand the synopsis and make it more newbie-friendly I would include the following:
-add a link to explanation of compilation (in the first line)


-explanation of callable (as opposed to method)

i dont understand this part.   Python decorators can be on any callable.   methods are callables so are OK.



-how the subclass needs to inherit from one of the classes defined in the "subclassing guidelines"  

https://docs.sqlalchemy.org/en/14/core/compiler.html#synopsis :  "Usage involves the creation of one or more ClauseElement subclasses and one or more callables defining its compilation:"

can be any ClauseElement.   "Subclassing guidelines" gives more specifics





-how the function that defines the compilation could be called anything as long as has the @compiles decorator, but recommendation is to start with compile_ or visit_

there's no recommendation, call it anything.  I think basic understanding of Python decorators is the prerequisite here.

-explanation of each parameter in compile_mycolumn and what is their purpose

OK this could be more explicit

-how compile_mycolumn inherits the name attribute from ColumnClause so you don't need to specify additional attributes in MyColumn class

you could do this any way you wanted.    if you want to know about the "name" field on Column that's documented at https://docs.sqlalchemy.org/en/14/core/metadata.html#accessing-tables-and-columns


these are all things people should read before they jump into creating custom SQL.  I can put an "advanced usage" note at the top with links to these sections to read first




-if you did need to specify additional attributes, you should specify them in the class construct, as in the AlterColumn example

I think this is just basic Python programming

-replace print(str(s)) with print(s) (unless there's some difference I'm not aware off)

probably a good idea.


Reply all
Reply to author
Forward
0 new messages