I would like to know how to save a calculated value in a clean way in the database: An example (taken from the SQLAlchemy manual): So length is calculated. But for easier query look-ups (in another database tool), I would like to have this length saved as well into the table. So my first crude test was to sim…
Tag: sqlalchemy
“Maximum number of parameters” error with filter .in_(list) using pyodbc
One of our queries that was working in Python 2 + mxODBC is not working in Python 3 + pyodbc; it raises an error like this: Maximum number of parameters in the sql query is 2100. while connecting to SQL Server. Since both the printed queries have 3000 params, I thought it should fail in both environments, but…
Flask SQLAlchemy reflection ignoring most of tables on Redshift
I’m creating the engine and the Metadata as follows Yields AttributeError: packages, and dir(Base.classes) returns no attributes with that name, neither with the orders name. Taking the only=[‘orders’, ‘packages’] off makes it reflect only a few random tables. Now, when using the…
asyncpg.exceptions.DataError: invalid input for query argument $1: 217027642536 (value out of int32 range)
I’m working on a project that uses FastAPI alongside Pydantic and SQLAlchemy. I’m also using encode/databases to manage database connections. But for some weird reason, I get asyncpg.exceptions.DataError: invalid input for query argument $1: 217027642536 (value out of int32 range) anytime I try sa…
Python flask-sqlalchemy: Do I have to commit session after a query?
I am writing an app in python flask-sqlalchemy with MySQL DB (https://flask-sqlalchemy.palletsprojects.com/en/2.x/) and I am wondering if I have to make “db.session.commit() or db.session.rollback()” after GET call, which only query DB . For example: Answer orders = Order.query.all() is a SELECT q…
Delete sql table rows from python
I can successfully connect my python notebook with sql server this way : Let’s take this sample dataframe : I have a sql table with the same columns, named table_name. Objective : I want to delete in table_name all the rows where a row in df has the same Name, same Date, same Status, and Max = 0 (only i…
List of object attributes in pydantic model
I use Fast API to create a web service. There are following sqlAlchemy models: Pydantic schemas are below: What I have now is: What I want to achieve is to get user from api in following structure: Is that possible? How should I change schemas to get this? Answer If you are okay with handling the how to ̶…
Partial update in FastAPI
I want to implement a put or patch request in FastAPI that supports partial update. The official documentation is really confusing and I can’t figure out how to do the request. (I don’t know that items is in the documentation since my data will be passed with request’s body, not a hard-coded…
How to upsert pandas DataFrame to PostgreSQL table?
I’ve scraped some data from web sources and stored it all in a pandas DataFrame. Now, in order harness the powerful db tools afforded by SQLAlchemy, I want to convert said DataFrame into a Table() object and eventually upsert all data into a PostgreSQL table. If this is practical, what is a workable met…
Why BINARY usage in SQLAlchemy with Python3 cause a TypeError: ‘string argument without an encoding’
I read a lot of similar questions but none of the clearly answer my issue. I’m using sqlalchemy-utils EncryptedType on a mysql table column. The table creation and the insert is ok, but when I’m trying to do a query a receive: I found out that this error occurs only using python 3, not using pytho…