Attempting to import existing sqlite3 database w/o primary key

[Cord Thomas]

Just discovered this project and it looks very promising. I am planning to use it for a lightweight community project to provide a way for people to better understand how our City Council operates. 

Problem:  I have a table that has a composite primary key.  I undersatnd that Jam.py only supports single-field keys.

So, in my import, I added an INTEGER field called ID.

When I imported the table it was empty.

I read in the Integration with Existing Database article that i had to specify a generator "Before saving, specify the primary key field for the item and generator name, if necessary." but for the life of me I can't figure out what a generator is.  I assume it would create some auto_increment function but i can't figure that out.

Thanks in advance

[Dean D. Babic]

Hi,

the generator means different stuff for different DB providers, ie:
https://docs.oracle.com/cd/E11035_01/workshop102/ejb/entity/conAutomaticPrimaryKeyGeneration.html

The generator option does not even appear for SQLite3 on Import table. I will update the Docs in due time.

Jam.py does create auto increment function (or a generator for ie Oracle), only when the table is created. 

Not after, because changing the Primary Key is not normally supported for any DB provider. 

So whatever is selected as a Primary Key (even a TXT type), will be used on table creation.

Hence, the Primary Key is needed before the Table Import.

If an INTEGER field was just created, it means it is empty. 

It is not populated with anything.

So your table is empty, and this is normal.

You need to populate it, OR, better, redesign your table to use a single PK.

See this vid where a complete app was created from SQLite3 file:

https://www.youtube.com/watch?v=twoKjU-HV1I

This vid is important since touches on reserved words and attachments as well. 

The PK is quite common issue with ie MS Access as well:
https://jampy-application-design-tips.readthedocs.io/en/latest/migration/access.html#id1

Id u keen, send me a sqlite3 schema and I'll have a look.
There might be heaps of Foreign Keys as well.

Now, the mother of all questions, how did you find Jam.py? Googling?

[Cord Thomas]

Thank you for the response Dean.

I will get back to the substance of your response, but to answer your question about how I found jam.py, yes, by googling for some combination of "lightweight web framework for managing a sqlite database" and "automated forms generation for working with a small database". 

--
You received this message because you are subscribed to the Google Groups "Jam.py Users Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jam-py+un...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/jam-py/6bf2d02b-7d3e-4a82-b1eb-a1cf16bf509an%40googlegroups.com.

[Cord Thomas]

This forced me to refactor my database schema and in that, I noticed 2 composite keys that should not have been composite.  So, thank you for that.  That said, I assume you get how I had designed the database, but below is an image of a primary key I had where cf_number and action_date were the primary key; to satisfy Jam.py, I had to add council_action_id as a meaningless attribute. No concern with this and I am understanding why Jam.py needs a singly primary key attribute.

For your curiosity, I've uploaded the database here: https://drive.google.com/file/d/1b2IcOyFdZsU8sJlhAOue85Jlv3ZHiRG_/view?usp=sharing - this is a database scraped from the Los Angeles City Council online portal using this project: https://github.com/CordThomas/la-city-council-extractor which I now need to update to account for some minor schema changes.

Now I am off to learn how to build an application around the website using Jam.py.

[Dean D. Babic]

5 mins job :)

I would suggest Jam.py V7. It is more mobile phone friendly.

For any foreign keys there are two options:
- add deleted field for tables with FK

- or add logic for the real delete

I prefer first option. This is how Demo works. 

However, reports and dashboards should use deleted field in logic.

With all available Demos for V7, this is a simple c/p for fancy stuff.

What a new user should now, is that Jam is Event driven platform.

But events come from JS.

Simply put: if the grid is JS, events can be executed.
If the grid is HTML, like nice and fancy Datatables, 

events are no longer able to execute.

This is why other platforms have ie a button for each row or similar.

Jam does not need it. Since a row is or can be an event.

D.

[Fabio Lenzarini]

In my example application “SEM” there is a tool to export/import individual tables.

https://sem.pythonanywhere.com/

You can see if it helps

ciao

Fabio

To view this discussion visit https://groups.google.com/d/msgid/jam-py/12365cb5-729a-4aec-9f4c-694c1c46b522n%40googlegroups.com.