Archive
SQLAlchemy – Print the query params
Problem:
When troubleshooting an SQL query in development, you may need to see what values are being passed to the query. (python + postgresql + sqlalchemy)
Solution:
print(query)
print(query.compile().params)
Checking data format
Problem: When doing bulk inserts, if the data format is wrong I get errors. Below commands are a way to check that I have the correct format
mydb=> select 68,216.00::integer;
int4
——-
68216
(1 row)mydb=> SELECT ‘2020/09/22′::date;
date
————
2020-09-22
(1 row)mydb=> SELECT ’22/09/2020′::date;
ERROR: date/time field value out of range: “22/09/2020”
LINE 1: SELECT ’22/09/2020’::date;
^
HINT: Perhaps you need a different “datestyle” setting.
Insert with Select
One of the most pleasant aspects of working with Postgres is coming across features that save me lots of typing. Whenever I see repetitive SQL queries, I now tend to assume there is a feature available that will help me out.
One such feature is INSERT using a SELECT, and beyond that, using the output of a SELECT statement in place of VALUES.
https://www.chesnok.com/daily/2013/11/19/everyday-postgres-insert-with-select/comment-page-1/
sqlalchemy.exc.NoSuchModuleError: Can’t load plugin: sqlalchemy.dialects:driver
Problem:
I was getting the following error on running alembic upgrade head
sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:driver
Solution:
I had not specified to alembic which sqlalchemy driver to use. I added lines 27-28 to my env.py
# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.
db_url = os.environ.get("DATABASE_URL")
config.set_main_option("sqlalchemy.url", db_url)
Alternatively, I could have edited the alembic.ini file and changed the SQLAlchemy URL as per the tutorial.
$ less alembic.ini ... sqlalchemy.url = postgresql://scott:tiger@localhost/test ...
Source:
https://alembic.sqlalchemy.org/en/latest/tutorial.html
Dockerise a Python App with PostgreSQL
heroku – alembic
Problem:
Alembic migrations are not run automatically on heroku. On first install of the application, attempting to run $heroku run alembic upgrade head --app application-name results in an error as it appears that tables are build from the models that were defined in the application.
$ heroku run alembic history --app application-name
$ heroku run alembic downgrade -1 --app application-name fails with
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
ERROR [alembic.util.messaging] Relative revision -1 didn't produce 1 migrations
FAILED: Relative revision -1 didn't produce 1 migrations
$ heroku run alembic downgrade 017e517ce319 --app application-name fails with
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
ERROR [alembic.util.messaging] Destination 017e517ce319 is not a valid downgrade target from current head(s)
FAILED: Destination 017e517ce319 is not a valid downgrade target from current head(s)
On checking the alembic version in the database
$ heroku pg:psql DATABASE_URL --app application-name
application-name::DATABASE=>select * from alembic_version;
version_num
-------------
(0 rows)
application-name::DATABASE=> update alembic_version set version_num = '017e517ce319';
UPDATE 0
application-name::DATABASE=> select * from alembic_version;
version_num
-------------
(0 rows)
Solution:
To start using alembic I needed to drop all tables and run alembic. Doing a dump of the db, running alembic, then reading back the dump should also work.
1. Clear all data
$ heroku run pg:reset DATABASE_URL --app application-name
2. Run alembic
3. Moving forward, always manually run alembic. (You could add this to your deploy step as well, depends on your needs.)
4. alembic downgrade,upgrade should now work.
Getting Started with Heroku – notes
Notes:
1. Install the heroku cli.
2. If you have a preexisting git repository with the desired files to be pushed to heroku.
You need to have at minimum a Procfile and requirements.txt file
$ less Procfile
web: gunicorn wsgi:app
$ less requirements.txt
Flask==1.0.3
alembic==1.0.10
...
gunicorn==20.0.4
3. Merge changes into master branch if working off a different branch.
4. Login into heroku.
$ heroku login
5. Create a heroku app. You can specify a name for the app. Below command generate a random name.
$ heroku create
To create an app outside the default region of the us.
$ heroku create midnight-cask-76349 --region eu
6. Create a heroku postgres database.
$ heroku addons:create heroku-postgresql:hobby-dev
7. Push changes up to heroku app. Check if Alembic changes were applied. If not, run alembic manually. (See note 10)
$ git push heroku master
8. Set application `Config vars` under settings on the dashboard
9. Get database application details from dashboard. Follow link from database details under installed addons section. THe database url details will be available under the linked settings. Also have instructions on how to connect to database
$ heroku pg:psql postgresql-sparkle-97339 --app midnight-cask-76349
10. To manually run alembic
$ heroku run alembic upgrade head
11. To check logs
$ heroku logs --tail
12. To get database credentials
$ heroku pg:credentials:url
13. You can clone the heroku repo for the app locally
$ heroku git:clone -a midnight-cask-76349
Sources:
https://stackabuse.com/deploying-a-flask-application-to-heroku/
https://stackoverflow.com/questions/34379130/pushing-to-heroku-fails-no-such-app-fatal
https://elements.heroku.com/addons/heroku-postgresql
https://www.digitalocean.com/community/tutorials/how-to-serve-flask-applications-with-gunicorn-and-nginx-on-ubuntu-14-04
https://blog.dbi-services.com/a-free-postgresql-cloud-database/
https://devcenter.heroku.com/articles/heroku-postgresql#designating-a-primary-database
Postgres “Select * from ” freezes
Problem:
Due to a programming error in a python program I had written, I was unknowingly preventing postgres from obtaining an access lock to a table. There were no errors but trying to run a
Select * from <table>
would just freeze.
Solution:
The solution was to fix the python program. But I used the following query to get some more insight into what was causing the issue.
$ psql -U myuser -h localhost mydatabase Password for user myuser: psql (10.8 (Ubuntu 10.8-1.pgdg14.04+1)) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. mydatabase=> SELECT * FROM pg_locks l JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r' WHERE t.relname = '<table>'; pid ------ 2028 2159 2029 2357 2106 2409 2128 2387 2084 (9 rows) mydatabase=> \q
Source:
https://stackoverflow.com/questions/10317114/postgresql-drop-table-doesnt-work
Beginner’s Guide to Using Databases with Python: Postgres, SQLAlchemy, and Alembic
Running Flask on Kubernetes
Posted by Michael Herman on Sep 19, 2018
In this post, we’ll first take a look at Kubernetes and container orchestration in general and then we’ll walk through a step-by-step tutorial that details how to deploy a Flask-based microservice (along with Postgres and Vue.js) to a Kubernetes cluster.
https://testdriven.io/running-flask-on-kubernetes
Source:
https://pycoders.com
pseudo-scripter 