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.
