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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/ca3bf778-b93b-402c-99f6-94724a00fabf%40googlegroups.com.
