Questions tagged [sqlalchemy]

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

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

How do I set up a self referential relationship using postgresql and sqlalchemy

I have a table of Users, and for some of these users I have information on their parents. I would like to build a table structure so that I can call User.mother and User.father and return their ...
1
vote
0answers
7 views

Query not well formed in Sqlalchemy for select [migrated]

I am trying to generate the following raw query using sqlalchemy core: SELECT library.name, (SELECT ARRAY (select book.title from books where book....
5
votes
2answers
176 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 | ----------...
-2
votes
3answers
76 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....
0
votes
1answer
23 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, ...
0
votes
1answer
58 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 ...
0
votes
0answers
65 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 ...
1
vote
1answer
233 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)...
1
vote
1answer
162 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 ...
0
votes
1answer
101 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, ...
0
votes
1answer
79 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 ...
0
votes
1answer
1k 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 ...
0
votes
2answers
40 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 ...
2
votes
1answer
12k 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=...
0
votes
0answers
55 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 ...
0
votes
1answer
3k 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 ...
1
vote
0answers
1k 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("...
0
votes
1answer
4k 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 ...
1
vote
0answers
213 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 ...
2
votes
1answer
584 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 ...
3
votes
0answers
498 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, ...
0
votes
1answer
279 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 ...
5
votes
3answers
7k 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' ...