[PonyORM-list] Couple of questions

[Goldberg, Arthur P]

drop_table() does not work for me. Using MySQL, starting with an empty dbms.
#!/usr/bin/env python
from pony.orm import *
db = Database()

class Variant(db.Entity):
    pos = Required(str)
    SubjectID = Required("Subject")

class Subject(db.Entity):
    FamilyID = Optional(str)
    IndivID = PrimaryKey(str)
    Sex = Required(str)
    variants = Set(Variant)

# dict with params here
db.bind('mysql', params["host"], params["user"], params["passwd"], params["db"] )

db.generate_mapping( create_tables=True )

def dropTable( tableName ):
    db.drop_table( tableName, if_exists=True, with_all_data=True )

@db_session
def inserts():
    s1 = Subject(    IndivID = 'sub1',     Sex = '1',)
    s2 = Subject(    IndivID = 'sub2',    Sex = '2',)

def main():
    inserts()
    dropTable('Subject')
    inserts()

main()

generates:
Traceback (most recent call last):
  File "/hpc/users/goldba06/repos/asc_datamanagementandprocessing/dbms_pony/t.py", line 34, in <module>
    main()
  File "/hpc/users/goldba06/repos/asc_datamanagementandprocessing/dbms_pony/t.py", line 32, in main
    inserts()
  File "<auto generated wrapper of inserts() function>", line 2, in inserts
  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 941, in commit
    if cache.modified: cache.flush()
  File "/hpc/users/goldba06/.local/lib/python2.7/site-packages/pony/orm/core.py", line 1006, in flush
    if obj is not None: obj._save_()
  File "/hpc/users/goldba06/.local/lib/python2.7/site-packages/pony/orm/core.py", line 3938, in _save_
    if status == 'created': obj._save_created_()
  File "/hpc/users/goldba06/.local/lib/python2.7/site-packages/pony/orm/core.py", line 3838, in _save_created_
    % (obj, e.__class__.__name__, msg), e)
  File "/hpc/users/goldba06/.local/lib/python2.7/site-packages/pony/utils.py", line 124, in throw
    raise exc
pony.orm.core.CommitException: Object Subject['sub1'] cannot be stored in the database. IntegrityError: 1062 Duplicate entry 'sub1' for key 'PRIMARY'

which is what we expect if the table isn't dropped.
What I want to do is have these modules:
utilities: connect to dbms, and optionally drop a table
entities: the entities
load file: load a file into a table
query: some queries
load file, and query optionally use utilities


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/48671c0a/attachment-0001.html>