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 build my own iterator that
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 possible at the ORM level to
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.commit(). For example: Flask-SQLAlchemy is based on SQLAlchemy, so
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 databases are normally present by default. If you are able to
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 — And does not work — neither does — How should
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 series of files (around 5 million rows in total) and my session
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: it starts a transaction
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.Enum Although I would only recommend its usage if your database has a native enum type. Otherwise I would personally just use an
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 level. It doesn’t handle any tricky cases