logo
down
shadow

SQLAlchemy not including CTEs in DELETE


SQLAlchemy not including CTEs in DELETE

By : Iste Manit
Date : October 16 2020, 03:08 PM
I hope this helps . I'm attempting to utilize a CTE with a DELETE in SQLAlchemy (core). So far, my attempts have been unsuccessful whereby the CTE is not being included in the compiled SQL statement. Ultimately, this will be run against a PostgreSQL database, which supports this kind of statement. , In
code :
WITH ctetbl AS (
    SELECT name FROM foo
    WHERE id = 123
)
DELETE FROM foo WHERE foo.name = ctetbl.name
from sqlalchemy import *
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Delete, Update
import re

@compiles(Delete, 'postgresql')
def compile_delete(element, compiler, **kw):
    using_clause = None
    extra_froms = Update._extra_froms.__get__(element)

    if extra_froms:
        # Pre-compile extra_froms in order to populate CTEs before
        # compiling the delete statement itself
        using_clause = "USING %s" % ', '.join(
            compiler.process(fr, asfrom=True, **kw)
            for fr in extra_froms
        )

    text = compiler.visit_delete(element, **kw)

    if using_clause:
        # NOTE: This will blow up badly, if your CTEs also
        # contain DELETE statements.
        text = re.sub(
            r"(DELETE FROM \S+)",
            lambda m: "%s %s" % (m.group(1), using_clause),
            text
        )

    return text
delete_stmt = tbl.delete().where(tbl.c.name == mycte.c.name)
WITH ctetbl AS 
(SELECT foo.name AS name 
FROM foo
WHERE foo.id = %(id_1)s)
 DELETE FROM foo USING ctetbl WHERE foo.name = ctetbl.name
   ...: tbl_alias = tbl.alias()
   ...: delete_stmt = tbl.delete().\
   ...:     where(tbl.c.name == tbl_alias.c.name).\
   ...:     where(tbl_alias.c.id == 123)
   ...: 
   ...: print("Postgres:", delete_stmt.compile(dialect = postgresql.dialect()))
   ...: 
Postgres: DELETE FROM foo USING foo AS foo_1 WHERE foo.name = foo_1.name AND foo_1.id = %(id_1)s
   ...: delete_stmt = tbl.delete().where(tbl.c.name == mycte.select().as_scalar())
   ...: 
   ...: print("Postgres:", delete_stmt.compile(dialect = postgresql.dialect()))
Postgres: WITH ctetbl AS 
(SELECT foo.name AS name 
FROM foo 
WHERE foo.id = %(id_1)s)
 DELETE FROM foo WHERE foo.name = (SELECT ctetbl.name 
FROM ctetbl)


Share : facebook icon twitter icon
SQLAlchemy: Delete object directly from one-to-many relationship without using session.delete()

SQLAlchemy: Delete object directly from one-to-many relationship without using session.delete()


By : Danil Shepelev
Date : March 29 2020, 07:55 AM
I wish did fix the issue. Read Configuring delete/delete-orphan Cascade section of documentation for more information, but basically you need delete-orphan as well in your cascade option of the relationship:
code :
class Post(Base):
    # ...
    comments = relationship('Comment', cascade="all, delete-orphan")
DELETE using CTEs does not seem to work

DELETE using CTEs does not seem to work


By : user3073028
Date : March 29 2020, 07:55 AM
wish help you to fix your issue I have the following query which uses multiple CTE's: , You can do this two ways.
Use IN operator to delete the records.
code :
WITH Test
     AS (SELECT plan_id,
                Max("id") maxid
         FROM   Scheme
         GROUP  BY plan_id),
     Rec
     AS (SELECT rh.*
         FROM   Scheme rh,
                Test
         WHERE  rh.plan_id = Test.plan_id
                AND rh.id = Test.maxid
                AND rh.event_id = 20)
DELETE FROM Scheme
WHERE  id IN(SELECT a.id
             FROM   rec a);
WITH Test
     AS (SELECT plan_id,
                Max("id") maxid
         FROM   Scheme
         GROUP  BY plan_id),
     Rec
     AS (SELECT rh.*
         FROM   Scheme rh,
                Test
         WHERE  rh.plan_id = Test.plan_id
                AND rh.id = Test.maxid
                AND rh.event_id = 20)
DELETE FROM Scheme join rec using(id);
Dependent CTEs in SQLAlchemy? (example inside)

Dependent CTEs in SQLAlchemy? (example inside)


By : Sabine
Date : October 28 2020, 05:11 PM
it helps some times Here is a very simple example in PostgreSQL: , This works:
code :
from sqlalchemy import literal

a = session.query(literal(1).label('x')).cte('a')
b = session.query(a.c.x.label('y')).cte('b')
res = session.query(b).all()

assert res[0].y == 1
sqlalchemy delete function problem, I want to delete a single item, but it actually deletes the whole category?

sqlalchemy delete function problem, I want to delete a single item, but it actually deletes the whole category?


By : Nate Ryckman
Date : March 29 2020, 07:55 AM
will help you this is my github link , This is because you set your relationship with cascade delete-orphan.
code :
class Categories(Base):
    __tablename__ = 'categories'
    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship(User)

    @property
    def serialize(self):
        return {
            'name': self.name,
            'id': self.id,
        }


class CategoryItem(Base):
    __tablename__ = 'category_item'
    name = Column(String(80), nullable=False)
    id = Column(Integer, primary_key=True)
    description = Column(String(250))
    categories_id = Column(Integer, ForeignKey('categories.id'))
    categories = relationship(Categories, cascade="all, delete-orphan",
                              single_parent=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship(User)

    @property
    def serialize(self):
        return {
            'name': self.name,
            'description': self.description,
            'id': self.id,
        }
How to fix SQLAlchemy: SAWarning: DELETE statement on table expected to delete 1 row(s); 0 were matched

How to fix SQLAlchemy: SAWarning: DELETE statement on table expected to delete 1 row(s); 0 were matched


By : Chase
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I think your problem is related https://bitbucket.org/zzzeek/sqlalchemy/issues/2403/work-around-mysql-innodb-bug-need-per
You can try this code.
Related Posts Related Posts :
  • Can i use Docker for creating exe using pyinstaller
  • Reindexing data frame Pandas
  • Django-registration how to check if user exists
  • Attribute error when trying to get number of nodes
  • Sort list of list in Python according to a specific column
  • Crawling a page with python
  • How to open a Python 3 Program in another folder within Python 3
  • Format comments in vim starting with #' (hash-dash)
  • Boolean Addition Assignment in Python
  • with open(file, 'a+') as f is not appending
  • how to merge two files using python
  • Python canvas text position changes with font
  • Highlight a label in a legend, matplotlib
  • Numpy element-wise in operation
  • Python unittests run multiple times
  • Removing coordinates from list on python
  • What is the recommended way to work with a distutils-based setup.py of a C extension?
  • python html table data parsing
  • How To Call A Class Function From A Variable
  • ImportError: cannot import name 'IsolationForest'
  • Assign value to a column based of other columns from the same pandas dataframe
  • Error setting up Selenium on Ubuntu server 14.04, httplib.BadStatusLine: ''
  • read excel and convert index to datatimeindex pandas
  • Python: slicing a list into a list of lists using list comprehension
  • Python: what does "." in os.listdir(".") mean?
  • Two-Dimensional Arrays not updating correctly in python 3
  • Python running clock in separate window
  • Replacing duplicate item in list through raw_input
  • Why does this code using variable arguments give a TypeError?
  • Unindent does not match any outer indentation level in Python
  • Input parameter change as loop calls for another def (python 3.0), why?
  • Python sqlite3 generate unique identifier
  • how to test FCN(voc-fcn8s) on caffe python?
  • Assess whether duplicate samples have different data in their fields and whether to copy the data over?
  • How can I type hint an attribute in Python 3.5?
  • Bind many keys at the same time
  • Flask REST Blueprint not found
  • What is the easiest way to create a python list from a string that is in JSON format
  • Read a file .txt to build a dictionary
  • Get the value of a cell by row and column
  • How to convert unusual 24 hour date time format in python?
  • Get Outer Class Name for Nested Class (Python)
  • Why are many Python built-in/standard library functions actually classes
  • for i in range: TypeError: 'type' object is not iterable
  • Python SOAP client with Zeep - authentication
  • Django + mod_wsgi + apache2: ImportError: No module named <project>
  • Get coordinates from points density with KDE
  • Share global variable across python modules
  • how to divide two integers stored in variables then store the answer in an variable
  • Python, For loops depending on int
  • fcn should filters in deconv layers need to be trained?
  • Django - stop synchronisation between different variables based on filters on same object
  • Using python together with knitr
  • Difference between <type 'classobj'>, <type 'object'>?
  • what is top level module in Python?
  • Is there a query method or similar for pandas Series (pandas.Series.query())?
  • Deleting DataFrame row in Pandas where column value in list
  • Python Integer and String Using
  • Python requests: URL with percent character
  • Why ActionChains(driver).move_to_element(elem).click().perform() twice
  • shadow
    Privacy Policy - Terms - Contact Us © soohba.com