Feature Request: Drop table if exists

Explain why petl needs this feature?

Hello guys. I'm using petl to regularly transfer data between to databases, i.e. MariaDB and PosgreSQL. It is part of a warehousing process, so I frequently drop and recreate aggregated tables. As they sometimes change (columns are added or dropped) it is more convenient to have a logic that drops target tables and recreates them depending on the transformed data. Now it seems that the current method covers the scenarios:

the table does not exist, so create it, i.e. todb(..., create=True)
the table exists, so truncate it, i.e. todb(..., create=False)
the table exists, so drop it and then create a new one todb(..., drop=True, create=True)

uncovered scenario (according to my knowledge):

if the table exists, drop it and finally create a new one.

Describe the solution you would like

The method petl.io.db.todb() could have an drop: {None, ‘force’, 'if_exists'}, default None parameter which replaces the current drop parameter. This would allow for full coverage of the above scenarios:

the table does not exist, so create it, ie. todb(..., create=True)
the table exists, so truncate it, ie. todb(..., create=False)
the table exists, so drop it and then create a new one todb(..., drop='force', create=True)
if the table exists, drop it and finally create a new one todb(..., drop='if_exists', create=True)

Describe alternatives solutions you would have considered

The alternative I am using today to simulate the wanted behavior is rather ugly:

if transfer_mode == Mode.create:
    try:   # first try dropping the existing table
        petl.todb(
            **petl_kwargs,
            drop=True,  # raises error
            create=True
        )
    except:  # if the table doesn't exist
        petl.todb(**petl_kwargs, create=True)
elif transfer_mode == Mode.replace:
    petl.todb(**petl_kwargs)
elif transfer_mode == Mode.append:
    petl.appenddb(**petl_kwargs)

Source Code Examples

def todb(table, dbo, tablename, schema=None, commit=True,
         create=False, drop=None, constraints=True, metadata=None,
         dialect=None, sample=1000):

    needs_closing = False

    # convenience for working with sqlite3
    if isinstance(dbo, string_types):
        import sqlite3
        dbo = sqlite3.connect(dbo)
        needs_closing = True

    try:
        if create:
            if drop:
                if_exists = True if drop=='if_exists' else False
                drop_table(dbo, tablename, schema=schema, commit=commit, if_exists=if_exists)
            create_table(table, dbo, tablename, schema=schema, commit=commit,
                         constraints=constraints, metadata=metadata,
                         dialect=dialect, sample=sample)
        _todb(table, dbo, tablename, schema=schema, commit=commit,
              truncate=True)

    finally:
        if needs_closing:
            dbo.close()

Table.todb = todb

###

def drop_table(dbo, tablename, schema=None, commit=True, if_exists=False):
    """
    Drop a database table.

    Keyword arguments:

    dbo : database object
        DB-API 2.0 connection, callable returning a DB-API 2.0 cursor, or
        SQLAlchemy connection, engine or session
    tablename : text
        Name of the table
    schema : text
        Name of the database schema the table is in
    commit : bool
        If True commit the changes
    if_exists : bool
        If True, don't raise error if the table doesn't exist
    """

    # sanitise table name
    tablename = _quote(tablename)
    if schema is not None:
        tablename = _quote(schema) + '.' + tablename

    if if_exists:
        sql = u'DROP TABLE IF EXISTS %s' % tablename
    else: 
        sql = u'DROP TABLE %s' % tablename
    _execute(sql, dbo, commit)

Additional Notes

No response

Code of Conduct

[X] I agree to follow this project's Code of Conduct

petl-developers / petl