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/94d76f48-35b5-4ac7-aa94-eb65eef8c206%40googlegroups.com.
