:sparkles: sql database support

[raspitim]

My proposal for a db integration would be to use it like this: define tables in models.py:

import pigeon.db as db

class Model(db.Model):
    field1 = db.Field(str, default="test")

app.py:

from pigeon import DBManager
import models

db = DBManager(models, "path/to/db.sqlite3")

[lstuma]

@raspitim first of all - sry for the late reply.

I love the approach you took but i'd even propose to make the second part, this one:

from pigeon import DBManager
import models

db = DBManager(models, "path/to/db.sqlite3")

optional and if not specified let pigeon create a database automatically

[lstuma]

let's use SQLAlchemy @raspitim

db url format: SQL Alchemy Database URLs

[lstuma]

@raspitim i wanna implement a way to get current schema. but how should database schema be handled for migration handling? as a class?

like:

class DatabaseSchema:
    def __init__(self, name: str, tables: dict):
        self.name = name
        self.tables: dict = tables

tables like:

tables = {
    'users': {
        'id':Field,
        'username':Field,
        'password':Field,
    },
    'orders': {
        'id':Field,
        'product':Field,
        'customer':Field,
    }
}

alternatively we could just handle the schema as a dictionary alltogether - no need for a class tbh

[lstuma]

honestly i think making the entire schema as a dict might be the most elegant way - i'm not entirely sure though. what do you think?

[lstuma]

found way better way of handling this: sqlalchemy has a Schema Definition Language

see following links for more info:

• Schema Definition Language
• Describing Databases with MetaData

[lstuma]

@raspitim PROGRESS!!!

[lstuma]

[lstuma]

for migrations we may use: https://docs.sqlalchemy.org/en/20/core/metadata.html#altering-database-objects-through-migrations

[lstuma]

plan for field datatypes is as following:

• allow entering builtin python datatypes (str, int, float)
• also allow entering sqlalchemy datatypes for more fine-grained control (Text, Integer, Varchar, Float, ...)

[lstuma]

further progress :tada:

current features needed to implement:

• changes in column properties
• when creating a column with nullable=False and no default value, the migration process will crash since each field in the column will in fact be given the value null

[lstuma]

Interacting with the database

Interacting with the database should be possible the following way: (with the thought that this may be near impossible to implement exactly this way, especially the select().where() statement may need an extra .execute())

Example

import pigeon.database as db

class User(db.Model):
    """
    Default User Model for database
    """
    user_id = db.Field(db.Integer, primary_key=True, autoincrement=True)
    user_id2 = db.Field(db.Integer, primary_key=True)
    user_name = db.Field(db.String(16), nullable=False)
    email_address = db.Field(db.String(60), key="email")
    email_address_2 = db.Field(db.String(60), key="email_2")
    nickname = db.Field(db.String(50))

# get all users
User.all()

# querying for user
my_user = User.select().where(User.user_id == 19)
my_user.nickname = "changed"
mu_user.save()

# saving new user
new_user = User(use_name="Tim", nickname="Timmy")
new_user.save()

@raspitim what do you think on this?

implementing Model().all():

WORKING

print(User.all())

gives (example):

[User(email_address=None, email_address_2=None, nickname=Test, user_id=1, user_id2=1, user_name=admin)]

implementing User.select().where(User.nickname == "Test").execute()

WORKS

print(User.select().where(User.column('nickname') == "Test").execute())

gives (example): [User(email_address=None, email_address_2=None, nickname=Test, user_id=1, user_id2=1, user_name=admin)]

print(User.select().where(User.column('nickname') == "NotTest").execute())

gives (example):

[]

Now it is only needed to change the attribute 'nickname' to be a shortcut for .column('

in this case we achieve this by changing the value of the attribute of the class (for the Model in question) after the columns have been gathered and the sqlalchemy.Table object corresponding to the Model has been generated:

...  
for field in cls.fields:
     setattr(cls, field.name, cls.column(field.name))
...

For some reason, setting the attributes using cls.setattr(...) and through the dict cls.dict(...) does not work, therefore resorting to setattr(obj, key, value). Having this implemented we can finally write select statements like we were hoping to be able to:

print(User.select().where(User.nickname == "Test").execute())

gives (example):

[User(email_address=None, email_address_2=None, nickname=Test, user_id=1, user_id2=1, user_name=admin)]

implementing saving of models:

WORKING

user, = User.select().where(User.nickname == "Test").execute()
print(user)
user.email_address = 'not-really-an-email-is-it?'
user.save()
user2, = User.select().where(User.nickname == "Test").execute()
print(user2)

gives (exaple):

User(email_address=None, email_address_2=None, nickname=Test, user_id=1, user_id2=1, user_name=admin)
User(email_address=not-really-an-email-is-it?, email_address_2=None, nickname=Test, user_id=1, user_id2=1, user_name=admin)

new user:

if not User.select().where(User.user_name == 'Jeff').execute():
    jeff = User(user_name='Jeff', nickname='jeff123')
    jeff.save()
    print(jeff)

print(User.select().execute())

gives (example):

User(user_name=Jeff, nickname=jeff123, email_address=None, email_address_2=None, user_id=None, user_id2=None) 

[User(email_address=not-really-an-email-is-it?, email_address_2=None, nickname=Test, user_id=1, user_id2=1, user_name=admin), User(email_address=None, email_address_2=None, nickname=jeff123, user_id=2, user_id2=2, user_name=Jeff)]

implemented deletion

jeff, = User.select().where(User.user_name == 'Jeff').execute()
print(jeff)
jeff.delete()
print(User.select().execute())

gives (example):

User(email_address=None, email_address_2=None, nickname=jeff123, user_id=42, user_name=Jeff)
[User(email_address=not-really-an-email-is-it?, email_address_2=None, nickname=Test, user_id=1, user_name=admin)]

[lstuma]

looking at the previous comment, i believe it is safe to say that any operation is possible using the pigeon implementation.

however altering columns according to changing properties in the model might be important

[lstuma]

working for now!!!