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)
this is prerequisite knowledge and is in the tutorial: https://docs.sqlalchemy.org/en/14/tutorial/data.html#the-insert-sql-expression-construct > -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` <https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.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. > > 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 > > <https://groups.google.com/d/msgid/sqlalchemy/CAN14jWSd2%2Bd0CtCjjE1QCJD4J6A3eSxGs-AcM2Vn%3DmXGfZHLgQ%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/2afe2435-4bb9-4d73-a122-4a522001c843%40www.fastmail.com.
