[PonyORM-list] Couple of questions

Goldberg, Arthur P arthur.p.goldberg at mssm.edu
Sat Jun 21 19:57:15 UTC 2014
Previous message: [PonyORM-list] Couple of questions
Next message: [PonyORM-list] Couple of questions
Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Thanks Alexey

entityName.drop_table(with_all_data=True)

works. Also, I changed all entity names to lower case, which might avoid future problems.

A

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

This happens because the table name which is generated from the entity name is not always equal to the entity name. In MySQL it is recommended to convert the names of the tables to the lower case and Pony follows this recommendation (see more on this here: http://blog.ponyorm.com/?p=148)
and due to this the table name for the Subject entity is converted to "subject".

When you call db.drop_table("Subject", if_exists=True) in MySQL on Linux, this command cannot find the table "Subject", because the name of the table is "subject" and MySQL is case sensitive on Linux.

In order to delete the table associated with the entity you should use the method drop_table of an entity:

    Subject.drop_table(with_all_data=True)

Another option is to pass the entity class to the method db.drop_table:

   db.drop_table(Subject, with_all_data=True)

In this case Pony will get the right name of the table.

Thanks for the question, I've updated the Pony documentation to cover this topic http://doc.ponyorm.com/database.html#drop_table

Regards,
Alexey


On Thu, Jun 19, 2014 at 11:27 PM, Goldberg, Arthur P <arthur.p.goldberg at mssm.edu<mailto:arthur.p.goldberg at mssm.edu>> wrote:
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>





_______________________________________________
ponyorm-list mailing list
ponyorm-list at ponyorm.com<mailto: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/20140621/a92efeab/attachment-0001.html>
Previous message: [PonyORM-list] Couple of questions
Next message: [PonyORM-list] Couple of questions
Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
More information about the ponyorm-list mailing list