{
    "info": {
        "author": "Tim Savannah",
        "author_email": "kata198@gmail.com",
        "bugtrack_url": null,
        "classifiers": [
            "Development Status :: 4 - Beta",
            "License :: OSI Approved :: GNU Lesser General Public License v2 (LGPLv2)",
            "Programming Language :: Python",
            "Programming Language :: Python :: 2",
            "Programming Language :: Python :: 2.7",
            "Programming Language :: Python :: 3",
            "Programming Language :: Python :: 3.3",
            "Programming Language :: Python :: 3.4",
            "Programming Language :: Python :: 3.5",
            "Programming Language :: Python :: 3.6",
            "Programming Language :: SQL",
            "Topic :: Database",
            "Topic :: Database :: Front-Ends",
            "Topic :: Software Development :: Libraries :: Python Modules"
        ],
        "description": "ichorORM\n========\n\nA python library for postgresql focused on performance and supporting ORM and query-building functionality. It supports transactions and autocommit mode.\n\nichorORM uses psycopg2 to interact with a configured SQL database and provides an easy and efficient abstraction layer.\n\nThe default mode handles SQL parameterization to automatically protect against SQL-injection attacks.\n\n\nConnecting to the Database\n==========================\n\nichorORM provides two means of connection to the database.\n\n**Global Connection**\n\nThe first is the \"global\" connection info. This is the default used for everything unless specified otherwise.\n\nSet these fields via the *setGlobalConnectionParams* method\n\n\tsetGlobalConnectionParams(host='localhost', port=5432, dbname='my\\_db', user='Super User', password='cheese')\n\n\nFields can be omitted and they will not be sent, or default will be used. For example, if your ident specifies a default user to be your system user, or if you don't have a password for localhost accounts, etc. , then don't set those fields.\n\n**Individual Connections**\n\nWhile the global provides the defaults to use, you may also create standalone connections (for example, to copy data from one database and then connect and save to another).\n\nYou will also need to create and pass along a connection object when doing transactions.\n\n\nThe *getDatabaseConnection* method will return a new connection. If you don't provide any arguments, it will inherit the connection info from the global connection. Any arguments you do provide will override the associated global connection parameter for the returned connection.\n\n\t# Get a connection same settings as global connection\n\n\tdbConn = getDatabaseConnection() \n\n\t# Get a connection same settings as global connection (for transactions)\n\n\tdbConn = getDatabaseConnection(isTransactionMode=True)\n\n\t# Get a connection using same settings but connect to a different database:\n\n\tdbConnBak = getDatabaseConnection(db\\_name='bak\\_my\\_db')\n\n\t\n\nModels\n======\n\nThis section will deal with your ORM models and the associated methods. Models are also used with the query builder, which is covered in the \"Query Builder\" section.\n\n\nYour SQL tables should be represented by a DatabaseModel object ( ichorORM.DatabaseModel ).\n\nEach table is expected to have a serial sequence primary key (generally called \"id\") for sanity and performance reasons.\n\nYou may find it useful to add relevant methods to this model object.\n\n\n\tfrom ichorORM import DatabaseModel\n\n\tclass Person(DatabaseModel):\n\n\t\t# TABLE\\_NAME \\- This is the name of the corrosponding table in your database\n\n\t\tTABLE\\_NAME = 'Person'   \n\n\t\t# FIELDS \\- A list of all fields on this table\n\n\t\tFIELDS = [ 'id', 'first\\_name', 'last\\_name', 'age', 'birthday', 'gender', 'eye\\_color', 'ethnicity', 'title' ]\n\n\t\t# REQUIRED\\_FIELDS \\- A list of NOT NULL fields which will validate prior to \n\n\t\t#        insertObject/createAndSave (cannot be None or ValueError raised)\n\n\t\tREQUIRED\\_FIELDS = [ 'first\\_name' ]\n\n\t\t# DEFAULT\\_FIELD\\_VALUES \\- A map for client\\-side defaults.\n\n\t\t#        When a new object is created, the fields are assigned these values\n\n\t\t#         if not explicitly set to something else\n\n\t\tDEFAULT\\_FIELD\\_VALUES = { 'title' : 'General Employee' }\n\n\t\t# PRIMARY\\_KEY \\- If your primary serial key is not 'id', name it here\n\n\t\t# PRIMARY\\_KEY = 'serial\\_num'\n\n\n**Creating and Saving an entry**\n\nAll field names found in the 'FIELDS' array on your model can be set by passing as a kwarg to \\_\\_init\\_\\_.\n\nThey also become variable members of the object.\n\nAny defaults found in DEFAULT\\_FIELD\\_VALUES will be applied here if a different value isn't explicitly set.\n\n\tpersonObj = Person(first\\_name='Tim', age=30, gender='male', eye\\_color='Hazel')\n\n\tprint ( \"%s is a %d year old %s whose job title is %s.\" %( personObj.first\\_name, personObj.age, personObj.gender, personObj.title ) )\n\nThe above code will output \"Tim is a 30 year old male whose job title is General Employee.\"\n\n\nTo save this object, we call the *insertObject* method\n\n\tpersonObj.insertObj()\n\nThis will perform an INSERT of that person, and it will set the primary key on the personObj.\n\nSo if the next serial sequence item was 5, personObj.id would now == 5.\n\nThis commits the transaction right away. How to group multiple actions within a single transaction will be covered later.\n\n.\n\nYou may also use the static method *createAndSave* to immediately save and return a given object:\n\n\tpersonObj = Person.createAndSave(first\\_name='Tim', age=30, gender='male', eye\\_color='Hazel')\n\nThis will have all the same field values, including primary key set, as the earlier insert method.\n\ncreateAndSave also supports transactions which will be covered later.\n\n\n**Updating an entry**\n\nAny model that is saved can be updated simply by setting the appropriate field values and calling *updateObject* as seen below:\n\n\tpersonObj.last\\_name = 'Johnson'\n\n\tpersonObj.title = 'General Manager'\n\n\t# updateObject method \\- Provide a list of fields to update\n\n\tpersonObj.updateObject( ['last\\_name', 'title'] )\n\n\nThis method also supports transactions, with the default being immediate commit.\n\n\n**Deleting an entry**\n\nAn object can be deleted by calling the *delete* method\n\n\toldId = personObj.delete()\n\nThis will return the old primary key (in oldId above) and clear the primary key field off \"personObj.\"\n\n\n**Fetching an entry**\n\nAn entry can be fetched via several means. More advanced means are covered in latter sections, this will focus on the methods available through *DatabaseModel*\n\n\nBy primary key, use *get*:\n\n\tpersonObj = Person.get(5) # If primary key is 5\n\n\nBy field values, use *filter*:\n\n\tpersonObj = Person.filter(age\\_\\_gt=20, gender='male', eye\\_color\\_\\_in=['Brown', 'Hazel'])\n\n\nThis will return a list of all Person objects where *age > 20* and *gender = 'male'* and *eye color is one of \"Brown\" or \"Hazel\"*.\n\nThe standard \"filter\" rules apply here, double underscore and operation.\n\n\t\\* = or \\_\\_eq=   \\- Equals operator\n\n\t\\* \\_\\_ne= \\- Not Equal ( <> ) operator\n\n\t\\* \\_\\_ is or \\_\\_isnot \\- IS or IS NOT ( you can use \\*None\\* to represent NULL, or the SQL\\\\\\_NULL constant )\n\n\t\\* \\_\\_in or \\_\\_notin \\- IN or NOT IN a list of values\n\n\t\\* \\_\\_isnull=True or \\_\\_isnull=False \\- Compare IS NULL or IS NOT NULL.\n\nOr to fetch all objects of a given table, use the *all* method\n\n\tallPeople = Person.all()\n\n\nAny objects fetched can be updated just by changing property values and calling *.updateObject*\n\n\n**Other Methods**\n\n*asDict* - This will return a dict of the field names -> values\n\n\tpersonDict = personObj.asDict()\n\n\nForeign Relations\n=================\n\nForeign relations ( a foreign key that points to another object(s) ) can be defined easily by implementing the classmethod DatabaseModel.getModelRelations\n\n\nFor example, consider that you have a model *Person* with primary key \"id\", and a model *Meal* that contains a foreign key reference \"id\\_person\". You can implement these relations like so:\n\nAdd \"meals\" relation to Person to fetch all Meals where Meal.id_person = Person.id\n\n\tfrom ichorORM.relations import OneToManyRelation\n\n\tclass Person(DatabaseModel):\n\n\t\t...\n\n\t\t@classmethod\n\n\t\tdef getModelRelations(cls):\n\t\t\t\n\t\t\t# Import the model here to prevent circular reference\n\n\t\t\tfrom .Meal import Meal\n\n\t\t\t# Reference Person.id to Meal.id\\_person\n\n\t\t\tmealRelation = OneToManyRelation('id', Meal, 'id\\_person')\n\n\t\t\treturn {\n\n\t\t\t\t'meals' : mealRelation, # Add property \"Person.meals\" which will follow this relation\n\n\t\t\t\tMeal : mealRelation,    # Add the same relation available by calling Person.getRelation(Meal)\n\n\t\t\t}\n\nAdd \"person\" relation to Meal to fetch the Person where Meal.id\\_person = Person.id\n\n\tfrom ichorORM.relations import OneToOneRelation\n\n\tclass Meal(DatabaseModel):\n\n\t\t...\n\n\t\t@classmethod\n\n\t\tdef getModelRelations(cls):\n\t\t\t\n\t\t\t# Import the model here to prevent circular reference\n\n\t\t\tfrom .Person import Person\n\n\t\t\t# Reference Meal.id\\_person to Person.id\n\n\t\t\tpersonRelation = OneToOneRelation('id\\_person', Person, 'id')\n\n\t\t\treturn {\n\n\t\t\t\t'person' : personRelation, # Add property \"Meal.person\" which will follow this relation\n\n\t\t\t\tPerson : personRelation, # Add the same relation available by calling Meal.getRelation(Person)\n\n\t\t\t}\n\n\nThere are two ways to use this relation:\n\n**By property**\n\nFor any item in the dict returned by *getModelRelations* where the key is a string, a property is added to the model which, upon access, will follow the relation.\n\nFor instance, in the above examples, if I have an instance of a Meal object, I can access the related person like so:\n\n\tmyMealObj = .... # Fetch the Meal object\n\n\tpersonWhomAteMeal = myMealObj.person # follow the relation with key 'person' to return the Person where id = myMealObj.id\\_person\n\nAnd from an instance of a Person object I can access a list of all related meals like so:\n\n\tmyPersonObj = .... # Fetch the Person object\n\n\tmyPersonsMeals = myPersonObj.meals # follow the relation with key \"meals\" to return all Meal objects where id\\_person = myPersonObj.id\n\nKeep in mind that each time this property is accessed a query is executed to follow this relation. Thus, if you want to fetch the relation once then save the result to a local variable and use that local variable instead of re-accessing the property.\n\n**By method**\n\nFor any item in the dict returned by *getModelRelations* you can pass the key to DatabaseModel.getRelated\n\nFor instance, using the above examples, if I have an instance of a Meal object I can access the related person like so:\n\n\tmyMealObj = ..... # Fetch the Meal object\n\n\t# We can use the string 'person' as returned in getModelRelations\n\n\tpersonWhomAteMeal = myMealObj.getRelated('person')\n\n\t# Or we can use the model itself which we also provided as a key returned by getModelRelations\n\n\tpersonWhomAteMeal = myMealObj.getRelated(Person)\n\n\nFor now, these relations are \"read-only\", that is, assinging myMealObj.person = someOtherPerson is not effective.\n\n\nTransactions\n============\n\nichorORM supports transactions easily to ensure atomic operations which affect several tables, and for bulk-actions.\n\n\n**Each transaction needs a connection**\n\nStart by obtaining a new connection you will use for this transaction. Make sure to set *isTransactionMode* to True to enable read-commit instead of auto-commit mode\n\n\tdbConn = getDatabaseConnection(isTransactionMode=True)\n\n\n**For use on model methods**\n\nThis section covers how to do transactions within the DatabaseModel methods. Doing a transaction with the query builder will be discussed later.\n\n\nEach of the \"save action\" methods ( *insertObject*, *updateObject*, *createAndSave* ) take two parameters you will set to performa transaction.\n\nThese are \"dbConn\" in which you will pass the transaction connection you opened in step 1, and \"doCommit\" which you will set to False. When you are done, you can call *commit* on the connection object.\n\n\tdbConn = getDatabaseConnection(isTransactionMode=True)\n\n\tperson1 = Person(first\\_name='John', last\\_name='Smith', age=34)\n\n\tperson2 = Person(first\\_name='Jane', last\\_smith='Doe', age=29)\n\n\tperson3 = Person(first\\_name='Bob', last\\_name='Doll', age=69)\n\n\tperson1.insertObject(dbConn=dbConn, doCommit=False)\n\n\tperson2.insertObject(dbConn=dbConn, doCommit=False)\n\n\tperson3.insertObject(dbConn=dbConn, doCommit=False)\n\n\tdbConn.commit()\n\nor\n\t\n\tdbConn = getDatabaseConnection(isTransactionMode=True)\n\n\tperson1 = Person.createAndSave(first\\_name='John', last\\_name='Smith', age=34, dbConn=dbConn, doCommit=False)\n\n\tperson2 = Person.createAndSave(first\\_name='Jane', last\\_smith='Doe', age=29, dbConn=dbConn, doCommit=False)\n\n\tperson3 = Person.createAndSave(first\\_name='Bob', last\\_name='Doll', age=69, dbConn=dbConn, doCommit=False)\n\n\tdbConn.commit()\n\n\nFor updates:\n\n\tdbConn = getDatabaseConnection(isTransactionMode=True)\n\n\tpeopleChangingTitle = People.filter(title='Customer Service Rep')\n\n\tfor person in peopleChangingTitle:\n\n\t\tperson.title = 'Customer Care Officer'\n\n\t\tperson.updateObject( ['title'], dbConn=dbConn, doCommit=False)\n\t\n\tdbConn.commit()\n\nThis will rename all people with the title \"Customer Service Rep\" to the new title \"Customer Care Officer\" in one atomic transaction.\n\n\n**Rollback**\n\nYou can trigger a rollback by calling \"rollback\" on the connection method during a transaction\n\n\tdbConn.rollback()\n\nThis is required to be called if the transaction fails (throws an exception) and you want to use this connection again (as this will signify the end of the transaction block)\n\n\n**For use in query builders**\n\n\nEach of the execute\\* methods ( *execute*, *executeGetRows*, *executeGetObjs*, *executeInsert*, *executeUpdate*, *executeDelete*, etc. ) has a \"dbConn\" parameter. Any non-read action also has a \"doCommit.\" These have the same meaning as before, so pass the connection to the operations and call \"commit\" when ready to execute.\n\nThe transaction mode is READ\\_COMMITTED when isTransactionMode=True, so any of the queries will return right away and any writes (update/insert) will execute when \"commit\" is called on the connection object.\n\n\nQuery Builder\n=============\n\nAlthough the ORM and DatabaseModel are very simple and complete, for optimization or complex projects you may prefer to use the query builder.\n\n\nMost query builder classes take one or more DatabaseModel's as parameters. Depending on the methods called, you can use query builder and still get objects returned.\n\n\nWe will start with a basic select query:\n\n**Simple Select Query**\n\nThe simplest query is the Select Query.\n\n\tselQ = SelectQuery(Person, selectFields=['first\\_name', 'age'], orderByField='age', orderByDir='DESC', limitNum=50)\n\n\trows = selQ.executeGetRows()\n\nThis will return a list of tuples, each containing first\\_name followed by age. Each one of these tuples is a returned row. They will be sorted in descending order based on the 'age' field. No more than 50 items will be returned.\n\nDefault is to select all fields, no explicit order by, no explicit order direction, and no limit.\n\n\nYou can also have the Model objects returned with all selected fields filled in.\n\n\tselQ = SelectQuery(Person, orderByField='age', orderByDir='DESC')\n\n\tpeopleObjs = selQ.executeGetObjs()\n\nThis will fetch all fields and return People objects for each one. This would be the same as calling Person.all(), except the results are ordered by age descending.\n\n\n\nWheres\n------\n\nNow it's not very useful to return all objects, we want to be able to filter them.\n\nAll query types have a method, *addStage* which takes 1 argument, \"AND\" or \"OR\" (default \"AND\"). This creates a group in the WHERE clause based on conditions, added via *addCondition.* If \"OR\" is selected, each conditional in this group will be linked with an \"OR\", otherwise \"AND\".\n\n*addCondition* takes a 1. Field name, 2. Field operation, 3. Right-side value\n\nFor example:\n\n\tselQ = SelectQuery(Person)\n\n\tselQWhere1 = selQ.addStage('AND')\n\n\tselQWhere1.addCondition('age', '>', 30)\n\n\tselQWhere1.addCondition('eye\\_color', '=', 'Blue')\n\n\tselQWhere2 = selQ.addStage('OR')\n\n\tselQWhere2.addCondition('age', '<', 35)\n\n\tselQWhere2.addCondition('last\\_name', '=', 'Smith')\n\n\tmatchedPeople = selQ.executeGetObjs()\n\n\nThis will generate a query with two \"groups\" in the WHERE clause. The executed query will look something like this:\n\n\tSELECT \\* FROM person WHERE ( age > 30 AND eye\\_color = 'Blue' ) AND ( age < 35 OR last\\_name = 'Smith' )\n\n\nNotice the top-level stages are joined by an \"AND\". You can get as complicated as you want here!\n\nThe object returned by *addStage* also has an *addStage* method to add sub stages.\n\nSo, for example, if I wanted to filter where (age is > 30 and eye color is 'Blue') OR  ( age < 35 or last\\_name = 'Smith' ):\n\n\tselQ = SelectQuery(Person)\n\n\tselQOuterWhere = selQ.addStage('OR')\n\n\tselQWhere1 = selQOuterWhere.addStage('AND')\n\n\tselQWhere1.addCondition('age', '>', 30)\n\n\tselQWhere1.addCondition('eye\\_color', '=', 'Blue')\n\n\tselQWhere2 = selQOuterWhere.addStage('OR')\n\n\tselQWhere2.addCondition('age', '<', 35)\n\n\tselQWhere2.addCondition('last\\_name', '=', 'Smith')\n\n\tmatchedPeople = selQ.executeGetObjs()\n\n\nso basically creating an \"outer stage\" set to OR and adding substages to that, we now get a query like:\n\n\tSELECT \\* FROM person WHERE ( ( age > 30 AND eye\\_color = 'Blue' ) OR ( age < 35 OR last\\_name = 'Smith' ) )\n\n\nAdvanced Select / Join Multiple Tables\n--------------------------------------\n\n\n**SelectInnerJoinQuery**\n\nThis performs an inner join between multiple tables. This should generally not be used over the more powerful SelectGenericJoinQuery\n\nPass as the first argument a list of Models to use.\n\nFor selectFields, prefix with the table name ( e.x. \"person.age\" )\n\nFor conditionals, do the same. Make sure conditionals perform the joins!\n\n\t\n\tselQ = SelectInnerJoinQuery( [Person, Meal] )\n\n\tselQWhere = selQ.addStage('AND')\n\n\tselQWhere.addCondition('meal.id\\_person', '=', 'person.id')\n\n\t# As dict objs\n\n\tdictObjs = selQ.executeGetDictObjs()\n\n\t# Or as a mapping\n\n\tmapping = selQ.executeGetMapping()\n\n\nThis will generate a query like\n\n\tSELECT person.\\*, meal.\\* FOR person, meal WHERE meal.id\\_person = person.id\n\n\n**SelectGenericJoinQuery**\n\nThis is the prefered method for getting the results of joined tables.\n\nIt takes the primary model ( the FROM ) as the first argument.\n\nFor selectFields, prefix with the table name ( e.x. \"person.age\" )\n\n\n\tselQ = SelectGenericJoinQuery( Person, selectFields=['Person.first\\_name', 'Person.last\\_name', 'Meal.item\\_name'] )\n\n\tselQWhere = selQ.addStage()\n\n\tselQWhere.addCondition('age', '>', 18)\n\n\nJoin on another table by calling *joinModel* passing the model to join, a join type constant (from ichorORM.constants) JOIN\\_\\* (e.x. JOIN\\_INNER, JOIN\\_LEFT, JOIN\\_RIGHT, JOIN\\_OUTER\\_FULL) , and \"AND\" or \"OR\" outer-mode for this stage.\n\nThe stage is returned so you can call .addCondition on it to add conditionals on the join line. Don't forget to join your tables with a condition here!\n\n\n\tjoinWhere = selQ.joinModel( Meal, 'INNER', 'AND' )\n\n\tjoinWhere.addCondition( 'id\\_person', '=', Person.PRIMARY\\_KEY )\n\n\t# As dict objs\n\n\tdictObjs = selQ.executeGetDictObjs()\n\n\t# Or as a mapping\n\n\tmapping = selQ.executeGetMapping()\n\n\nThis will generate a more conventional joined-select query, like so:\n\n\tSELECT Person.first\\_name, Person.last\\_name, Meal.item\\_name FROM Person\n\n\t  INNER JOIN Meal ON ( Meal.id\\_person = Person.id )\n\n\tWHERE\n\n\t  person.age > 18\n\n\n**Results**\n\nIf you call *executeGetDictObjs* you will get a list of DictObjs. This is an object where access is supported either via dot (.field) or sub (['field']). The first level is the table name, the second level is the field names. For example, obj['person']['first_name'] would be the person.first\\_name field.\n\n\nIf you call *executeGetMapping* you will get a list of OrderedDict (in same order specified in selectFields). For example, obj['person.first\\_name'] if you named the field like that in selectFields, or if you just had selectFields=['first\\_name'... ] then it would be obj['first\\_name']\n\n\n\nUpdate Query\n------------\n\n\nUpdate queries use the UpdateQuery object. The stages work the same as in a SelectQuery.\n\nUse the method *setFieldValue* to update the value of a field.\n\n\tupQ = UpdateQuery(Person)\n\n\tupQ.setFieldValue('title', 'Customer Care Expert')\n\n\tupQWhere = upQ.addStage()\n\n\tupQWhere.addCondition('title', '=', 'Customer Service Rep')\n\n\tupQ.executeUpdate()\n\n\n*execute* can also be used as an alias to *executeUpdate*\n\n\nAlso keep in mind that you can pass a getDatabaseConnection(isTransactionMode=True) to executeUpdate and set doCommit=False to link multiple updates or inserts and updates into a single transaction (executed when dbConn.commit() is called)\n\n\nInsert Query\n------------\n\nAn InsertQuery object is used to build queries to perform inserts.\n\n\tinsQ = InsertQuery(Person)\n\n\tinsQ.setFieldValue('first\\_name', 'Tim')\n\n\tinsQ.setFieldValue('age', 22)\n\n\tpersonId = insQ.executeInsert()\n\n*execute* can also be used as an alias to *executeInsert*\n\nThe \"returnPk\" argument (default True) causes the primary key of the Person model to be returned. This is returned immediately, even when within a transaction (read-commit).\n\nAlso keep in mind that you can pass a getDatabaseConnection(isTransactionMode=True) to executeInsert and set doCommit=False to link multiple inserts or inserts and updates into a single transaction (executed when dbConn.commit() is called)\n\n\nDelete Query\n------------\n\nA DeleteQuery object is used to build queries to delete records\n\n\tdelQ = DeleteQuery(Person)\n\n\tdelQWhere = delQ.addStage()\n\n\tdelQWhere.addCondition('age', '<', 18)\n\n\tdelQWhere.executeDelete()\n\n\n*execute* can also be used as an alias to *executeDelete*\n\nKeep in mind you can also delect records in a transaction by passing dbConn and doCommit=False to *execute* or *executeDelete*. Changes will be applied when *commit* is called on that connection.\n\n\nTransactions within Query Builder\n---------------------------------\n\nTo use transactions with query builder statements, get a isTransactionMode=True Database connection like before and pass it to the execute\\* methods along with doCommit=False. When ready, call commit on the connection object.\n\n\n\tdbConn = getDatabaseConnection(isTransactionMode=True)\n\n\tmagicCake = Meal(food\\_group='desert', item\\_name='cake', price='5.99')\n\n\t# Everyone on their birthday will get a magic cake\n\n\tfor person in birthdayPeople:\n\n\t\tmagicCakeCopy = copy.deepcopy(magicCake)\n\n\t\tmagicCakeCopy.id\\_person = person.id\n\n\t\tmagicCakeCopy.insertObject(dbConn=dbConn, doCommit=False)\n\n\t\t# Everyone who eats the magic cake has their eyes turn blue\n\n\t\tupQ = UpdateQuery(Person)\n\n\t\tupQ.setFieldValue('eye\\_color', 'Blue')\n\n\t\tupQ.execue(dbConn=dbConn, doCommit=False)\n\n\t# Execute the transaction, but only commit on success. If fail, exception will be raised.\n\n\ttry:\n\n\t\tdbConn.commit()\n\n\texcept Exception as e:\n\n\t\tprint ( 'Unable to run birthday transaction, rolling back. Error is %s   %s' %(str(type(e)), str(e)))\n\n\nHandling Errors\n---------------\n\nIf a query fails, an exception will be raised (a psycopg2 exception).\n\nFor transactions, you can wrap the \"dbConn.commit\" call in a try/except loop.\n\nFor any immediate commit actions, the *execute\\* * method or the *insertObject/updateObject/createAndSave* method will raise an exception upon error.\n\n\nUsing explicit SQL / Special Values\n===================================\n\n\nNULL\n----\n\nThere are a few ways to represent a value of \"NULL.\" The most common is that ichorORM associates NULL with None.\n\nNone will be used when fields have a NULL value, and can be used with filtering as a replacement for NULL.\n\nYou may also find it useful sometimes to use \"ichorORM.constants.SQL\\_NULL\".\n\nIf you do a query like:\n\n\tmyObjs = MyModel.filter(some\\_field=None)\n\nichorORM will automatically convert the \"equals\" operator to the \"is\" operator; that is to say you would have a query like:\n\n\tSELECT \\* FROM MyModel WHERE some\\_field IS NULL\n\nwhich will return results as expected, versus:\n\n\tSELECT \\* FROM MyModel WHERE some\\_field = NULL\n\nwhich would return NOTHING (postgres is pedantic that you must use \"is\" and \"is not\" with NULL)\n\n\n\nExplicit Query Strings\n----------------------\n\nWhile the given ORM functions should cover 98% of use cases, sometimes you want to use a call to an explicit sql function or otherwise.\n\nYou can use the same functions such as \"setFieldValue\" but instead of interpreting the value as a string (thus quoting / escaping) you can provide explicit SQL by wrapping the value in a *QueryStr* type.\n\n\nExample:\n\n\timport datetime\n\n\tfrom ichorORM.query import QueryStr, SelectQuery, UpdateQuery\n\n\ttoday = datetime.date.today()\n\n\t# Find all the \"Person\" objects\n\n\tbirthdayQ = SelectQuery(Person, selectFields=['id'])\n\n\t# Filter on all folks who had birthday today.\n\n\t#   Note: we can directly pass a datetime.date/datetime.datetime object for TIMESTAMP fields\n\n\tbirthdayQWhere = birthdayQ.addStage()\n\n\tbirthdayQWhere.addCondition('birthday', '>=', today)\n\n\tbirthdayQWhere.addCondition('birthday', '<', today + datetime.timedelta(days=1))\n\n\t# Execute the query and return rows\n\n\tbirthdayQRows = birthdayQ.executeGetRows()\n\n\t# Rows are returned with columns matching #selectFields in SelectQuery.\n\n\t#    In this case, we are only selecting 'id' and thus take first col in every row\n\n\tbirthdayIds = [ row[0] for row in birthdayQRows ]\n\n\tupQ = UpdateQuery(Person)\n\n\t# Increment age + 1 for people whose birthday is today\n\n\t#  NOTE: WE USE QueryStr HERE FOR EXPLICIT \"age + 1\" rather than a string holding value 'age + 1'\n\n\tupQ.setFieldValue('age', QueryStr('age + 1'))\n\n\t# Qualify the WHERE to be the birthday ids\n\n\tupQWhere = upQ.addStage()\n\n\tupQWhere.addCondition('id', 'in', birthdayIds)\n\n\ttry:\n\n\t\tupQ.execute()\n\n\texcept Exception as e:\n\n\t\tprint ( \"Failed to increment age of birthday people. Error is %s  %s\" %(str(type(e)), str(e)) )\n\n\nThis example would execute a select to gather ids, and then perform an update like this:\n\n\tUPDATE Person SET age = age + 1 WHERE id in ( ...list\\_of\\_ids... )\n\n\nThis is also a drawn-out example for documentation/tutorial purposes. It executes a SELECT query, returns the ids back to the client, which then issues an UPDATE query using those ids. This can all be simplified by just using the SELECT conditionals within the Update query itself:\n\n\tupQ = UpdateQuery(Person)\n\n\tupQ.setFieldValue('age', QueryStr('age + 1'))\n\n\tupQWhere = upQ.addStage()\n\n\tupQWhere.addCondition('birthday', '>=', today)\n\n\tupQWhere.addCondition('birthday', '<', today + datetime.timedelta(days=1))\n\n\ttry:\n\n\t\tupQ.execute()\n\n\texcept Exception as e:\n\n\t\tprint ( \"Failed to increment age of birthday people. Error is %s  %s\" %(str(type(e)), str(e)) )\n\n\nOr the entire condition value can be a QueryStr:\n\n\tupQWhere.addCondition('birthday', 'BETWEEN', QueryStr(\"\"\"date\\_trunc('day', CURRENT\\_TIMESTAMP) AND (day\\_trunc('day', CURRENT\\_TIMESTAMP) + INTERVAL '23:59:59')))\n\n\nOr a tuple of two items (range start, range end), either as \"date-like\" objects or QueryStr, or a mix thereof\n\n\tupQWhere.addCondition('birthday', 'BETWEEN', [ QueryStr(\"\"\"date\\_trunc('day', CURRENT\\_TIMESTAMP)\"\"\"), today + datetime.timedelta(days=1)] )\n\n\nEmbedded Queries\n----------------\n\nYou may embed subqueries directly using the QueryStr object, or you can embed a SelectQuery you have created.\n\n\n\t# Create the UpdateQuery object on a Person\n\n\tupQ = UpdateQuery(Person)\n\n\t# Increment age + 1 for people whose ate birthday cake today\n\n\tupQ.setFieldValue('age', QueryStr('age + 1'))\n\n\t# Create a subqery that will select the id\\_person from a Meal object with matching critera\n\n\tmealIdSelQ = SelectQuery(Meal, selectFields=['id\\_person'])\n\n\tmealIdSelQWhere = mealIdSelQ.addStage()\n\n\tmealIdSelQWhere.addCondition('food\\_name', 'LIKE', '%Cake')\n\n\tmealIdSelQWhere.addCondition('consumed\\_date', datetime.date.today() )\n\n\t# Create the WHERE query to embed\n\n\tupQWhere = upQ.addStage()\n\n\t# select id from the subquery\n\n\tupQWhere.addCondition('id', 'in', mealIdSelQ)\n\t\n\ttry:\n\n\t\tupQ.execute()\n\n\texcept Exception as e:\n\n\t\tprint ( \"Failed to increment age of birthday people. Error is %s  %s\" %(str(type(e)), str(e)) )\n\n\nThis will generate a query like:\n\n\tUPDATE Person SET age = age + 1 WHERE id in (SELECT id\\_person FROM Meal WHERE food\\_name LIKE '%Cake' AND consumed\\_date = '2018\\-07\\-04'::date )\n\n\nAggregate Functions\n-------------------\n\nYou may wish to select some aggregate data from one of your models, such as the average of some field or the sum of another.\n\nTo accomplish this, use a QueryStr containing the aggregate expression within selectFields.\n\nFor example,\n\n\t# Build a SelectQuery with our aggregate functions specified within selectFields\n\n\tselQ = SelectQuery(MyPersonModel, selectFields=[ QueryStr('AVG(age)'), QueryStr('MAX(age)'), QueryStr('MAX(birth\\_day)'), QueryStr('MIN(birth\\_month)'), QueryStr('MIN(age)') ])\n\n\t# We need to use executeGetRows here, not executeGetObjs, as the models do not have fields for the aggregates.\n\n\tresults = selQ.executeGetRows()\n\n\t# Because every entry in #selectFields is an aggregate function,\n\n\t#   we will get one row back containing the results in the same\n\n\t#   order as #selectFields specified\n\n\t(gotAvgAge, gotMaxAge, gotMaxBirthDay, gotMinBirthMonth, gotMinAge) = results[0]\n\n\nAdditional Libraries\n--------------------\n\nYou may find it useful to combine ichorORM with QueryableList ( https://github.com/kata198/QueryableList )\n\nThis will allow you to further refine query results client-side (after data has been received from the postgresql database) ORM-style.\n\nFor example:\n\n\timport QueryableList\n\n\tadultPeople = People.filter(age\\_\\_gte=18)\n\n\tadultPeople = QueryableList.QueryableListObjs(adultPeople)\n\n\tadultTims = adultPeople.filter(first\\_name='Tim')\n\nIn this simple example, we query the database for any People that have an age >= 18. Then, client-side using QueryableList, we further refine the results to filter out only those whose first name is 'Tim'",
        "description_content_type": "",
        "docs_url": null,
        "download_url": "",
        "downloads": {
            "last_day": -1,
            "last_month": -1,
            "last_week": -1
        },
        "home_page": "https://github.com/kata198/ichorORM",
        "keywords": "python,orm,postgres,postgresql,query,build,select,update,delete,model,psycopg,psycopg2",
        "license": "LGPLv2",
        "maintainer": "Tim Savannah",
        "maintainer_email": "kata198@gmail.com",
        "name": "ichorORM",
        "package_url": "https://pypi.org/project/ichorORM/",
        "platform": "",
        "project_url": "https://pypi.org/project/ichorORM/",
        "project_urls": {
            "Homepage": "https://github.com/kata198/ichorORM"
        },
        "release_url": "https://pypi.org/project/ichorORM/2.0.2/",
        "requires_dist": null,
        "requires_python": "",
        "summary": "A python library for postgresql focused on performance and supporting ORM and query-building functionality",
        "version": "2.0.2"
    },
    "last_serial": 4041997,
    "releases": {
        "1.0.2": [
            {
                "comment_text": "",
                "digests": {
                    "md5": "8e60ce92e39a9713591c3d6044a11555",
                    "sha256": "aec10b7282e6a3e315633477a1a39b4859f789a941f5a774787ea41b041c3359"
                },
                "downloads": -1,
                "filename": "ichorORM-1.0.2.tar.gz",
                "has_sig": false,
                "md5_digest": "8e60ce92e39a9713591c3d6044a11555",
                "packagetype": "sdist",
                "python_version": "source",
                "requires_python": null,
                "size": 54720,
                "upload_time": "2018-05-31T21:27:41",
                "url": "https://files.pythonhosted.org/packages/97/1c/7a0ae0654bb2f3b92ed756eca62fad35fbf7a4f7b0246e346d74897ef4dd/ichorORM-1.0.2.tar.gz"
            }
        ],
        "2.0.0": [
            {
                "comment_text": "",
                "digests": {
                    "md5": "a3c3e80139ff346be415182b00a59fd9",
                    "sha256": "8c507fc14638ed46f9686501a8a19486222b40c74b2bf1a7b9e8d7bc37259e4e"
                },
                "downloads": -1,
                "filename": "ichorORM-2.0.0.tar.gz",
                "has_sig": false,
                "md5_digest": "a3c3e80139ff346be415182b00a59fd9",
                "packagetype": "sdist",
                "python_version": "source",
                "requires_python": null,
                "size": 131257,
                "upload_time": "2018-07-05T17:47:38",
                "url": "https://files.pythonhosted.org/packages/7b/1a/3e02d94098e7dc010747904dfc052a351a155b1fcc47f35049ac0d099866/ichorORM-2.0.0.tar.gz"
            }
        ],
        "2.0.1": [
            {
                "comment_text": "",
                "digests": {
                    "md5": "fc3106c8795c7a1e1f87d6a6f9a2a066",
                    "sha256": "71fa62d14d1845d7265cdaa2af2d1d83ff774f3e32cbe29f09ddb649ae725533"
                },
                "downloads": -1,
                "filename": "ichorORM-2.0.1.tar.gz",
                "has_sig": false,
                "md5_digest": "fc3106c8795c7a1e1f87d6a6f9a2a066",
                "packagetype": "sdist",
                "python_version": "source",
                "requires_python": null,
                "size": 131591,
                "upload_time": "2018-07-07T21:15:36",
                "url": "https://files.pythonhosted.org/packages/1a/02/2d454cb1d166dee911ec9173b8a53f3ab1a677b1bb89ea6dd81a3fd5579e/ichorORM-2.0.1.tar.gz"
            }
        ],
        "2.0.2": [
            {
                "comment_text": "",
                "digests": {
                    "md5": "5bf124302904d9720bff66f683ce084c",
                    "sha256": "5d039da42da41c70a7625612eb136758deb768469bfdb312d335714a45dd0afd"
                },
                "downloads": -1,
                "filename": "ichorORM-2.0.2.tar.gz",
                "has_sig": false,
                "md5_digest": "5bf124302904d9720bff66f683ce084c",
                "packagetype": "sdist",
                "python_version": "source",
                "requires_python": null,
                "size": 137552,
                "upload_time": "2018-07-09T01:13:42",
                "url": "https://files.pythonhosted.org/packages/23/0b/47c42d66e3e8b46e9e4b94a3e0b1877bea197e66a959ff8069e26baa3474/ichorORM-2.0.2.tar.gz"
            }
        ]
    },
    "urls": [
        {
            "comment_text": "",
            "digests": {
                "md5": "5bf124302904d9720bff66f683ce084c",
                "sha256": "5d039da42da41c70a7625612eb136758deb768469bfdb312d335714a45dd0afd"
            },
            "downloads": -1,
            "filename": "ichorORM-2.0.2.tar.gz",
            "has_sig": false,
            "md5_digest": "5bf124302904d9720bff66f683ce084c",
            "packagetype": "sdist",
            "python_version": "source",
            "requires_python": null,
            "size": 137552,
            "upload_time": "2018-07-09T01:13:42",
            "url": "https://files.pythonhosted.org/packages/23/0b/47c42d66e3e8b46e9e4b94a3e0b1877bea197e66a959ff8069e26baa3474/ichorORM-2.0.2.tar.gz"
        }
    ]
}