Questions tagged [sqlalchemy]
Use this tag for the eponymous Python SQL toolkit and Object Relational Mapper.
39 questions
0
votes
0
answers
30
views
Should one stick to 1NF (and higher NF) rules for SQL even while using ORMs?
I am currently making a database in MySQL using the SQLAlchemy ORM. I followed the principles of 1NF until this point (atomised Data, no redundancy) but now I am tempted to add a few useful (though ...
0
votes
1
answer
315
views
PostgreSQL driven by SQLAlchemy - Release Savepoint Idle in Transaction
While running PostgreSQL 13.12 (occurs in several versions of PG11/PG13) using SQLAlchemy 1.3, we are occasionally hitting issues where increased concurrency leaves certain transactions (and their ...
- 50
0
votes
1
answer
137
views
Minified vs. pretty XML
I have a text field in a PostgreSQL Database in which XML data is stored.
Now I was wondering if it makes a difference to store the XML in minified or in pretty version.
According to http://...
- 103
0
votes
1
answer
56
views
Checking for already present data before commiting?
I'm following a simple youtube course on how to build a FastAPI app with a Postgres db.
The Users table has the following schema (using SQLAlchemy):
class User(Base):
"""
Class ...
0
votes
0
answers
424
views
Sudden momentary dips in aws rds free storage space available
I have been experiencing some sudden momentary dips in the available aws rds free storage space available. Once the free space dips it goes into the storage optimization mode.
I have a postgres table ...
- 1
0
votes
1
answer
157
views
Proper way of constructing database of historical datasets
I've been attempting to learn how to build a database of historical sports statistics using postgresql/sqlalchemy ORM the past few days and could really use some advice on approach. My use case seems ...
- 101
2
votes
2
answers
428
views
Operational Error on MySQL AWS RDS Instance
The heading pretty much describes it all, here are some more information on the Amazon RDS I am using.
Engine: MySQL Community 8.0.28
Class db.r6g.large
Multi-AZ no. The data volume is very small, ...
- 73
0
votes
0
answers
188
views
Unable to connect to sql server on Ubuntu after MySQLAlchemy connection
Context:
I have installed mysql-server on Ubuntu and was able to setup password for root account and also created a few accounts with all privileges. I was able to connect to any user or the root user ...
0
votes
0
answers
119
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
249
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}
...
1
vote
1
answer
2k
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
2k
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
46
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
2k
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
184
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
2k
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
237
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
701
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 ...
- 53
6
votes
2
answers
2k
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 |
----------...
- 219
-2
votes
3
answers
83
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
100
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, ...
- 125
2
votes
1
answer
817
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 ...
- 21
0
votes
0
answers
827
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
3k
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
1k
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
891
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
600
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
171
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 ...
5
votes
2
answers
62k
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
1
answer
10k
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
4k
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
8k
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
433
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
835
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
1k
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
439
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
8
votes
3
answers
13k
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' ...
- 195
1
vote
2
answers
2k
views
How to cache queries in sqlalchemy just like I do it in mysql by using SQL_CACHE keyword?
I want to cache sqlalchemy, I have discovered that using keyword SQL_CACHE in mysql can be helpful in caching queries on demand.
But how do I do it in sqlalchemy?
Is it possible?
