djrobstep / sqlbag

Handy python code for doing database things.

sqlbag: various sql boilerplate

This is just a collection of handy code for doing database things.

What is in the box

Connections, flask setup, SQLAlchemy ORM helpers, temporary database setup and teardown (handy for integration tests).

Installation

Simply install with pip:

$ pip install sqlbag

If you want you can install the database drivers you need at the same time, by specifying one of the optional bundles.

If you're using postgres, this installs sqlbag and psycopg2:

$ pip install sqlbag[pg]

If you're installing MySQL/MariaDB then this installs pymysql as well:

$ pip install sqlbag[maria]

sqlbag's Issues

Temporary database and pytest

I'm running into a permission error on windows when using the temp file in testing. Not sure if that's me or the implementation only considers the linux environment. (https://stackoverflow.com/questions/23212435/permission-denied-to-write-to-my-temporary-file)

Say I have some test

@patch("builtins.input", lambda _: "yes")
def test_sync_schema():
    dbh = DataBaseHandler("test", "sqlite:///:memory:")
    # add a new column so we have a migration safety issue when reverting back
    with dbh.engine.connect() as conn:
        conn.execute('ALTER TABLE "SomeTable" ADD COLUMN "test" INTEGER')
    # make sure drop column isnt just executed
    with pytest.raises(UnsafeMigrationException):
        sync_schema(dbh, safety=True, export=False)   # < ---------------------- ERROR OCCURS HERE
    # actually create it
    sync_schema(dbh, safety=False, export=False)

and then the sync_schema function basically looks like the one in the documentation

def sync_schema(dbh: DataBaseHandler, safety: bool = True, export: bool = True):
    if "postgresql" in dbh.url:
        dialect = "postgresql"
    else:
        dialect = "sqlite"

    with temporary_database(dialect=dialect) as temp_db_url:
        # migra schema will always be kept up to date with the current object_model
        migra_dbh = DataBaseHandler("migra", temp_db_url)

        with dbh.session_scope() as s_current, migra_dbh.session_scope() as s_target:
            m = migra.Migration(s_current, s_target)
            m.set_safety(safety)
            m.add_all_changes()

            if m.statements:
                print("THE FOLLOWING CHANGES ARE PENDING:", end="\n\n")
                print(m.sql)
                answer = input("Apply these changes? [yes/NO]").lower()
                if answer == "yes":
                    # the export stuff should be irrelevant, just saving the migration statements
                    if export:
                        p = (
                            Path(__file__).parent
                            / "schemas"
                            / f"{customer}_migrate.sql"
                        )
                        if p.is_file():
                            p.unlink()
                        with open(p, "a") as file:
                            file.write(str(m.sql))
                        dbh.get_sql_schema()
                    print("Applying...")
                    m.apply()

                else:
                    print("Not applying.")
            else:
                print("Already synced.")

I also tried using double nesting of temporary db (instead of the "sqlite://:memory:" in the test, but it throws the same error:

            finally:
                if not do_not_delete:
>                   os.remove(tmp.name)
E                    PermissionError: [WinError 32] The process cannot access the file, it's used by another process: 'C:\\Users\\~user\\AppData\\Local\\Temp\\tmp7dj3qb67'

Might also be my fault and I'm overlooking something.

pathlib dependency should be avoided in python3

This dependency effectively downgrades pathlib in newer versions of python.

It would be better to make this dependency conditional (unless python2 support can be dropped altogether).

SSL connect_args breaks with `S`

This line breaks: https://github.com/djrobstep/sqlbag/blob/master/sqlbag/sqla.py#L160

Because connect_args is a dict

Failing tests

Following test seem to fail, when run with the latest versions of all dependencies:

    "test_basic"
    "test_createdrop"
    "test_errors_and_messages"
    "test_flask_integration"
    "test_orm_stuff"
    "test_pendulum_for_time_types"
    "test_transaction_separation"

The issue seems to be caused by the same problems described in the migra issue.

BUG: Mutates dictionary while iterating over it

parse_interval_values in sqlbag/pg/datetimes.py mutates the dictionary while iterating over it when the key doesn't end in 's', which throws a "RuntimeError: dictionary keys changed during iteration".
For example, calling it with parse_interval_values('1 day') throws the error.

Doesn't work on windows due to `pwd`

pwd is used to find the current username when creating temporary databases.

Unfortunately this is unix-only. Need to swap it out with something platform-independent.

[Feature request] pass username and password to `temporary_database()`

I am trying to connect to postgres db hosted inside docker. Because I am connecting to it from outside docker I should specify username and password. But currently it seems that only the host can be passed to temporary_database().

Cannot connect to MySQL database without sqlbag trying to load postgres drivers.

Upon trying to perform a simple MySQL db connection, there were a number of errors as sqlbag tries to load the postgres drivers also. Will add a stacktrace soon.

SSL connect_args breaks with `S

Cannot install on a box without gcc - due to psycopg2

I'm building a fork of migra.
It includes schemainspect package.
That relays on sqlbag[pg].
That wants psycopg2 (not psycopg2-binary) in turn.

The box where I have to run the code has no gcc installed.

So, I see the crash in attempt to build psycopg2 (even having psycopg2-binary installed!).

This effectively prevents me from using migra.

Proposal: could you please replace psycopg2 with psycopg2-binary in your dependancies?

P.S. It's Python 3.10.12 in Ubuntu 22.04.3 LTS if it matters.

SQLAlchemy 1.4.0 makes URL object immutable

👋 Looks like this change in SQLAlchemy 1.4.0 is a problem: https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#the-url-object-is-now-immutable

    @contextmanager
    def admin_db_connection(db_url):
        url = copy_url(db_url)
        dbtype = url.get_dialect().name
    
        if dbtype == "postgresql":
>           url.database = ""
E           AttributeError: can't set attribute

.venv/lib/python3.8/site-packages/sqlbag/sqla.py:214: AttributeError