Questions tagged [sqlalchemy]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
123 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 ...
Justin Lowen's user avatar
0 votes
0 answers
18 views

One to one Modeling?

What do you guys think of this model? Catalog -< Catalog Items | - | Products and Catalog -< Catalog Items | - | Services That is, a Catalog Item, can be one and only one type: Product or ...
Cliff Ribeiro's user avatar
0 votes
1 answer
57 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://...
bechtold's user avatar
  • 103
0 votes
0 answers
220 views

Too many connections issue with postgres and multiprocessing

I am trying to read and write to a db in the following way: with open("argument_file.txt", "r") as f: arguments = f.read().split("\n") engine = create_engine('...
Andreas Haupt's user avatar
0 votes
1 answer
41 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 ...
An old man in the sea.'s user avatar
0 votes
0 answers
300 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 ...
dev_998's user avatar
0 votes
1 answer
68 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 ...
Nick's user avatar
  • 101
2 votes
2 answers
352 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, ...
Della's user avatar
  • 73
0 votes
0 answers
150 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 ...
Rookie_Coder2318's user avatar
0 votes
0 answers
103 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 ...
Technaton's user avatar
  • 171
0 votes
1 answer
186 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} ...
Minura Punchihewa's user avatar
1 vote
1 answer
1k 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 ...
R. Gosman's user avatar
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) ...
W. Hunter Giles's user avatar
0 votes
1 answer
44 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, ...
ARNON's user avatar
  • 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). ...
Liam's user avatar
  • 11
0 votes
0 answers
173 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 ...
Ethan Hill's user avatar
0 votes
2 answers
1k 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 ...
EspiFreelancer's user avatar
0 votes
1 answer
166 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 ...
Judy T Raj's user avatar
-1 votes
1 answer
643 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 ...
apple212's user avatar
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 | ----------...
PaxPrz's user avatar
  • 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....
user1795999's user avatar
0 votes
1 answer
78 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, ...
Давид Шико's user avatar
2 votes
1 answer
730 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 ...
martinktm's user avatar
0 votes
0 answers
676 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 ...
noslenkwah's user avatar
1 vote
1 answer
2k 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)...
some_programmer's user avatar
1 vote
1 answer
930 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 ...
smcs's user avatar
  • 113
0 votes
1 answer
752 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, ...
WiseSQLHost's user avatar
0 votes
1 answer
506 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 ...
Devesh Krishnani's 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 ...
Nilesh Kesar's user avatar
0 votes
2 answers
134 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 ...
Librarian's user avatar
5 votes
2 answers
58k 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=...
Farhaan Patel's user avatar
0 votes
1 answer
9k 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 ...
Niklas Rosencrantz's user avatar
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("...
codeninja's user avatar
  • 119
0 votes
1 answer
7k 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 ...
harrison's user avatar
1 vote
0 answers
420 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 ...
Kahr Kunne's user avatar
2 votes
1 answer
815 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 ...
Lin's user avatar
  • 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, ...
Michael Mattax's user avatar
0 votes
1 answer
420 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 ...
smallpants's user avatar
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' ...
McSonk's user avatar
  • 195