Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Alembic has, as one of its operation, <a href="http://alembic.zzzcomputing.com/en/latest/ops.html?highlight=insert#alembic.operations.Operations.bulk_insert"><code>bulk_insert()</code></a>. The documentation gives the following example (with some fixes I've included):</p> <pre><code>from datetime import date from sqlalchemy.sql import table, column from sqlalchemy import String, Integer, Date from alembic import op # Create an ad-hoc table to use for the insert statement. accounts_table = table('account', column('id', Integer), column('name', String), column('create_date', Date) ) op.bulk_insert(accounts_table, [ {'id':1, 'name':'John Smith', 'create_date':date(2010, 10, 5)}, {'id':2, 'name':'Ed Williams', 'create_date':date(2007, 5, 27)}, {'id':3, 'name':'Wendy Jones', 'create_date':date(2008, 8, 15)}, ] ) </code></pre> <p>Note too that the alembic has an <a href="http://alembic.zzzcomputing.com/en/latest/ops.html#alembic.operations.Operations.execute"><code>execute()</code></a> operation, which is just like the normal <code>execute()</code> function in SQLAlchemy: you can run any SQL you wish, as the documentation example shows:</p> <pre><code>from sqlalchemy.sql import table, column from sqlalchemy import String from alembic import op account = table('account', column('name', String) ) op.execute( account.update().\ where(account.c.name==op.inline_literal('account 1')).\ values({'name':op.inline_literal('account 2')}) ) </code></pre> <p>Notice that the table that is being used to create the metadata that is used in the <code>update</code> statement is defined directly in the schema. This might seem like it breaks <a href="http://en.wikipedia.org/wiki/Don%27t_repeat_yourself">DRY</a> (isn't the table already defined in your application), but is actually quite necessary. If you were to try to use the table or model definition that is part of your application, you would break this migration when you make changes to your table/model in your application. Your migration scripts should be set in stone: a change to a future version of your models should not change migrations scripts. Using the application models will mean that the definitions will change depending on what version of the models you have checked out (most likely the latest). Therefore, you need the table definition to be self-contained in the migration script.</p> <p>Another thing to talk about is whether you should put your seed data into a script that runs as its own command (such as using a Flask-Script command, as shown in the other answer). This can be used, but you should be careful about it. If the data you're loading is test data, then that's one thing. But I've understood "seed data" to mean data that is required for the application to work correctly. For example, if you need to set up records for "admin" and "user" in the "roles" table. This data SHOULD be inserted as part of the migrations. Remember that a script will only work with the latest version of your database, whereas a migration will work with the specific version that you are migrating to or from. If you wanted a script to load the roles info, you could need a script for every version of the database with a different schema for the "roles" table. </p> <p>Also, by relying on a script, you would make it more difficult for you to run the script between migrations (say migration 3->4 requires that the seed data in the initial migration to be in the database). You now need to modify Alembic's default way of running to run these scripts. And that's still not ignoring the problems with the fact that these scripts would have to change over time, and who knows what version of your application you have checked out from source control.</p>
    singulars
    1. This table or related slice is empty.
    1. POCreating seed data in a flask-migrate or alembic migration
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USdequis
    1. USMark Hildreth
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCreating seed data in a flask-migrate or alembic migration
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIs there a reverse to `bulk_insert()`? I believe there isn't, which would make it harder to write the `downgrade`. Even if there was a bulk_delete, what do you do if the data was changed by the application and looks completely different than when it was inserted by `bulk_insert`? It would only be safe to downgrade if the table was added in that same migration, as in that case you have to delete the table anyway, but other cases are not easily addressed. Still, I don't feel a need to downvote your answer.
      singulars
      1. PO
      1. USMiguel
    2. COIf the `bulk_insert` is done when a table is created (which is often the case), dropping the table is enough. Otherwise, you can use `execute` to delete. This isn't a problem with using alembic in this way, this is a problem with database migrations. They aren't easy, and no tool is going to make them easy (only make them easier). Also, I removed my downvote from your answer after I added my comment. No hard feelings :)
      singulars
      1. PO
      1. USMark Hildreth
    3. CO@MarkHildreth I went with your approach since all I'm storing in the table in this migration are required constants, and this table is read-only. I agree that the ad-hoc table is very un-DRY. Thanks!!!
      singulars
      1. PO
      1. USMark Richman
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload