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/07ef71e8-3d81-456b-b006-c1fb0c0603e7%40googlegroups.com.
