Existing tables

[aklaver]

How does one go about getting Camelot to work with existing tables in
a database?

[Adrian Klaver]

On 02/21/2013 03:50 PM, aklaver wrote:
> How does one go about getting Camelot to work with existing tables in
> a database?
>

I have been working on this and got as far as:

class CellPer(Entity):


__tablename__ = "cell_per"
__table_args__ = (
PrimaryKeyConstraint('line_id'),
{'autoload':True, 'schema': 'public'}
)

def __unicode__(self):
return self.category

class Admin(EntityAdmin):
form_size = (400, 200)
list_display = ["category", "cell_per"]

Nothing displays because of this error, from Postgres logs:

postgres-2013-02-22 07:26:47.530 PST-0ERROR: column cell_per.id does
not exist at character 654

which comes from this part SQL statement sent from Camelot:


... public.cell_per.id AS public_cell_per_id

This would seem to be similar to bug reported here:

https://groups.google.com/group/project-camelot/browse_thread/thread/e55d6a38ff80e2dd/505b06d6afcee505?lnk=gst&q=autoload#505b06d6afcee505


--
Adrian Klaver
adrian...@gmail.com

[Adrian Klaver]

On 02/21/2013 03:50 PM, aklaver wrote:

> How does one go about getting Camelot to work with existing tables in
> a database?
>

Forgot to mention I am using Camelot 12.06.29.

--
Adrian Klaver
adrian...@gmail.com

[Erik Janssens]

Hi,

I made a code snippet on using an existing database. I'll try
to add it to the documentation later on.

This has been tested with the current master from git.

Cheers,

Erik

[Adrian Klaver]

On 02/22/2013 10:17 AM, Erik Janssens wrote:
> Hi,
>
> I made a code snippet on using an existing database. I'll try
> to add it to the documentation later on.
>
> This has been tested with the current master from git.

I can confirm it works with 12.06.29 also.
Thanks for the code.

>
> Cheers,
>
> Erik



--
Adrian Klaver
adrian...@gmail.com

[Adrian Klaver]

On 02/22/2013 10:17 AM, Erik Janssens wrote:

> Hi,
>
> I made a code snippet on using an existing database. I'll try
> to add it to the documentation later on.
>
> This has been tested with the current master from git.

I spoke to soon. When I use the example on my own table I get an item
list but for the most part the fields are grayed out and are read only.
I pulled from the GitHub repo and see the same behavior. Any clues?

[Erik Janssens]

if they are read-only, that's propably because they are not
recognized as 'SQLAlchemy' properties and just treated as
normal Python properties, which are read only by default

You could maket them editable through the field attributes,
but even then probably the type is not going to be reflected,
etc.

Are you sure you're using the EntityAdmin and not the ObjectAdmin
to specify the Admin interface ?

You might have to look in EntityAdmin.get_field_attributes to
see exactly what happens why the properties are not
recognized

[Adrian Klaver]

On 02/23/2013 01:15 AM, Erik Janssens wrote:
>
> if they are read-only, that's propably because they are not
> recognized as 'SQLAlchemy' properties and just treated as
> normal Python properties, which are read only by default
>
> You could maket them editable through the field attributes,
> but even then probably the type is not going to be reflected,
> etc.

Well that worked.

>
> Are you sure you're using the EntityAdmin and not the ObjectAdmin
> to specify the Admin interface ?

I am using EntityAdmin.

>
> You might have to look in EntityAdmin.get_field_attributes to
> see exactly what happens why the properties are not
> recognized

How would I go about doing that?





--
Adrian Klaver
adrian...@gmail.com

[Adrian Klaver]

On 02/23/2013 01:15 AM, Erik Janssens wrote:
>

> if they are read-only, that's propably because they are not
> recognized as 'SQLAlchemy' properties and just treated as
> normal Python properties, which are read only by default
>
> You could maket them editable through the field attributes,
> but even then probably the type is not going to be reflected,
> etc.
>
> Are you sure you're using the EntityAdmin and not the ObjectAdmin
> to specify the Admin interface ?
>
> You might have to look in EntityAdmin.get_field_attributes to
> see exactly what happens why the properties are not
> recognized

Alright I used SQLAlchemy directly and got the following when I
reflected the table:

insp.get_columns("cell_per")

[{'autoincrement': False,
'default': None,
'name': u'line_id',
'nullable': False,
'type': INTEGER()},
{'autoincrement': False,
'default': None,
'name': u'category',
'nullable': False,
'type': VARCHAR(length=10)},
{'autoincrement': False,
'default': None,
'name': u'cell_per',
'nullable': False,
'type': INTEGER()},
{'autoincrement': False,
'default': None,
'name': u'type',
'nullable': True,
'type': VARCHAR(length=6)},
{'autoincrement': False,
'default': u'now()',
'name': u'ts_insert',
'nullable': True,
'type': TIMESTAMP(precision=0)},
.....]

The INTEGER fields where the only ones I could edit because they map to
a type understood by Camelot. The VARCHAR fields are not mapped
correctly. Seems by default SQLAlchemy will take a UNICODE() type and
map it to the appropriate database dialect type, but not the other way
around. Seems there might be a way to override this:

http://docs.sqlalchemy.org/en/rel_0_7/orm/examples.html#declarative-reflection

Seems I am going to learn more about SQLAlchemy:)



--
Adrian Klaver
adrian...@gmail.com

[Adrian Klaver]

On 02/23/2013 01:15 AM, Erik Janssens wrote:
>

> if they are read-only, that's propably because they are not
> recognized as 'SQLAlchemy' properties and just treated as
> normal Python properties, which are read only by default
>
> You could maket them editable through the field attributes,
> but even then probably the type is not going to be reflected,
> etc.
>
> Are you sure you're using the EntityAdmin and not the ObjectAdmin
> to specify the Admin interface ?
>
> You might have to look in EntityAdmin.get_field_attributes to
> see exactly what happens why the properties are not
> recognized

Actually it turned out to be fairly simple. Just need to give type hints:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.types import Unicode

Base = declarative_base(metadata=metadata)

class CellPer(Base):
__table__ = Table("cell_per", Base.metadata,
Column("type", Unicode),
Column("category", Unicode),
autoload=True, autoload_with=engine,
)

class Admin(EntityAdmin):
list_display = ["category", "type", "cell_per"]


--
Adrian Klaver
adrian...@gmail.com

[Erik Janssens]

Ok, now I see what's happening

Camelot only contains default field attributes that make sense
for the basic SQLA types, not for all types of individual databases.

I just modified the git master (in gitorious), to try to find the
'closest' match, this should work with reflected databases.

The code that does the resolution is in

EntityAdmin.get_sql_field_attributes

Please let me know how that goes

[Adrian Klaver]

On 02/24/2013 06:50 AM, Erik Janssens wrote:
> Ok, now I see what's happening
>
> Camelot only contains default field attributes that make sense
> for the basic SQLA types, not for all types of individual databases.
>
> I just modified the git master (in gitorious), to try to find the
> 'closest' match, this should work with reflected databases.

About Gitorious. I tried to clone the repo but it failed with private
key error, so I figured I needed to sign up with Gitorous. I tried that
yesterday and again today, still waiting for the confirmation email that
will allow me to login. I have cloned from the GitHub repo it tracks the
Gitorious repo closely enough for my needs.

>
> The code that does the resolution is in
>
> EntityAdmin.get_sql_field_attributes

So how would I go about getting to that point?

>
> Please let me know how that goes
>
>

>

Thanks,
--
Adrian Klaver
adrian...@gmail.com

[Erik Janssens]

In the ideal case nothing should be done, apart from using
the master version. It should work out of the box.

If that is not the case, please place some debug or print statements
in camelot/admin/entity_admin.py in EntityAdmin.get_sql_field_attributes
to see what comes in and what goes out.

(as for gitorious, I believe you can also use http to do
checkouts, then no pk is needed)

[Adrian Klaver]

On 02/24/2013 06:50 AM, Erik Janssens wrote:

> Ok, now I see what's happening
>
> Camelot only contains default field attributes that make sense
> for the basic SQLA types, not for all types of individual databases.
>
> I just modified the git master (in gitorious), to try to find the
> 'closest' match, this should work with reflected databases.

Well the confirmation email showed up. At any rate I found that to clone
directly from Gitorious the command should be:

git clone https://git.gitorious.org/camelot/camelot.git

not the command listed on the Get it page:

git clone g...@gitorious.org:camelot/camelot.git


--
Adrian Klaver
adrian...@gmail.com

[Adrian Klaver]

On 02/24/2013 11:12 AM, Erik Janssens wrote:
> In the ideal case nothing should be done, apart from using
> the master version. It should work out of the box.

Yes, it worked. Thanks.

>
> (as for gitorious, I believe you can also use http to do
> checkouts, then no pk is needed)

So I found out:) Might be good idea to change that on the Get it page
though.


--
Adrian Klaver
adrian...@gmail.com

[Erik Janssens]

Hello Wim,

I'm not sure how the reflection of relationships works in SQLA,
if the relationship reflection works, Camelot should handle it fine,
and should give you the selector box.

Can you try using the SQLA 'relationship' property instead of
ManyToOne ?  If that doesn't work, try to define the foreign  key
and the relationship explicitly to see if that works ?

Cheers,

Erik

On Sat, May 18, 2013 at 6:13 PM, Wim Van Damme <wim.van...@gmail.com> wrote:

Is it possibly to reflect foreign key relationships in this way? I succeeded at getting simple tables reflected, but I'm not sure how to tackle it for tables with relationships. As a dummy example say you have a table country and a table city with city having a foreign key from a certain country. Is it possible to reflect this relationship? Currently I'm not able to enter a new city without running into some errors for the foreign key. I would like to make it so you have a kind of selector box when you create a new city form so that you could choose the country or create a new one. Any idea how to do this?

Example of non-working code:

class Country( Base ):
    __table__ = Table( 'country', Base.metadata, 
                       autoload=True )
   
    class Admin( EntityAdmin ):
        list_display = ['country_id',
                        'country',
                        'last_update']

class City( Base ):
    __table__ = Table( 'city', Base.metadata, 
                       autoload=True )
   
    country = ManyToOne('Country')
   
    class Admin( EntityAdmin ):
        list_display = ['city_id',
                        'city',
                        ]
        form_display = TabForm([
          ('City', Form([
            'city',
            'country',
            ], columns = 2)),
         
        ])

Thanks.

Wim

Op zondag 24 februari 2013 20:28:00 UTC+1 schreef aklaver het volgende:

[Adrian Klaver]

Sorry, this email got lost in the inbox. To get SQlA FK relationships to
work I did this, where projection has a FK back to plant:

from sqlalchemy.orm import relationship

class Plant(Base):

__table__= Table("plant1", Base.metadata,
Column("category_type", Unicode, FetchedValue()),
Column("category_sub_type", Unicode,
FetchedValue()),
schema="public",
autoload=True, autoload_with=engine)

proj = relationship("Projection", backref="plant",
order_by="desc('year'), Projection.c_id")

class Projection(Base):

__table__ = Table("projection", Base.metadata, schema="public",
autoload=True, autoload_with=engine)


*NOTE* I am using Postgres and the schema declaration is important.
Without I got strange FK errors. See here for the details of why that is:

http://docs.sqlalchemy.org/en/rel_0_8/dialects/postgresql.html#remote-cross-schema-table-introspection

--
Adrian Klaver
adrian...@gmail.com