I am adding JSON string to Postgres jsonb column in python using the sqlalchemy library. Code to create json string is Code to add to postgresql database is: My JSON is printed properly in logs But when it goes to database the json is enclosed in a string and an escape character is added in front of every dou…
Tag: sqlalchemy
How to union two counts queries in SQLAlchemy?
I have two queries and the only difference between then is that one is counting the success status and the other failure status. Is there a way to get this result in just one query? I’m using SQLALchemy to do the queries. Answer You can use conditions on Count, your query will look like:
Case in Sqlalchemy hybrid_property / expression error
I want to use expressions is ORM queries like this. Profile.query.filter(Profile.complete).all() Where complete is a calculated field Sqlalchemy docs shows the below So i’ve attempted to copy it with the following. But the output sql query is as below – the case isn’t compared agaisnt anythi…
Best way to check if database already exists with flask-sqlalchemy, otherwise create
I have a database app, and want to create the tables and database on the fly when starting the app for the first time. If the app is restarted, it should only create the database and tables if they do not exist, otherwise ignore this creation. I have a few questions. 1.) Is this a proper way of dealing with
sqlite query based on selecting from column string and filtering by last rows in descending order
I have a database, tick_df, that looks like this I am trying to select all columns and the last 2 rows in descending time order from only one symbol – e.g.AVAX/USD:USD. The query I have tried is But this return an error can anyone point to what I’m doing wrong here. Thanks Answer If you look at th…
Can’t connect Sqlalchemy with pyodbc to SQL Server 2000
I followed this website by installing After install I try this code and it worked, it print all records from table2 However, I want to use SQLAlchemy with pyodbc and it does not work ProgrammingError: (pyodbc.ProgrammingError) (‘42000′, “[42000] [FreeTDS][SQL Server]’schema_name’…
SQLAlchemy group by day in timestamp column
I have an ORM (Model) defined in SQLAlchemy as below: I’m planning to get the sum of all views in each day and tried to group the results based on their end_to. I have used the following query in sqlalchemy: But this query throws this error: I am using the timestamp in my model and I don’t plan to…
Querying JSON in POSTGRESQL returns NONE everytime
jsonb column text_results contains values in the following form : {‘Name’ : ‘john doe’ , ‘id’ : ‘123’} On querying Select text_results->>’Name’ as Name from user_master or Select json_extract_path_text(text_results::json,’Name’) as …
How to find the max values of each columns using a single SQL query
I have a table, I want to get all the max values of col1, col2, col3 using one query only. I’m using sqlite and flask-sqlalchemy. I tried but then I got [(88,), (30,), (75,), (93,)] I want the output to be [(93,), (88,), (75,)] How can I do this? Answer I think you want three separate calls to MAX here:
Why doesn’t SQLite appear to lock my table / row?
I’m tinkering with an in-memory sqlite database, trying to wrap my head around concurrency. Here’s an example using SQLAlchemy OUTPUT Here, I use two connections to write data to the same record. I would expect the output to be “A112”, because I thought the first connection would have …