<p dir="auto"><strong>Migrated issue, originally created by Rémy Roy (<a href="https://github.com/remyroy">@remyroy</a>)</strong></p>
<p dir="auto">IF EXISTS and IF NOT EXISTS are already part of many DBMSs DDL vocabulary. They have many use cases which are highly useful in some situations.</p>
<p dir="auto">For instance, creating a table if it does not already exists or dropping a column if it exists.</p>
<p dir="auto">It would be nice to have those directives with standard SQLAlchemy DDL methods. I guess they could be implemented using the native support for DBMSs that support them or with some introspection for those that do not.</p>
<h3 dir="auto">Describe the bug</h3>
<p dir="auto">When using <strong>SQLAlchemy</strong> with <strong>PyMySQL</strong> driver and attempting to add a conflicting resource which is unique by the name, <code class="notranslate">pymysql.err.IntegrityError</code> is being raised, where instead <code class="notranslate">SQLAlchemy.exc.IntegrityError</code>  should be wrapping the driver exception.</p>
<h3 dir="auto">To Reproduce</h3>
<h4 dir="auto">Model</h4>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="
class Test(CustomModel):
    __tablename__ = &quot;tests&quot;

    id = Column(Integer, primary_key=True)
    name = Column(String(255), unique=True, nullable=False)

    def __init__(self, **kwargs) -&gt; None:
        self.name = kwargs['name']"><pre class="notranslate"><span class="pl-k">class</span> <span class="pl-v">Test</span>(<span class="pl-v">CustomModel</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"tests"</span>

    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">name</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">String</span>(<span class="pl-c1">255</span>), <span class="pl-s1">unique</span><span class="pl-c1">=</span><span class="pl-c1">True</span>, <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">False</span>)

    <span class="pl-k">def</span> <span class="pl-en">__init__</span>(<span class="pl-s1">self</span>, <span class="pl-c1">**</span><span class="pl-s1">kwargs</span>) <span class="pl-c1">-&gt;</span> <span class="pl-c1">None</span>:
        <span class="pl-s1">self</span>.<span class="pl-s1">name</span> <span class="pl-c1">=</span> <span class="pl-s1">kwargs</span>[<span class="pl-s">'name'</span>]</pre></div>
<h4 dir="auto">Database init</h4>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="class DatabaseManager:
    &quot;&quot;&quot;Manage database sessions and model creation.&quot;&quot;&quot;

    def __init__(self, config: dict) -&gt; None:
        self.uri = '{}://{}:{}@{}:{}/{}'.format(
            config.drivers,
            config.user,
            config.password,
            config.host,
            config.port,
            config.name
        )

        self.engine = sqlalchemy.create_engine(self.uri)
        self.session = scoping.scoped_session(
            sessionmaker(
                bind=self.engine,
                autocommit=False
            )
        )

    @property
    def session(self) -&gt; object:
        &quot;&quot;&quot;Retrieve database session.&quot;&quot;&quot;
        return self._session

    @session.setter
    def session(self, value: object) -&gt; None:
        &quot;&quot;&quot;Set default database session.&quot;&quot;&quot;
        self._session = value"><pre class="notranslate"><span class="pl-k">class</span> <span class="pl-v">DatabaseManager</span>:
    <span class="pl-s">"""Manage database sessions and model creation."""</span>

    <span class="pl-k">def</span> <span class="pl-en">__init__</span>(<span class="pl-s1">self</span>, <span class="pl-s1">config</span>: <span class="pl-s1">dict</span>) <span class="pl-c1">-&gt;</span> <span class="pl-c1">None</span>:
        <span class="pl-s1">self</span>.<span class="pl-s1">uri</span> <span class="pl-c1">=</span> <span class="pl-s">'{}://{}:{}@{}:{}/{}'</span>.<span class="pl-en">format</span>(
            <span class="pl-s1">config</span>.<span class="pl-s1">drivers</span>,
            <span class="pl-s1">config</span>.<span class="pl-s1">user</span>,
            <span class="pl-s1">config</span>.<span class="pl-s1">password</span>,
            <span class="pl-s1">config</span>.<span class="pl-s1">host</span>,
            <span class="pl-s1">config</span>.<span class="pl-s1">port</span>,
            <span class="pl-s1">config</span>.<span class="pl-s1">name</span>
        )

        <span class="pl-s1">self</span>.<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-en">create_engine</span>(<span class="pl-s1">self</span>.<span class="pl-s1">uri</span>)
        <span class="pl-s1">self</span>.<span class="pl-s1">session</span> <span class="pl-c1">=</span> <span class="pl-s1">scoping</span>.<span class="pl-en">scoped_session</span>(
            <span class="pl-en">sessionmaker</span>(
                <span class="pl-s1">bind</span><span class="pl-c1">=</span><span class="pl-s1">self</span>.<span class="pl-s1">engine</span>,
                <span class="pl-s1">autocommit</span><span class="pl-c1">=</span><span class="pl-c1">False</span>
            )
        )

    <span class="pl-en">@<span class="pl-s1">property</span></span>
    <span class="pl-k">def</span> <span class="pl-en">session</span>(<span class="pl-s1">self</span>) <span class="pl-c1">-&gt;</span> <span class="pl-s1">object</span>:
        <span class="pl-s">"""Retrieve database session."""</span>
        <span class="pl-k">return</span> <span class="pl-s1">self</span>.<span class="pl-s1">_session</span>

    <span class="pl-en">@<span class="pl-s1">session</span>.<span class="pl-s1">setter</span></span>
    <span class="pl-k">def</span> <span class="pl-en">session</span>(<span class="pl-s1">self</span>, <span class="pl-s1">value</span>: <span class="pl-s1">object</span>) <span class="pl-c1">-&gt;</span> <span class="pl-c1">None</span>:
        <span class="pl-s">"""Set default database session."""</span>
        <span class="pl-s1">self</span>.<span class="pl-s1">_session</span> <span class="pl-c1">=</span> <span class="pl-s1">value</span></pre></div>
<h4 dir="auto">Add object</h4>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="        test = Test(**data)

        session = app.db.session()

        try:
            session.add(test)
        except exc.IntegrityError as error:
            app.logger.error(&quot;Object '%s' already exists in the database.&quot;, object_name)
            raise exceptions.Conflict(f&quot;Object '{object_name}' already exists.&quot;) from error
        else:
            session.commit()"><pre class="notranslate">        <span class="pl-s1">test</span> <span class="pl-c1">=</span> <span class="pl-v">Test</span>(<span class="pl-c1">**</span><span class="pl-s1">data</span>)

        <span class="pl-s1">session</span> <span class="pl-c1">=</span> <span class="pl-s1">app</span>.<span class="pl-s1">db</span>.<span class="pl-en">session</span>()

        <span class="pl-k">try</span>:
            <span class="pl-s1">session</span>.<span class="pl-en">add</span>(<span class="pl-s1">test</span>)
        <span class="pl-k">except</span> <span class="pl-s1">exc</span>.<span class="pl-v">IntegrityError</span> <span class="pl-k">as</span> <span class="pl-s1">error</span>:
            <span class="pl-s1">app</span>.<span class="pl-s1">logger</span>.<span class="pl-en">error</span>(<span class="pl-s">"Object '%s' already exists in the database."</span>, <span class="pl-s1">object_name</span>)
            <span class="pl-k">raise</span> <span class="pl-s1">exceptions</span>.<span class="pl-v">Conflict</span>(<span class="pl-s">f"Object '<span class="pl-s1"><span class="pl-kos">{</span><span class="pl-s1">object_name</span><span class="pl-kos">}</span></span>' already exists."</span>) <span class="pl-k">from</span> <span class="pl-s1">error</span>
        <span class="pl-k">else</span>:
            <span class="pl-s1">session</span>.<span class="pl-en">commit</span>()</pre></div>
<h3 dir="auto">Error</h3>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="[2022-03-19 11:38:24,615] ERROR in exceptions: An exception has occurred internally. Reason: '(pymysql.err.IntegrityError) (1062, &quot;Duplicate entry 'testobj' for key 'name_index'&quot;)
[SQL: INSERT INTO tests (name) VALUES (%(name)s)]
[parameters: {'name': 'testobj'}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)'"><pre class="notranslate"><code class="notranslate">[2022-03-19 11:38:24,615] ERROR in exceptions: An exception has occurred internally. Reason: '(pymysql.err.IntegrityError) (1062, "Duplicate entry 'testobj' for key 'name_index'")
[SQL: INSERT INTO tests (name) VALUES (%(name)s)]
[parameters: {'name': 'testobj'}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)'
</code></pre></div>
<h3 dir="auto">Versions</h3>
<ul dir="auto">
<li>OS: Ubuntu 20.04.4 LTS</li>
<li>Python: Python 3.7.12 (default, Feb  8 2022, 11:05:33)</li>
<li>SQLAlchemy: SQLAlchemy==1.4.32</li>
<li>Database: 10.5.5-MariaDB-1:10.5.5+maria~focal</li>
<li>DBAPI (eg: psycopg, cx_oracle, mysqlclient): PyMySQL==1.0.2</li>
</ul>
<h3 dir="auto">Additional context</h3>
<p dir="auto"><em>No response</em></p>