Database abstraction / using an ORM #74
Comments
|
Hah - posted this, then saw you already created an abstracted_database branch with a ton of changes. :) |
|
Yeah, it was quick and dirty but time was pretty tight this week. I
actually did everything during slow times at work on a raspberry pi. I'll
clean it up when i have the luxury to sit at my workstation for a bit.
I did consider an ORM, but this project is pretty lightweight and I didn't
want to bloat it too much- plus the specialized cases of getting table
names, column names and relationships is probably outside the scope of most
ORMs anyway. I enjoy abstracted coding and thought it would be a little
more fun regardless.
Adding MySql and Postge will be quite easy now, so this will be a huge leap
in capability
…On Thu, Feb 16, 2023, 7:30 AM ssweber ***@***.***> wrote:
Hah - posted this, then saw you already created an abstracted_database
branch with a ton of changes. :)
—
Reply to this email directly, view it on GitHub
<#74 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AQX2RETWDS7EIITKS7H7Q23WXYMXFANCNFSM6AAAAAAU6DQQMI>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
|
|
BTW - I tested the sqlite version of your abstracted_database on my work program, and it looks like everything was working. |
|
Awesome, thanks
MySql driver is now working! It wasn't as bad as I thought it would be to
implement. Here is a test script if you want to test it out:
… import pysimplesql as ss
driver = ss.Mysql('sql9.freesqldatabase.com','sql9598795','DMmCAFX2es','sql9598795')
tables = driver.table_names()
for t in tables:
print(f'Table: {t}')
columns = driver.column_names(t)
print(f'\tColumns: {columns}')
pk_col = driver.pk_column(t)
print(f'\tPrimary key: {pk_col}')
print('Relationships: ')
relationships=driver.relationships()
print(f'\t{relationships}')
print(f'Test Query:')
q = 'SELECT * FROM Contact;'
rows = driver.execute(q)
print(f'\t{rows}')
On Thu, Feb 16, 2023 at 1:46 PM ssweber ***@***.***> wrote:
BTW - I tested the sqlite version of your abstracted_database on my work
program, and it looks like everything was working.
—
Reply to this email directly, view it on GitHub
<#74 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AQX2REVFP4QPB4QVEBLQQ43WXZYYLANCNFSM6AAAAAAU6DQQMI>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
|
|
Postgres driver is (mostly) working. I just need to get a couple of
example working now. This code executes and returns expected results:
(assuming you've pip installed psycopg2 or psygcop2-binary)
… import pysimplesql as ss
elephant_postgres = {
'host':'queenie.db.elephantsql.com',
'user':'yunaahtj',
'password':'OMX8u8CDKNVTrldLbnBFsUjxkArTg4Wj',
'database':'yunaahtj'
}
driver = ss.Postgres(**elephant_postgres)
tables = driver.table_names()
for t in tables:
print(f'Table: {t}')
columns = driver.column_names(t)
print(f'\tColumns: {columns}')
pk_col = driver.pk_column(t)
print(f'\tPrimary key: {pk_col}')
print('Relationships: ')
relationships=driver.relationships()
print(f'\t{relationships}')
print(f'Test Query:')
q = 'SELECT * FROM Journal;'
rows = driver.execute(q)
print(f'\t{rows}')
On Thu, Feb 16, 2023 at 5:07 PM Jon Decker ***@***.***> wrote:
Awesome, thanks
MySql driver is now working! It wasn't as bad as I thought it would be to
implement. Here is a test script if you want to test it out:
> import pysimplesql as ss
> driver = ss.Mysql('sql9.freesqldatabase.com','sql9598795','DMmCAFX2es','sql9598795')
>
> tables = driver.table_names()
> for t in tables:
> print(f'Table: {t}')
> columns = driver.column_names(t)
> print(f'\tColumns: {columns}')
> pk_col = driver.pk_column(t)
> print(f'\tPrimary key: {pk_col}')
>
> print('Relationships: ')
> relationships=driver.relationships()
> print(f'\t{relationships}')
>
> print(f'Test Query:')
> q = 'SELECT * FROM Contact;'
> rows = driver.execute(q)
> print(f'\t{rows}')
>
>
On Thu, Feb 16, 2023 at 1:46 PM ssweber ***@***.***> wrote:
> BTW - I tested the sqlite version of your abstracted_database on my work
> program, and it looks like everything was working.
>
> —
> Reply to this email directly, view it on GitHub
> <#74 (comment)>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AQX2REVFP4QPB4QVEBLQQ43WXZYYLANCNFSM6AAAAAAU6DQQMI>
> .
> You are receiving this because you were mentioned.Message ID:
> ***@***.***>
>
|
|
With most of the database abstraction done (duplicate still needs done), I wanted to have some discussion on some sane default behavior. Currently, after inserting a new record:
|
|
|
Thanks for the feedback - this is what I was thinking as well but its always nice to get opinions for others that use it |
|
For duplicate, is this all that would be needed to make it compatible with all three? |
|
Not quite - we will probably have to add a duplicate method to the drivers themselves - Posgres in particular must have the table names in double quotes. Plus, this will keep all direct queries inside the drivers |
I'm thinking since most of the differences between drivers has more to do wth quoting than anything else, perhaps the driver system can be simplified by having most of the query strings in the abstract class, with calls to the derived class to quote things properly
- improved SQLDriver so that more default methods will work for various databases. This was accomplished by adding quote_tabe(), quote_column() and quote_value() methods, as the largest difference between databases is between how the query strings are formatted. - moved delete_record() to be handled by the driver - started working on cleanup and documentation of the abstracted database concept
…l... get the next pk from the database. This still isn't perfect, as there are still 2 issues: - We still aren't querying the sequencer directly - the insert portion of the upsert query currently manually creates the primary key - if it didn't, then the insert would always go through, defeating the purpose of the upsert. Moving forward, there are two options: 1- continue just adding record primary keys manually, with the possibility of manually updating the sequencer afterwards? 2- have separate insert and update queries. This would definitely involve also having accurate primary key synchronization with the sequencer.
|
Huge improvements to this today. Should fix a ton of little collision issues
Initial tests are very promising. I'd be interested in hearing how your parent/child tests go with this @ssweber |
|
I saw your flurry of activity. I tried testing, but ran into this bug: python_7gUct4V0S8.mp4 |
|
The file I used for that is: here. I tested with abstracted_database branch, current after your changes. I also worked to unify the saving mechanism... Form.save_records, Form.prompt_save and Query.prompt_save all use Form.save_records in this diff: https://github.com/PySimpleSQL/pysimplesql/compare/abstracted_database...ssweber:pysimplesql:ab_save_record?diff=unified Once we fix the above bug, I'll re-integrate this code, since it's not a clean merge. |
|
Great, thanks. I probably won't get a chance to look at it until tomorrow,
but the example will help a lot
…On Wed, Feb 22, 2023, 4:22 PM ssweber ***@***.***> wrote:
The file I used for that is: here
<https://github.com/ssweber/pysimplesql-examples/blob/main/tests/parent%20child%20grandchild.py>
.
I tested with abstracted_database branch, current after your changes.
I also worked to unify the saving mechanism... Form.save_records,
Form.prompt_save and Query.prompt_save all use Form.save_records in this
diff:
https://github.com/PySimpleSQL/pysimplesql/compare/abstracted_database...ssweber:pysimplesql:ab_save_record?diff=unified
Once we fix the above bug, I'll re-integrate this code, since it's not a
clean merge.
—
Reply to this email directly, view it on GitHub
<#74 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AQX2REXCK25YSBCMKFZRCFLWYZ7RRANCNFSM6AAAAAAU6DQQMI>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
|
|
Btw - I branched from This branch, so it’s just been in the commits from today. If that helps narrow it down. Might take a look at it later and narrow it down to the commit that introduces it. |
|
I did a quick attempt at a fix from my phone. I'm not able to test myself, we have some differences still (my Form no longer supports sql_commands, as it's moved to the driver) I'll look more tomorrow when I have a computer in front of me |
|
Cool I’ll test in the morning. I posted the wrong form - this one uses the sqldriver. |
|
Ok, I have the issue narrowed down. For some reason, cursor.lastrowid is always returning 2 in the driver. Going to look at it now. |
|
Just commenting with my research so far: |
|
Ok, fix is pushed. In the end, I did have to kind of hack around the issue. With SQLite, the cursor.lastrowid is not operating as it should per documentation. After countless tests, not once could I get it to return None on an UPDATE query - which the docs clearly state should happen. In the end, for SQLDriver.save_record() I just manually set the ResultSet.lastrowid = None, as in the end it really doesn't matter. The caller already knows the pk, as it was passed into save_records() - so this will be more of a documentation issue that ResultSet.lastrowid will only be set when SQLDriver.insert_record() is run, or on any INSERT query executed with SQLDriver.execute(). This will at least be consistent across the databases on the pysimplesql end |
|
Your fix worked :). Thank you! Maybe you were bumping into where pythons wrapper diverges from direct SQLite? lastrowid: |
Need to start cleaning this up soon, I can see a new release coming
I've spent way too much time on this. I can't for the life of me figure out why the asterisks are appearing AFTER the column field, and not before as they are clearly defined. I'm thinking this has to be a PySimpleGUI bug of some sort. The sg.Text() element is defined in the layout before the actual element, but displays after the element in the GUI. I have purposely set the visibility to Tue on creation and you can see a very quick blip on loading where the "marker" appears before the sg.element - but everythin after shows it after the element
|
This is working quite well as a concept - I'm going to close this now |
I was thinking about this the other day. What are you thoughts on using an ORM like Pony to handle the backend?
This is all new to me, but I imagine first switch to using their execute() function, and then work through replacing functionality with their higher level functions.
————————————————————
My next big update will be some database abstraction so that I can start
adding other database engines :)
Originally posted by @PySimpleSQL in #70 (comment)
The text was updated successfully, but these errors were encountered: