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/d6a276de-986d-4bd1-8d2e-f771859a67d2%40googlegroups.com.
