I have the following tables defined (very simplified version): I am using BigCommerce API and have multiple order_ids in both tables. The order_id is not unique globally but is unique per store. I am trying to work out how to link the two tables. I do have a Store table (shown below) that holds the store.id f…
Tag: sqlalchemy
Convert PostgreSQL COUNT … FILTER query to SQL Alchemy
I’m new to SQLAlchemy, and I would like to convert this PostgreSQL query: I have already tried this: Thank you in advance for your help Answer Thanks to @IljaEverilä’s comment, here is a more direct answer: (Original answer) I’m not sure if SQLAlchemy’s postgresql dialect specifically …
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 name: library Table name: books Now when I do query like below: The query generates output for library 1 & 2, but not for library 3. Why and how to solve this issue? (Generate an empty list…
Multithreading to load data to SQL tables?
On a weekly basis, I run a Python script that migrates data from a PostgreSQL server to a Teradata server. One table at a time, this script: DROP/CREATEs the Teradata version of the table, pulls the new data from PostgreSQL, saves a copy of the table data as a CSV in a network drive (for business reasons), ad…
query list of users after specified id in sqlalchemy.Table
Letss say that we have 10000 users on this data table. If the table is ordered by registration_date and I have a user id, how can I query the next 2 users after that id. e.g if this is the db table and I have the user id 1703, I want to get the next two users in the list.
SQL Update statement: OperationalError near “FROM”
Replacing box = with tb.box = shifts the error to the ‘.’: Error: Answer Actual implementation may not be fully adhered even with latest SQLite to support UPDATE-FROM. Specifically, docs do not indicate: JOIN in outer query is supported in UPDATE. FROM table should not repeat table in UPDATE. Tabl…
Best practice for scaling SQL queries on joins?
I’m writing a REST api that works with SQL and am constantly finding myself in similar situations to this one, where I need to return lists of objects with nested lists inside each object by querying over table joins. Let’s say I have a many-to-many relationship between Users and Groups. I have a …
SQL apply where clause to an arbitrary query results
I’m working on a system where the user introduces an SQL server/db connection and a valid SQL query, which I save on my system. I’m using python+sqlalchemy+pandas to accomplish this. That query will return a table like this one, in which the only rule is that the query result must have a timestamp…
SQLAlchemy: Filter the Objects From Relationship When Accessed
Query Statement: Get Children that have their Names start with ‘A’. Link Them With their Parents. Schema: I Have a One-to-Many Relationship. Parent(id, Name, children(rel->child)) -> Child(id, …
Tables being queried with SQL and Sqlalchemy have same column names
I am querying a variety of different tables in a mysql database with sqlalchemy, and sql query code. My issue right now is renaming some of the columns being joined. The queries are all coming into …