This week, my head-bashing was courtesy of SQL! Learned some good things though.
- A good reason to use SQLAlchemy/SQLModel if interacting with SQL from Python is that you can also use Alembic! It is a pretty straightforward change management tool for your SQLAlchemy-defined tables/constraints/etc. For example, I made a typo in my table models and needed to add a unique constraint to a column. At the terminal (assuming you have it installed and your venv activated), I wrote:
> alembic init alembic # then I added my database's connection string to alembic.ini > alembic revision -m "add unique constraint to article.url" # filled out the upgrade and downgrade functions in the revision file > alembic upgrade head
And thats it! The first comment is truly just a one line change, and filling out the upgrade/downgrade was really easy. The
revision command creates a stub for the file, and you just need to fill out the body of the two functions. Here is all I wrote:
def upgrade(): op.create_unique_constraint("unique_article_url", "article", ["url"]) def downgrade(): op.drop_constraint("unique_article_url", "article")
Nice and easy.
- Super useful StackOverflow link for deleting all duplicate rows in a table. Saved me a lot of time, and I simply changed the
SELECTto get the problematic
ids so I could delete connected data in other tables as well
- More random Postgres queries I found useful to have lying around for debugging, courtesy of another StackOverflow answer when I was investigating a locked/hanging process
- SQLAlchemy supports
ON CONFLICThandling in their insert statements! Between this and this all I had to change was:
# from this db_client.add_all(db_articles) # to this instead insert_statement = insert(Article).on_conflict_do_nothing(index_elements=["url"]) db_client.exec(statement=insert_statement, params=db_articles)
And voila, any conflicts on my unique
url column are handled as desired.
+raaid© Raaid Arshad.RSS