Questions tagged [sqlalchemy]
Use this tag for the eponymous Python SQL toolkit and Object Relational Mapper.
35
questions
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" ...
- 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 ...
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 ...
- 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}
...
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,
...
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 ...
- 23
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 ...
- 11
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) ...
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,
...
- 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). ...
- 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 ...
- 101
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 ...
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 ...
- 103
-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 ...
- 43
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 |
----------...
- 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....
- 111
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, ...
- 115
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 ...
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 ...
- 101
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)...
- 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 ...
- 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,
...
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 ...
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 ...
- 121
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 ...
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=...
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 ...
- 225
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 ...
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("...
- 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 ...
- 3
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 ...
- 13
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 ...
- 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,
...
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 ...
- 101
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' ...
- 165