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 Le mer. 21 avr. 2021 à 16:19, Mike Bayer <mike...@zzzcomputing.com> a écrit : > > > 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) > > > > > 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 > > > Le lun. 19 avr. 2021 à 23:24, Mike Bayer <mike...@zzzcomputing.com> a > écrit : > > > > > 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, > element.column.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 > > http://www.sqlalchemy.org/ > > 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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/0b3e9438-7631-46d8-bd3a-8f835a8c11c1n%40googlegroups.com > <https://groups.google.com/d/msgid/sqlalchemy/0b3e9438-7631-46d8-bd3a-8f835a8c11c1n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > 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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/e1053691-8d35-4452-9826-c4f4df04dbda%40www.fastmail.com > <https://groups.google.com/d/msgid/sqlalchemy/e1053691-8d35-4452-9826-c4f4df04dbda%40www.fastmail.com?utm_medium=email&utm_source=footer> > . > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > 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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAN14jWTbx5tKqfTjkyuL-3KLY6Gyykzrq8Qyvur-AqDMUqFWfw%40mail.gmail.com > <https://groups.google.com/d/msgid/sqlalchemy/CAN14jWTbx5tKqfTjkyuL-3KLY6Gyykzrq8Qyvur-AqDMUqFWfw%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > 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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/3f37f7c7-f13f-420d-b18d-b7cba086cad6%40www.fastmail.com > <https://groups.google.com/d/msgid/sqlalchemy/3f37f7c7-f13f-420d-b18d-b7cba086cad6%40www.fastmail.com?utm_medium=email&utm_source=footer> > . > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAN14jWSd2%2Bd0CtCjjE1QCJD4J6A3eSxGs-AcM2Vn%3DmXGfZHLgQ%40mail.gmail.com.
