Questions tagged [sqlalchemy]

Use this tag for the eponymous Python SQL toolkit and Object Relational Mapper.

Filter by
Sorted by
Tagged with
0 votes
0 answers
22 views

Use JSONB as default alias für JSON [migrated]

My application uses the SQLalchemy ORM code exclusively to define the database schema. For the database, two uses cases exist. For the sake of simplicity, I'll call the first the "simple" ...
user avatar
  • 171
0 votes
0 answers
22 views

SQLAlchemy Incremental value based on another column [closed]

I'm using Flask-SQLAlchemy with MySQL. I have a relationship table for reproductions with a primary key formed of 3 columns: user_id, song_id, count user_id and song_id are foreing keys to other ...
user avatar
0 votes
0 answers
35 views

Is there a cross join in these CTEs?

I have a query that is constructed from several CTEs (in order to organize the query). I constructed it in my SQL workbench and was happy with the test results on a smaller dataset, so I converted it ...
user avatar
  • 171
0 votes
1 answer
22 views

Using Indexing When Performing JOINs on Part of a Composite Key

I have a table in my database (hosted on MariaDB) that looks like this (I have given the definition in SQLAlchemy), class Address(Base): __tablename__ = 'address' __table_args__ = {'schema': DB_NAME} ...
user avatar
0 votes
0 answers
43 views

SQLAlchemy Relationships: Row in Child Table is Dependent on Two Parent Tables

I have a Table whose row is dependent on the two parent tables. Child Table: class ClassUpdate(Base): id = Column(INTEGER(unsigned=False), name="ID", primary_key=True, nullable=False, ...
user avatar
0 votes
0 answers
60 views

ORM vs Raw Queries

I am working on a project which I want to eventually launch to the public. In past, I have Used Django which uses ORM to use databases as python models. But I also know that they add extra overhead in ...
user avatar
1 vote
1 answer
236 views

UPDATE incredibly slow on small PostgreSQL query - EXPLAIN ANALYZE causes it to hang

I'm trying to debug a very slow UPDATE query in one of my tables. The table name is beepers. It has 2486 rows and weighs 1204 MB. It has 18 columns and two indices: one on id and the other on ...
user avatar
0 votes
1 answer
298 views

I get the error "(psycopg2.OperationalError) FATAL: role "wsb" does not exist", but the user does exits

I am trying connect to my postgress database using SQLAlchemy. I was working fine yesterday (01/27/22). Now I get the following error: sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) ...
user avatar
0 votes
1 answer
36 views

Can this many-to-many table be improved?

I am creating a database where a user can add many books. And a book can be read by many users. I'm using PostgreSQL and set up the following structure: CREATE TABLE users( uid SERIAL PRIMARY KEY, ...
user avatar
  • 103
1 vote
0 answers
1k views

SQL connection error in python webapp: "pandas.io.sql.DatabaseError: Execution failed on sql"

I have built a Webapp with python flask, that reads and writes to SQL tables. Typically in the morning, I receive an error regarding SQL query execution that looks like this (see logs snippet below). ...
user avatar
  • 11
0 votes
0 answers
81 views

Python / AWS RDS - 300 insert statements taking 10 minutes to complete

I'm using Python 3.7 with SQLAlchemy on Ubuntu 18.04. I have two MySQL Databases - a local instance hosted on bare metal and an Amazon RDS instance. Both are accessible from MySQL Workbench on my ...
user avatar
0 votes
2 answers
974 views

SQLAlchemy: table version already exists. pgAdmin4 fails on Linux Mint 20 and 20.1

I already found the solution, I just want to share it in case someone has the same problem. Situation Install pgAdmin4 version 4.30 on Linux Mint 20 (Ulyana) and Linux Mint 20.1 (Ulyssa) inside ...
user avatar
0 votes
1 answer
54 views

Check if a database table has been renamed

My app has a list of an external db's tables and it needs to be regularly updated. How do I check if a table has been renamed? i.e. how to differentiate between a new table and an old table that's ...
user avatar
-1 votes
1 answer
405 views

sqlalchemy bulk update [closed]

I use PostgreSQL 12 SQLAlchemy 1.4 I have a table with multiple columns and one column is a value which is computed in the python code before it's inserted. Now the factors in this computed value may ...
user avatar
5 votes
2 answers
713 views

Postgres not returning data on array_agg query as below

The problem arise when there are no data for books in specific library. Consider a following working scenario. Table library -------------------------------- | id | name | owner | ----------...
user avatar
  • 209
-2 votes
3 answers
81 views

can I write the below query using Over()in mysql?

I am trying to optimise the below sql query but am not finding any way to do it: SELECT DISTINCT ( SELECT COUNT(followers.follower_id) FROM followers WHERE followers....
user avatar
0 votes
1 answer
33 views

Splitting table with many field for convenience example

Is this good idea to split table with many field for on two parts for convenience? Real example: class UserData(Base): __tablename__ = "users_data" id = Column(Integer, ...
user avatar
0 votes
1 answer
340 views

How to efficiently store impressions in db?

For my flask app I need to track impressions, clicks on images. The current implementation that I have is impressions table where I store impression ip, useragent, geo location and all the ...
user avatar
0 votes
0 answers
256 views

Migrating from MariaDB to Mysql - Duplicate Constraint Name

I am attempting to migrate from MariaDB to MySQL by doing a mysqldump from MariaDB and then restoring it to MySQL (mysql:latest docker container). I am getting the following error when importing into ...
user avatar
1 vote
1 answer
1k views

Unable to convert a table to hypertable due to unique index error

I am creating a table using Flask SQL Alchemy as follows: class Price(db.Model): __tablename__ = "prices" id = db.Column(db.Integer, primary_key=True) country_code = db.Column(db.String(2)...
user avatar
  • 113
1 vote
1 answer
557 views

Storing many pandas DataFrames in SQLite with metadata

I need some help with designing a database. My aim is to persistently store a number of pandas DataFrames in a searchable way, and from what I've read SQLite is perfect for this task. Each DataFrame ...
user avatar
  • 113
0 votes
1 answer
316 views

Generated DDL doesn't match with CREATE TABLE statement

I'm using MySQL with InnoDB engine. I create a new table that contains a foreign key, eg: CREATE TABLE rooms ( id INTEGER NOT NULL AUTO_INCREMENT, my_id VARCHAR(15), house_id INTEGER, ...
user avatar
0 votes
1 answer
271 views

Ram consumed by single query in Postgres using SqlAlchemy

I have a list of 36 queries ranging from low complexity(No joins) to high complexity(joins with subquery). I want to find the RAM Consumed by each query. This is required for performance testing for ...
user avatar
1 vote
1 answer
2k views

How to Bulk Insert sqlalchemy subclasses

I am trying to bulk insert SQL-Alchemy Subclasses into the parent table and their respective tables ie fruits tables -> Apple Table and so I insert a table of APPLE and it will insert both the row ...
user avatar
0 votes
2 answers
82 views

Database design question: Tags with values(duration) linked to them

I am creating a database that takes in daily inputs such as: Date Events in that date (e.g. project foo: 5 hours, bar: 2 hours, etc...) Rating of the date There's more but these are probably the ...
user avatar
3 votes
2 answers
29k views

How to set a auto-increment value in SQLAlchemy?

This is my code : class csvimportt(Base): __tablename__ = 'csvimportt' #id = Column(INTEGER, primary_key=True, autoincrement=True) aid = Column(INTEGER(unsigned=True, zerofill=...
user avatar
0 votes
0 answers
58 views

How to "lock" a table for select

I have a table called assignments. A user will request an assignment from an API endpoint. I need to ensure that an assignment can only be handed to one user. There will be multiple users requesting ...
user avatar
0 votes
1 answer
6k views

Converting a date to a datetime in MySQL

I'm supposed to add time to a date field. We use MySQL with python3, alembic and sqlalchemy. Now I've written a conversion program. It is my first program of such a kind. I'm not certain how it will ...
user avatar
1 vote
0 answers
2k views

UUID default value on PSQL

I'm creating my database tables inside a flask application via SQLAlchemy. According to this answer, my code should look like this: id = db.Column(db.CHAR(36), primary_key=True, server_default=str("...
user avatar
  • 119
0 votes
1 answer
6k views

Why are my queries taking so long? [closed]

In order to quickly calculate some features for later calculations I have attempted to store a table that allows looking up distances between locations that have been pre-calculated. I am using Python ...
user avatar
1 vote
0 answers
321 views

Multiple many-to-many database relations

SO told me to ask here, so this is just copy-pasted from over there We're making a database of anime. Every anime can be listed as having been made by an arbitrary number of studios, and every studio ...
user avatar
2 votes
1 answer
727 views

Database relationship inheritance [closed]

I have a vehicle sales store. In my store I sale many kind of vehicles for example cars and boats. And I would like to keep track of the sales. My vehicles has some attributes as: Arrive on store ...
user avatar
  • 123
3 votes
0 answers
668 views

SELECT query blocking writes in postgresql

We have a web application that sends emails with a queue worker. Before an email is sent we write to a table: INSERT INTO user_email (user_id, email_key, sent_at) VALUES ( 175413579283955991, ...
user avatar
0 votes
1 answer
329 views

Use of MySQL trigger to insert row's week number

I have a table of orders in a MariaDB database that currently has a datetime column. For the dashboard I am building for this database, I have a statistic I would like to display which is "Orders This ...
user avatar
5 votes
3 answers
10k views

Performance of count(*) in subquery

Suppose we have the following queries: 1. SELECT COUNT(*) FROM some_big_table WHERE some_col = 'some_val' 2. SELECT COUNT(*) FROM ( SELECT * FROM some_big_table WHERE some_col = 'some_val' ...
user avatar
  • 165