I have a ~10M record MySQL table that I interface with using SqlAlchemy. I have found that queries on large subsets of this table will consume too much memory even though I thought I was using a built-in generator that intelligently fetched bite-sized chunks of the dataset: To avoid this, I find I have to bui…
Tag: sqlalchemy
Make Sqlalchemy Use Date In Filter Using Postgresql
I’m trying to perform the following query in Sqlalchemy. I have tried several methods. Some here on SO. But none seems “realistic” since some do Casts Other String Formating ?!?! and not a plain simple Date() ( By Date i Mean the Postgresql Date not the Python One) at the ORM Field. Is it po…
How to query database by id using SqlAlchemy?
I need to query a SQLAlchemy database by its id something similar to but for id. How do I do this? [Searching over Google and SO didn’t help] Answer Query has a get function that supports querying by the primary key of the table, which I assume that id is. For example, to query for an object with ID of
Flask-SQLalchemy update a row’s information
How can I update a row’s information? For example I’d like to alter the name column of the row that has the id 5. Answer Retrieve an object using the tutorial shown in the Flask-SQLAlchemy documentation. Once you have the entity that you want to change, change the entity itself. Then, db.session.c…
How to create a new database using SQLAlchemy?
Using SQLAlchemy, an Engine object is created like this: Accessing engine fails if the database specified in the argument to create_engine (in this case, mydb) does not exist. Is it possible to tell SQLAlchemy to create a new database if the specified database doesn’t exist? Answer On postgres, three da…
Select NULL Values in SQLAlchemy
Here’s my (PostgreSQL) table — I want to select all people that are not known to be married, i.e., including those with NULL marriage_status. This does not work — Of course this does — The problem is that I’m accessing it from SQLAlchemy with — which gets translated to R…
SQLAlchemy: What’s the difference between flush() and commit()?
What the difference is between flush() and commit() in SQLAlchemy? I’ve read the docs, but am none the wiser – they seem to assume a pre-understanding that I don’t have. I’m particularly interested in their impact on memory usage. I’m loading some data into a database from a seri…
Is it possible to issue a “VACUUM ANALYZE ” from psycopg2 or sqlalchemy for PostgreSQL?
Well, the question pretty much summarises it. My db activity is very update intensive, and I want to programmatically issue a Vacuum Analyze. However I get an error that says that the query cannot be executed within a transaction. Is there some other way to do it? Answer This is a flaw in the Python DB-API: i…
Best way to do enum in Sqlalchemy?
I’m reading about sqlalchemy and I saw following code: Should I make ‘type’ an int, with constants in a library? Or should I make just make type an enum? Answer SQLAlchemy has an Enum type since 0.6: http://docs.sqlalchemy.org/en/latest/core/type_basics.html?highlight=enum#sqlalchemy.types.E…
SQLAlchemy: a better way for update with declarative?
Let’s say I have a user table in declarative mode: When I know user’s id without object loaded into session, I update such user like this: I dislike using User.__table__, should I stop worrying with that? Is there a better way to do this? Answer There’s also some update capability at the ORM…