The line causing the error now is: with engine.connect() as con: rs = con.execute('SELECT DISTINCT last_name, first_name,addressee_display, street_address, city, a.name, postal_code, f.name as country FROM civicrm_contact c, civicrm_entity_tag d , civicrm_address e, civicrm_state_province a, civicrm_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 civicrm_contribution, civicrm_contact c, civicrm_entity_tag d WHERE receive_date > \'2005-07-01\' and contact_id = c.id and c.id = entity_id and tag_id = 6 )')
This was working fine. (It is just my way of making sure I am connected to db and able to retrieve data (the old way). On Thursday, August 22, 2019 at 6:56:56 PM UTC-4, Ira Fuchs wrote: > > Weird. When I go back to just running the SQL query, I now get: > > InternalError: (pymysql.err.InternalError) (1115, "Unknown character set: > 'utf8mb4'") > (Background on this error at: http://sqlalche.me/e/2j85) > > I seem to be regressing. > > > On Thursday, August 22, 2019 at 6:53:46 PM UTC-4, Ira Fuchs wrote: >> >> Strange thing just occurred where I now get an error message that pymysql >> module is now missing. I redid a pip install and it seems to get passed >> that point and comes back with a new error. This was just running the SQL >> query to make sure I could do that. I commented it out of the notebook for >> now and went back to just running your converted query. I now get: >> >> NameError Traceback (most recent call >> last)<ipython-input-11-60d8f90ad86b> in <module> 1 s = Session() 2 >> subquery = (----> 3 s.query(Entity_Tag.display_name) 4 .filter( >> 5 Contribution.receive_date > datetime.date(2005, 7, 1), >> NameError: name 'Entity_Tag' is not defined >> >> >> >> >> On Thursday, August 22, 2019 at 6:37:39 PM UTC-4, Peter Schutt wrote: >>> >>> The `ondelete=...` keyword argument is a parameter to the `ForeignKey()` >>> constructor, not the `Column`. >>> >>> This: >>> >>> contact_id = Column(ForeignKey(u'civicrm_contact.id'), ondelete=u'CASCADE', >>> nullable=False, index=True) >>> >>> Should be this: >>> >>> contact_id = Column(ForeignKey(u'civicrm_contact.id', ondelete=u >>> 'CASCADE'), nullable=False, index=True) >>> >>> >>> On Friday, 23 August 2019 05:16:32 UTC+10, Ira Fuchs wrote: >>>> >>>> I fixed the syntax errors and tried your rewritten query but I got an >>>> error in the definitions: >>>> >>>> TypeError Traceback (most recent call >>>> last)<ipython-input-6-97e6a9952682> in <module>() 7 display_name >>>> = Column(String(128, u'utf8_unicode_ci')) 8 ----> 9 class >>>> CivicrmContribution(Base): 10 __tablename__ = >>>> 'civicrm_contribution' 11 >>>> <ipython-input-6-97e6a9952682> in CivicrmContribution() 11 12 >>>> id = Column(INTEGER, primary_key=True, comment=u'Contribution ID')---> 13 >>>> contact_id = Column(ForeignKey(u'civicrm_contact.id'), >>>> ondelete=u'CASCADE', nullable=False, index=True) 14 >>>> financial_type_id = Column(ForeignKey(u'civicrm_financial_type.id'), >>>> index=True) 15 contribution_page_id = >>>> Column(ForeignKey(u'civicrm_contribution_page.id', ondelete=u'SET NULL'), >>>> index=True) >>>> /Users/ihf/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/schema.pyc >>>> in __init__(self, *args, **kwargs) 1382 self.info = >>>> kwargs.pop("info") 1383 -> 1384 self._extra_kwargs(**kwargs) >>>> 1385 1386 def _extra_kwargs(self, **kwargs): >>>> /Users/ihf/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/schema.pyc >>>> in _extra_kwargs(self, **kwargs) 1385 1386 def >>>> _extra_kwargs(self, **kwargs):-> 1387 >>>> self._validate_dialect_kwargs(kwargs) 1388 1389 # @property >>>> /Users/ihf/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/base.pyc >>>> in _validate_dialect_kwargs(self, kwargs) 287 raise >>>> TypeError( 288 "Additional arguments should be "--> >>>> 289 "named <dialectname>_<argument>, got '%s'" % k >>>> 290 ) 291 dialect_name, arg_name = >>>> m.group(1, 2) >>>> TypeError: Additional arguments should be named <dialectname>_<argument>, >>>> got 'ondelete' >>>> >>>> >>>> On Wednesday, August 21, 2019 at 8:55:24 PM UTC-4, Peter Schutt wrote: >>>>> >>>>> 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/ca3bf778-b93b-402c-99f6-94724a00fabf%40googlegroups.com.