databases
databases copied to clipboard
Documentation lacks guidance on creating database tables
The Databases Introduction executes a raw SQL query like so:
query = """CREATE TABLE HighScores (id INTEGER PRIMARY KEY, name VARCHAR(100), score INTEGER)"""
await database.execute(query=query)
The next page, Database Queries instead uses SQLAlchemy Core like so:
import sqlalchemy
metadata = sqlalchemy.MetaData()
notes = sqlalchemy.Table(
"notes",
metadata,
sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column("text", sqlalchemy.String(length=100)),
sqlalchemy.Column("completed", sqlalchemy.Boolean),
)
...
from databases import Database
database = Database('postgresql://localhost/example')
# Establish the connection pool
await database.connect()
# Execute
query = notes.insert()
values = {"text": "example1", "completed": True}
await database.execute(query=query, values=values)
Nowhere actually shows how to create the tables. I think the documentation could do a better job at showing a full example that runs and works.
The documentation does link to SQLAlchemy core official tutorial, however this tutorial talks about "engines" which are nowhere to be found in the databases
library, so it's not easy to find out how to actually create a table or adapt their examples. One may naively try to use the database
as the engine
, which fails with a rather cryptic "AttributeError: 'Database' object has no attribute '_run_visitor'" error.
I think the documentation could do a better job at explaining how one should map the concepts from the SQLAlchemy tutorial for use in this library.
Finally, the documentation does not seem to provide a reference section to learn all the methods available and look at their internal documentation without the need for Python's help()
. I think the documentation should have a section with the reference documentation for all public types.
All of this is from the point of view who wants to use databases
as their only interface to databases, and has no experience with sqlalchemy
. Given that sqlalchemy
is the recommended way to use it, I think the library should put some effort into explaining how the both libraries work together.
This comment helped me a lot
I've also been wondering about this since a few days, before I found this issue.
I feel one way is to use the sync method like described in the comment^ which uses sqlalchemy create_all(), but I was wondering if it's just smarter to specify it in installation or as a comment to be ran on first run. I'm not sure I like the idea of having a sync call present in an async setup.
It would definitely be very useful to have a compatible implementation or similar, right from databases.
I think production-ready way to create tables is alembic package. Its official way to create database migration by sqlalchemy.
You can find example of how I did it in my pet project https://gitlab.com/artslob/cyberbox/-/tree/master/cyberbox. Basically you create metadata and Table objects from sqlalchemy, then create config for alembic, and in env.py
include your target metadata. Also I strongly suggest to define naming convention as early as possible.
Metadata + tables + naming convention: https://gitlab.com/artslob/cyberbox/-/blob/master/cyberbox/orm.py Alembic migrations + config file: https://gitlab.com/artslob/cyberbox/-/tree/master/cyberbox/migrations
For tests its pretty good to use metadata.create_all
, but in production code better way is yo use migrations.
How about creating temporary tables in runtime?
Updated:
OK, I finally got it. Here are the steps to create a table in Databases:
- Define your table the SQLAlchemy way.
- Get the
CREATE TABLE
string: - Pass it to
await database.execute(query=...)
, done.
Example code:
import sqlalchemy
from sqlalchemy.dialects import postgresql
from sqlalchemy.schema import CreateTable
from databases import Database
metadata = sqlalchemy.MetaData()
# Define the table
notes = sqlalchemy.Table(
"notes",
metadata,
sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column("text", sqlalchemy.String(length=100)),
sqlalchemy.Column("completed", sqlalchemy.Boolean),
)
dialect = postgresql.dialect()
# Get the CREATE TABLE statement
query = str(CreateTable(notes).compile(dialect=dialect))
database = Database('postgresql://localhost/example')
# Establish the connection pool
await database.connect()
# Execute the SQL statement
await database.execute(query=query)
References:
Although tighter integration would be welcome (imagine a method to create a table that does what you did manually above), that seems like the way to go for now, thanks. Now all there's left is to update the documentation.
This is what I do to create all tables based on @rockallite
dialect = sqlite.dialect()
for table in metadata.tables.values():
query = str(CreateTable(table).compile(dialect=dialect))
await database.execute(query=query)