pudo / dataset

Easy-to-use data handling for SQL data stores with support for implicit table creation, bulk loading, and transactions.
https://dataset.readthedocs.org/
MIT License
4.76k stars 297 forks source link

Added helper method get_or_create database #347

Closed kapily closed 3 years ago

kapily commented 3 years ago

This PR adds a succinct way for users to create a table with:

I've frequently used this helper function in my projects because I've wanted to explicitly specify the field types and reduces the amount of code needed to create a table with the columns and indicies that I wanted.

This example in the comments best illustrates this.

With this PR, you can now create a table like this:

db = dataset.get_or_create('sqlite:///my_website.sqlite3', schema={
    'users': {
        'primary': ('username', 'string'),
        'columns': [('age', 'integer'), ('upvotes', 'integer'), ('tagline', 'text'), ('bio', 'text'), ('url', 'text')],
        'index': ['url', ['username', 'upvotes'], ['username', 'age']]
    }
})

Instead of:

db = dataset.connect('sqlite:///my_website.sqlite3')
if 'users' not in db:
    table = db.create_table(users, primary_id='username', primary_type=db.types.string)
    table.create_column('age', db.types.integer)
    table.create_column('upvotes', db.types.integer)
    table.create_column('tagline', db.types.text)
    table.create_column('bio', db.types.text)
    table.create_column('url', db.types.text)
    table.create_index([url])
    table.create_index(['username', 'upvotes'])
    table.create_index(['username', 'age'])
pudo commented 3 years ago

I'm a little bit against this feature: it adds another syntax to the library, which is already getting confusing. Much of the point of dataset is to allow implicit schema creation. If the table layout is to be well-defined, then that's a strong hint that you might want to use SQLAlchemy proper. Otherwise, the next addition is going to be foreign key constraints and defining relationship mappings - then we've gone full circle.

Proposal: if there are two other people who chime in here and say they really really really want it, we'll do it. But then it also needs to be comprehensively documented.