[PonyORM-list] Couple of questions

[Goldberg, Arthur P]

Also, I am unalbe to drop a table with execute():
params = { 'host':'db', 'user':'', 'passwd':'', 'db':''} # data removed

from pony.orm import *
from geneticsClasses import db

@db_session
def dropTable( tableName ):
    sql = """SET foreign_key_checks = 0; DROP TABLE IF EXISTS """ + tableName + """; SET foreign_key_checks = 1;"""
    db.execute( sql )
    print 'dropped ', tableName

def Connect( tableName, dropAndCreateTable=False ):
    db.bind('mysql', params["host"], params["user"], params["passwd"], params["db"] )

    db.generate_mapping( create_tables=dropAndCreateTable )

    if dropAndCreateTable:
        dropTable( tableName )
generates:
Exception _mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now") in <bound method Cursor.__del__ of <MySQLdb.cursors.Cursor object at 0x1acab50>> ignored
Traceback (most recent call last):
  File "/hpc/users/goldba06/repos/asc_datamanagementandprocessing/dbms_pony/insertFileIntoTable.py", line 150, in <module>
SET foreign_key_checks = 0; DROP TABLE IF EXISTS Subject; SET foreign_key_checks = 1;
dropped  Subject
    Connect( MyLoadFile.args.className, dropAndCreateTable=MyLoadFile.args.dropAndCreateTable )
  File "/hpc/users/goldba06/repos/asc_datamanagementandprocessing/dbms_pony/geneticsDBMScommon.py", line 31, in Connect
    dropTable( tableName )
  File "<auto generated wrapper of dropTable() function>", line 2, in dropTable
  File "/hpc/users/goldba06/.local/lib/python2.7/site-packages/pony/orm/core.py", line 354, in new_func
    finally: self.__exit__(exc_type, exc_value, exc_tb)
  File "/hpc/users/goldba06/.local/lib/python2.7/site-packages/pony/orm/core.py", line 386, in __exit__
    commit()
  File "<auto generated wrapper of commit() function>", line 2, in commit
  File "/hpc/users/goldba06/.local/lib/python2.7/site-packages/pony/utils.py", line 97, in cut_traceback
    return func(*args, **kwargs)
  File "/hpc/users/goldba06/.local/lib/python2.7/site-packages/pony/orm/core.py", line 281, in commit
    reraise(CommitException, exceptions)
  File "/hpc/users/goldba06/.local/lib/python2.7/site-packages/pony/orm/core.py", line 275, in commit
    try: primary_cache.commit()
  File "/hpc/users/goldba06/.local/lib/python2.7/site-packages/pony/orm/core.py", line 949, in commit
    cache.rollback()
  File "/hpc/users/goldba06/.local/lib/python2.7/site-packages/pony/orm/core.py", line 960, in rollback
    try: provider.rollback(connection)
  File "<auto generated wrapper of rollback() function>", line 2, in rollback
  File "/hpc/users/goldba06/.local/lib/python2.7/site-packages/pony/orm/dbapiprovider.py", line 45, in wrap_dbapi_exceptions
    except dbapi_module.ProgrammingError, e: raise ProgrammingError(e)
pony.orm.core.CommitException: ProgrammingError: 2014 Commands out of sync; you can't run this command now


On Jun 19, 2014, at 10:24 AM, Alexey Malashkevich <alexeymalashkevich at gmail.com<mailto:alexeymalashkevich at gmail.com>> wrote:

Hi Arthur,

1. The method which you've used - db.get_connection().executescript() is specific for SQLite only.
You need to use the method db.execute() which works for all databases: http://doc.ponyorm.com/database.html#execute
Also you can use methods for dropping the database tables:
http://doc.ponyorm.com/database.html#drop_table
http://doc.ponyorm.com/database.html#drop_all_tables

2. The official way to get all entity attributes is getting the _attrs_ attribute from the entity class:

    MyEntity._attrs_

It returns the list of all the entity attributes. You can iterate over this list and examine the attribute properties. For example, this is how you can print all attribute names which are not collections (collection means one-to-many relationship attribute):

    for attr in MyEntity._attrs_:
        if not attr.is<http://attr.is>_collection:
            print attr.name<http://attr.name/>

Below you can see a couple of functions for getting attributes:

def get_all_attrs(entity_cls):
    return [ attr for attr in entity_cls._attrs_ ]

def get_attrs_with_columns(entity_cls):
    return [ attr for attr in entity_cls._attrs_with_columns_ ]

def get_collection_attrs(entity_cls):
    return [ attr for attr in entity_cls._attrs_ if attr.is<http://attr.is>_collection ]

def get_all_attr_names(entity_cls):
    return [ attr.name<http://attr.name/> for attr in entity_cls._attrs_ ]

Please let us know if you have further questions.

Regards,
Alexey


On Thu, Jun 19, 2014 at 3:13 AM, Goldberg, Arthur P <arthur.p.goldberg at mssm.edu> wrote:
Pony's nifty -- thanks.

Some questions:

1. Direct SQL. Suppose I want to drop a table. I thought that this would work because executescript() appears in examples
        sql = "DROP TABLE IF EXISTS %s;" % tableName
        db.get_connection().executescript(sql)

2. Entity class fields. Suppose I have

class Subject(db.Entity):
    FamilyID = Optional(str)
    IndivID = PrimaryKey(str)
    PatID = Optional(str, default='0')
    MatID = Optional(str, default='0')
    Sex = Required(str)
    Pheno = Required(int)
    SampleSet = Required(str)
    variants = Set(Variant)
className = 'Subject'
self.slqobj = globals()[ className ]
# I can get all fields in Subject with
self.slqobj._attrs_

but is there an official method?

Thanks
Arthur

---

Arthur Goldberg
Associate Professor of Psychiatry
Seaver Autism Center and Icahn Institute for Genomics & Multiscale Biology
Icahn School of Medicine at Mount Sinai
Seaver Center, Room ABE-33
212-241-4229<tel:212-241-4229>
Arthur.Goldberg at mssm.edu
Follow us on Twitter @IcahnInstitute<https://twitter.com/IcahnInstitute>





_______________________________________________
ponyorm-list mailing list
ponyorm-list at ponyorm.com
/ponyorm-list


_______________________________________________
ponyorm-list mailing list
ponyorm-list at ponyorm.com<mailto:ponyorm-list at ponyorm.com>
/ponyorm-list

---

Arthur Goldberg
Associate Professor of Psychiatry
Seaver Autism Center and Icahn Institute for Genomics & Multiscale Biology
Icahn School of Medicine at Mount Sinai
Seaver Center, Room ABE-33
212-241-4229
Arthur.Goldberg at mssm.edu<mailto:Arthur.Goldberg at mssm.edu>
Follow us on Twitter @IcahnInstitute<https://twitter.com/IcahnInstitute>




-------------- next part --------------
An HTML attachment was scrubbed...
URL: </ponyorm-list/attachments/20140619/4cb27382/attachment-0001.html>