Tag: sqlalchemy

Optimising filtering multiple SQL count sub queries with SQLAlchemy

I’m trying to convert raw postgres-SQL into sqlalchemy which uses a count and filter This works great and is very fast (sub 100ms – even when filtering 20 fields) I’ve written the sqlalchemy as (simplified) This produces the following SQL This works but is quite slow (+1 second). I’m not sure why it’s so slow or how to do a

ID primary key of ONE to MANY related tables jumping numbers with POSTGRESQL

alembic fastapi postgresql sql sqlalchemy

I am having an issue where the id column of tables users and posts are jumping in increments users and have many notes ==> one to many relationship table users table notes what is happening is the id column of the two tables jump…if there is 3 in id of notes, then next if for users will be 4 how

Can’t write SqlAlchemy query with contains operation

postgresql sql sqlalchemy

Let’s say that we have a two tables: Movie and Genre. Genre has an attribute genres which is a PostgreSQL ARRAY of genre ids. I have written a query to get all movie ids with genre names. But sadly I am getting: So, how can I rewrite my query to get what I want? Answer Remove the square brackets []

First row of all groups in Flask SQLAlchemy

flask-sqlalchemy postgresql python sql sqlalchemy

I want to group the database by gauge_id and then get the row having maximum time_col. This is my current code: But I am only able to get both gauge_id and time_col. When I add another column (reading), like this: it gives this error. How do I return the top row of each groups? Thanks in advance. Answer One way

What is the default limit for auto generated primary Column(Integer) in SQLAlchemy with FastAPI and MYSQL?

mysql orm python-3.x sql sqlalchemy

Currently, I’m using Python’s fastAPI with SQLAlchemy and MYSQL. I’ve defined an auto-generated primary column as shown below. What’s the maximum number that this column can hold? Currently, the DB grows by 10k records per day, and I clear 70% of it on the weekends. The number of records will always stay constant, but I want to know the maximum

SQLAlchemy Column Types As Generic (BIT Type)

python-3.x sql sqlalchemy

I am trying to list out the columns of each table in an MSSQL database. I can get them fine, but I need to turn them into generic types for use elsewhere. (Python types would be more ideal but not sure how to do that?) My code so far works until I come across a BIT type column. I get

ORM query many to many to one, Flask sql-alchemy

flask flask-sqlalchemy sql sqlalchemy

I have three flask-sqlalchemy-models. Books – unique entries by admin: class Book(db.Model): id = db.Column(db.Integer, primary_key=True, autoincrement=True) book_name = db.Column(db.String(20)…

Is there other option to check if a table is in a SQl connection , Im trying to use dialect.has_table

python sql sqlalchemy web-scraping

Im getting this error : AttributeError: ‘Engine’ object has no attribute ‘exec_driver_sql’ Answer Nowadays the preferred method for performing such reflection is to use inspect:

Function returns row type as single value instead of one value per field

postgresql set-returning-functions sql sql-function sqlalchemy

The only time I encountered a similar problem on SO is on here, but the proposed solution was not relevant for me. I call a PostgreSQL function, but it’s returning a single value instead of one value per field, which I can access. I have a table “Words”, which consists of a primary key word and other fields. This is

How create Postgres database using SQLAlchemy

postgresql python sql sqlalchemy

I want write project using database library (SQLAlchemy). When I use SQLite, all works good, but when I deploy project on server (Heroku with Postgres plugin), It doesn’t work. This is code for my database: I have engine & Base variable: In DATABASE_URL link to base on Heroku(Postgres). For create database I write in Interactive Python Console: After these manipulations