A couple of typos found re-reading my post, the Contribution.contact_id with a foreign key definition should be `contact_id = Column(Integer, ForeignKey('civicrm_contact'), nullable=False)`, I left out the closing parenthesis to the ForeignKey constructor.
Also, the very last line, I didn't close the parenthesis for the placeholders example, should read: "(e.g. `%(tag_id_2)s`) are passed...". Couldn't find a way to edit the original. On Thursday, 22 August 2019 10:43:17 UTC+10, Peter Schutt wrote: > > Hi Ira, > > For example Integer(xx) says that Integer cannot have parameters and >> Tinyint seems not to exist. > > > I'm aware of sqlacodegen, although never had the need to use it myself. > Those issues sound to me like it is using the mysql dialect types, not the > standard sqlalchemy types. For example `from sqlalchemy import Integer` > will not accept an argument but `from sqlalchemy.dialects.mysql import > INTEGER` does accept display_width as a parameter amongst others. Also, > `from sqlalchemy import TinyInteger` will raise an import error but `from > sqlalchemy.dialects.mysql import TINYINT` works. > > I presume that this front-end overhead will pay back when I write more >> interesting scripts that use data from Civicrm. If this works then I can >> map the remainder of the (many) tables. > > > Well, that's certainly the idea. The abstraction allows for writing code > that is both easier to write and read. Essentially you write a Python app > and let sqlalchemy handle the bridge between your application and the > database. It's not a magic bullet, there are plenty of sql paradigms still > exposed to you through the ORM and there are limitations to using it, > primarily performance relative to doing bulk operations (for example, see > here > <https://docs.sqlalchemy.org/en/13/faq/performance.html#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow>). > > Like any tool, you need to weigh up the pros and cons. For example, if you > are just using the ORM in order to generate schema or construct raw > queries, there are better ways. The benefit of the ORM comes once you start > understanding and using the richer feature set. For example, relationships. > > In the models that you built above, I notice that you haven't defined any > foreign keys on columns. I understand that your schema is already created > and so the foreign keys are already defined at the database level, but that > information is useful to sqlalchemy when you want to create relationships > <https://docs.sqlalchemy.org/en/13/orm/tutorial.html#building-a-relationship> > and > explicit joins in your queries. One example of applying a foreign key to > your columns would be on the `Contribution.contact_id` column. Defining > that as a foreign key would be as simple as changing the definition to > `contact_id = Column(Integer, ForeignKey('civicrm_contact', > nullable=False)`. This would then allow you to define a relationship > attribute on your Contribution model, e.g. `contact = > relationship('Contact')`. That allows you to access the instance that > represents the contact associated with a given contribution through > instance attribute access, e.g. `contribution_instance.contact` would > return an instance of `Contact` and sqlalchemy will issue the necessary > queries behind the scenes to make that happen. > > Now, how to create the equivalent SQL query? > > > Well, I've had to make a couple of guesses to fill in a couple of blanks. > I assumed that the column in your query `state_province_id` should be an > attribute on the `Address` model, and that the `display_name` column > belongs to the `Entity_Tag` model, as neither of those fields are defined > elsewhere. We haven't spoken about the Session in any detail yet either, > but you can read > https://docs.sqlalchemy.org/en/13/orm/tutorial.html#creating-a-session if > you need. I've purely tried to emulate your original query as closely as > possible. > > s = Session() > subquery = ( > s.query(Entity_Tag.display_name) > .filter( > Contribution.receive_date > datetime.date(2005, 7, 1), > Contribution.contact_id == Contact.id, > Contact.id == Entity_Tag.entity_id, > Entity_Tag.tag_id == 6, > ) > .subquery() > ) > result = ( > s.query( > Contact.last_name, > Contact.first_name, > Address.street_address, > Address.city, > Address.postalcode, > State.name, > Country.name.label("country"), > ) > .filter( > Contact.id == Entity_Tag.entity_id, > Entity_Tag.tag_id == 6, > Contact.id == Address.contact_id, > Address.state_province_id == State.id, > Address.country_id == Country.id, > Entity_Tag.display_name.notin_(subquery), > ) > .distinct() > .all() > ) > > This query issues this sql: > > SELECT DISTINCT civicrm_contact.last_name AS civicrm_contact_last_name, > civicrm_contact.first_name AS civicrm_contact_first_name, > civicrm_address.street_address > AS civicrm_address_street_address, civicrm_address.city AS > civicrm_address_city, civicrm_address.postalcode AS > civicrm_address_postalcode, civicrm_state_province.name AS > civicrm_state_province_name, civicrm_country.name AS country > FROM civicrm_contact, civicrm_address, civicrm_state_province, > civicrm_country, civicrm_entity_tag > WHERE civicrm_contact.id = civicrm_entity_tag.entity_id AND > civicrm_entity_tag.tag_id = %(tag_id_1)s AND civicrm_contact.id = > civicrm_address.contact_id AND civicrm_address.state_province_id = > civicrm_state_province.id AND civicrm_address.country_id = civicrm_country > .id AND civicrm_entity_tag.display_name NOT IN (SELECT civicrm_entity_tag. > display_name > FROM civicrm_contribution > WHERE civicrm_contribution.receive_date > %(receive_date_1)s AND > civicrm_contribution.contact_id = civicrm_contact.id AND civicrm_contact.id > = civicrm_entity_tag.entity_id AND civicrm_entity_tag.tag_id = %(tag_id_2) > s) > > Notice that the `FROM` clause in the subquery only contains one column, > this is becuase the other columns have been automatically correlated with > the columns expressed in the enclosing query. See here: > https://docs.sqlalchemy.org/en/13/core/tutorial.html#correlated-subqueries > . > > The scalar values in the query that have been substituted with > placeholders (e.g. `%(tag_id_2)s` are passed to the db driver in dictionary > form along with the query, e.g. `{'tag_id_1': 6, 'receive_date_1': > datetime.date(2005, 7, 1), 'tag_id_2': 6}`. > > On Wednesday, 21 August 2019 10:20:30 UTC+10, Ira Fuchs wrote: >> >> I noticed that some of the definitions created by sqlacodegen are >> resulting in errors in python. For example Integer(xx) says that Integer >> cannot have parameters and Tinyint seems not to exist. Perhaps this is a >> result of my running a less than current version of mysql? >> >> On Tuesday, August 20, 2019 at 6:58:03 PM UTC-4, Ira Fuchs wrote: >>> >>> Just an aside: I discovered the sqlacodegen tool which will create the >>> sqlalchemy class definitions automatically. I ran it against the civicrm >>> mysql db and it worked. The definitions comprise 5881 lines (428KB). >>> Fortunately I don't need much of it for my purposes. >>> >>> On Tuesday, August 20, 2019 at 6:15:46 PM UTC-4, Ira Fuchs wrote: >>>> >>>> I think I may have all the definitions: >>>> >>>> class Contact(Base): >>>> __tablename__ = "civicrm_contact" >>>> id = Column(Integer, primary_key=True) >>>> last_name = Column(String(20), nullable=False) >>>> first_name = Column(String(20), nullable=False) >>>> class Contribution(Base): >>>> __tablename__ = "civicrm_contribution" >>>> id = Column(Integer, primary_key=True) >>>> contact_id = Column(Integer, nullable=False) >>>> receive_date = Column(DateTime,nullable=False) >>>> total_amount = Column(DECIMAL(precision=20,scale=2), nullable=False) >>>> class Address(Base): >>>> __tablename__ = "civicrm_address" >>>> id = Column(Integer, primary_key=True) >>>> contact_id = Column(Integer, nullable=False) >>>> street_address = Column(String(96), nullable=False) >>>> city = Column(String(64), nullable=False) >>>> postalcode = Column(String(64), nullable=False) >>>> country_id = Column(Integer, nullable=False) >>>> class Country(Base): >>>> __tablename__ = "civicrm_country" >>>> id = Column(Integer, primary_key=True) >>>> name = Column(String(64)) >>>> class State(Base): >>>> __tablename__ = "civicrm_state_province" >>>> id = Column(Integer, primary_key=True) >>>> name = Column(String(64), nullable=False) >>>> abbreviation = Column(String(4), nullable=False) >>>> country_id = Column(Integer, nullable=False) >>>> class Entity_Tag(Base): >>>> __tablename__ = "civicrm_entity_tag" >>>> id = Column(Integer, primary_key=True) >>>> entity_id = Column(Integer, nullable=False) >>>> tag_id = Column(Integer, nullable=False) >>>> >>>> Now, how to create the equivalent SQL query? I presume that this >>>> front-end overhead will pay back when I write more interesting scripts >>>> that >>>> use data from Civicrm. If this works then I can map the remainder of the >>>> (many) tables. >>>> >>>> On Tuesday, August 20, 2019 at 5:15:36 PM UTC-4, Ira Fuchs wrote: >>>>> >>>>> OK, fixed the case: >>>>> >>>>> class Contact(Base): >>>>> __tablename__ = "civicrm_contact" >>>>> id = Column(Integer, primary_key=True) >>>>> last_name = Column(String(20), nullable=False) >>>>> first_name = Column(String(20), nullable=False) >>>>> class Contribution(Base): >>>>> __tablename__ = "civicrm_contribution" >>>>> id = Column(Integer, primary_key=True) >>>>> contact_id = Column(Integer, nullable=False) >>>>> receive_date = Column(DateTime,nullable=False) >>>>> total_amount = Column(DECIMAL(precision=20,scale=2), >>>>> nullable=False) >>>>> >>>>> On Tuesday, August 20, 2019 at 3:59:55 PM UTC-4, Ira Fuchs wrote: >>>>>> >>>>>> OK, I made some progress (see screenshot). I don't need the full >>>>>> model to be represented as I will not need many of the columns in each >>>>>> of >>>>>> the tables. If I could flesh this out enough so that I can do the query >>>>>> posted above I can probably use that as a template to move forward. >>>>>> >>>>>> On Monday, August 19, 2019 at 7:11:59 AM UTC-4, Peter Schutt wrote: >>>>>>> >>>>>>> Cool, how about we walk through creating a model for your schema as >>>>>>> an example. As we go I can point you to the relevant sections of the >>>>>>> tutorials/docs as they are great and will explain the details much >>>>>>> better >>>>>>> than I can, and I'll try to address any questions you have along the >>>>>>> way. >>>>>>> >>>>>>> The first thing you need is your Base class, all of your ORM models >>>>>>> will inherit from this: >>>>>>> >>>>>>> from sqlalchemy.ext.declarative import declarative_base >>>>>>> >>>>>>> Base = declarative_base() >>>>>>> >>>>>>> >>>>>>> Your models need to inherit from Base, need a __tablename__ class >>>>>>> attribute and at least a primary key column. For example, a class to >>>>>>> represent your "db_contact" table might look like this: >>>>>>> >>>>>>> from sqlalchemy import Column, Integer, String >>>>>>> >>>>>>> class Contact(Base): >>>>>>> >>>>>>> __tablename__ = "db_contact" >>>>>>> >>>>>>> id = Column(Integer, primary_key=True) >>>>>>> last_name = Column(String(20), nullable=False) >>>>>>> first_name = Column(String(20), nullable=False) >>>>>>> >>>>>>> Some things to note this far along: >>>>>>> >>>>>>> - In this example, the name of the Column in the database is >>>>>>> inferred from the name of the class attribute that the Column is >>>>>>> assigned >>>>>>> to. >>>>>>> - Column constructors take arguments that define specifics of >>>>>>> how the column is defined in the database. E.g. `primary_key=True`, >>>>>>> `nullable=False`. By declaring a single integer column as primary >>>>>>> key, it >>>>>>> will automatically be specified as autoincrement in the schema, as >>>>>>> well as >>>>>>> the primary key. Setting nullable=False on other columns declares >>>>>>> that a >>>>>>> NOT NULL constraint should be set on those columns. >>>>>>> - As you are using MySQL, String types (which represent VARCHAR >>>>>>> type in the database) require a length. If you were creating models >>>>>>> for >>>>>>> other backends such as SQLite or postgres, then that isn't required. >>>>>>> >>>>>>> Nothing that I've covered here isn't covered in the early stages of >>>>>>> the ORM Tutorial >>>>>>> <https://docs.sqlalchemy.org/en/13/orm/tutorial.html#declare-a-mapping>. >>>>>>> >>>>>>> Also, you can read the Column API >>>>>>> <https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.Column> >>>>>>> to >>>>>>> get a feel for the arguments that you can pass to Column in order to >>>>>>> define >>>>>>> your schema. >>>>>>> >>>>>>> One more important element is the Foreign Key. I'll use your >>>>>>> "db_entity_tag" table to give you an example of that: >>>>>>> >>>>>>> from sqlalchemy import ForeignKey >>>>>>> >>>>>>> class EntityTag(Base): >>>>>>> >>>>>>> __tablename__ = "db_entity_tag" >>>>>>> >>>>>>> id = Column(Integer, primary_key=True) >>>>>>> entity_id = Column( >>>>>>> Integer, >>>>>>> ForeignKey('db_contact.id', ondelete="CASCADE"), >>>>>>> nullable=False, >>>>>>> ) >>>>>>> >>>>>>> Pass the name of the related column to the ForeignKey object (note >>>>>>> that this isn't "Class.attributename", it is "tablename.columnname") >>>>>>> and >>>>>>> pass the ForeignKey object as a positional argument to the Column >>>>>>> constructor _after_ the column type argument. ForeignKey api is >>>>>>> documented >>>>>>> here >>>>>>> <https://docs.sqlalchemy.org/en/13/core/constraints.html#sqlalchemy.schema.ForeignKey> >>>>>>> and >>>>>>> touched on in the orm tutorial here >>>>>>> <https://docs.sqlalchemy.org/en/13/orm/tutorial.html#building-a-relationship> >>>>>>> . >>>>>>> >>>>>>> Have a go at filling in the columns that I've missed in those models >>>>>>> above and defining the rest of the tables in your schema and once you >>>>>>> get >>>>>>> that done we can move on. Happy to try to answer any questions you >>>>>>> might >>>>>>> have along the way. >>>>>>> >>>>>>> >>>>>>> On Monday, 19 August 2019 12:36:21 UTC+10, Ira Fuchs wrote: >>>>>>>> >>>>>>>> Thanks for your reply and offer to help. I am able to create an >>>>>>>> Engine and connect to the MySQL db. I can execute simple sql queries >>>>>>>> although I wasn't able to get the query I posted to work due to a >>>>>>>> syntax >>>>>>>> error (probably having to do with the quotes). I have not mapped any >>>>>>>> tables >>>>>>>> to classes. >>>>>>>> >>>>>>>> On Sunday, August 18, 2019 at 8:54:57 PM UTC-4, Peter Schutt wrote: >>>>>>>>> >>>>>>>>> Hi Ira, I'd be happy to help you find your feet with the >>>>>>>>> SQLAlchemy ORM. >>>>>>>>> >>>>>>>>> In general when creating an application that uses the SQLAlchemy >>>>>>>>> ORM, you would start with an Engine (for connecting to the db), a >>>>>>>>> declarative base class (maps db table to python class) and a Session >>>>>>>>> instance (for using a connection to issue queries). Do you have any >>>>>>>>> familiarity with those concepts? >>>>>>>>> >>>>>>>>> >>>>>>>>> On Monday, 19 August 2019 02:34:05 UTC+10, Ira Fuchs wrote: >>>>>>>>>> >>>>>>>>>> I am new to sqlalchemy and I would like to begin using it to >>>>>>>>>> create scripts with some sql queries that I have been using but need >>>>>>>>>> to >>>>>>>>>> change from time to time. It would help a lot if someone could >>>>>>>>>> translate >>>>>>>>>> one of these queries to python sqlalchemy using an ORM (not simply >>>>>>>>>> executing the query as is) so that I could use this as a template >>>>>>>>>> for other >>>>>>>>>> queries, or at least learn from it. >>>>>>>>>> >>>>>>>>>> One of my queries looks like this: >>>>>>>>>> >>>>>>>>>> SELECT DISTINCT last_name, first_name, street_address, city, >>>>>>>>>> a.name, postal_code, f.name as country FROM db_contact c, >>>>>>>>>> db_entity_tag d , db_address e, db_state_province a, db_country f >>>>>>>>>> WHERE >>>>>>>>>> c.id = d.entity_id and tag_id = 6 and c.id = e.contact_id AND >>>>>>>>>> state_province_id = a.id and e.country_id = f.id and >>>>>>>>>> display_name not in ( SELECT display_name FROM db_contribution, >>>>>>>>>> db_contact >>>>>>>>>> c, db_entity_tag d WHERE receive_date > '2005-07-01' and contact_id >>>>>>>>>> = >>>>>>>>>> c.id and c.id = entity_id and tag_id = 6 ) >>>>>>>>>> >>>>>>>>>> Thanks for any help. >>>>>>>>>> >>>>>>>>>> -- 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/e6cca0bd-5adc-41e1-80bb-e2bdbc7f4541%40googlegroups.com.