from pytest_mock_resources import (
create_mysql_fixture,
create_postgres_fixture,
create_redshift_fixture,
create_sqlite_fixture,
Statements,
)
statements = Statements("CREATE VIEW cool_view as select 3",
"CREATE VIEW cool_view_2 as select 1")
postgres = create_postgres_fixture(statements)
sqlite = create_sqlite_fixture(statements)
mysql = create_mysql_fixture(statements)
def test_statements_postgres(postgres):
execute = postgres.execute("""
SELECT table_name
FROM INFORMATION_SCHEMA.views
WHERE table_name in ('cool_view', 'cool_view_2')
ORDER BY table_name
""")
result = [row[0] for row in execute]
assert ["cool_view", "cool_view_2"] == result
def test_statements_mysql(mysql):
execute = mysql.execute("""
SELECT table_name
FROM INFORMATION_SCHEMA.views
class Quarter(Base):
__tablename__ = "quarter"
id = Column(Integer, primary_key=True)
year = Column(SmallInteger, nullable=False)
quarter = Column(SmallInteger, nullable=False)
rows = Rows(
Quarter(id=1, year=2012, quarter=1),
Quarter(id=2, year=2012, quarter=2),
Quarter(id=3, year=2012, quarter=3),
Quarter(id=4, year=2012, quarter=4),
)
postgres = create_postgres_fixture(rows)
mysql = create_mysql_fixture(rows)
def test_rows_postgres(postgres):
execute = postgres.execute("""
SELECT *
FROM quarter
ORDER BY id
""")
assert [(1, 2012, 1), (2, 2012, 2), (3, 2012, 3),
(4, 2012, 4)] == list(execute)
def test_rows_mysql(mysql):
execute = mysql.execute("""
rows = Rows(User(name="Harold"), User(name="Gump"))
row_dependant_statements = Statements(
"CREATE TEMP TABLE user1 as SELECT DISTINCT CONCAT(name, 1) as name FROM stuffs.user"
)
additional_rows = Rows(User(name="Perrier"), User(name="Mug"))
def session_function(session):
session.add(User(name="Fake Name", objects=[Object(name="Boots")]))
postgres_ordered_actions = create_postgres_fixture(rows,
row_dependant_statements,
additional_rows)
postgres_session_function = create_postgres_fixture(Base, session_function)
# Run the test 5 times to ensure fixture is stateless
@pytest.mark.parametrize("run", range(5))
def test_ordered_actions(postgres_ordered_actions, run):
execute = postgres_ordered_actions.execute("SELECT * FROM user1")
result = sorted([row[0] for row in execute])
assert ["Gump1", "Harold1"] == result
# Run the test 5 times to ensure fixture is stateless
@pytest.mark.parametrize("run", range(5))
class Quarter(PGBase):
__tablename__ = "quarter"
__table_args__ = {"schema": "public"}
id = Column(Integer, primary_key=True)
year = Column(SmallInteger, nullable=False)
quarter = Column(SmallInteger, nullable=False)
class Report(PGBase):
__tablename__ = "report"
id = Column(Integer, primary_key=True)
pg_implicit_schema = create_postgres_fixture(PGBase, tables=["report"])
pg_explicit_schema = create_postgres_fixture(PGBase, tables=["public.quarter"])
class TestPg:
def test_implicit_schema(self, pg_implicit_schema):
pg_implicit_schema.execute("select * from report")
with pytest.raises(sqlalchemy.exc.ProgrammingError):
pg_implicit_schema.execute("select * from public.quarter")
def test_explicit_schema(self, pg_explicit_schema):
pg_explicit_schema.execute("select * from quarter")
with pytest.raises(sqlalchemy.exc.ProgrammingError):
pg_explicit_schema.execute("select * from report")
import pytest
from sqlalchemy import create_engine, text
from pytest_mock_resources import (
compat,
create_mysql_fixture,
create_postgres_fixture,
create_redshift_fixture,
create_sqlite_fixture,
)
from pytest_mock_resources.fixture.database.relational.generic import EngineManager
sqlite = create_sqlite_fixture()
postgres = create_postgres_fixture()
redshift = create_redshift_fixture()
mysql = create_mysql_fixture()
def test_basic_sqlite_fixture(sqlite):
sqlite.execute("select 1")
def test_basic_postgres_fixture(postgres):
postgres.execute("select 1")
def test_basic_redshift_fixture(redshift):
redshift.execute("select 1")
def test_basic_postgres_and_redshift_fixture(postgres, redshift):
import pytest
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL
from pytest_mock_resources import (create_postgres_fixture,
create_redshift_fixture,
create_sqlite_fixture, create_mysql_fixture)
from pytest_mock_resources.fixture.database.relational.generic import EngineManager
sqlite = create_sqlite_fixture()
postgres = create_postgres_fixture()
redshift = create_redshift_fixture()
mysql = create_mysql_fixture()
def test_basic_sqlite_fixture(sqlite):
sqlite.execute("select 1")
def test_basic_postgres_fixture(postgres):
postgres.execute("select 1")
def test_basic_redshift_fixture(redshift):
redshift.execute("select 1")
def test_basic_postgres_and_redshift_fixture(postgres, redshift):
postgres.execute("select 1")
redshift.execute("select 1")
def test_create_all(sqlite):
Base.metadata.create_all(bind=sqlite)
result = list(sqlite.execute("select * from other.thing"))
assert result == [(1, "foo")]
def test_foreign_keys(sqlite):
sqlite.execute("INSERT INTO other.fk (id, thing_id) VALUES (1, 1)")
with pytest.raises(sqlalchemy.exc.IntegrityError):
sqlite.execute("INSERT INTO other.fk (id, thing_id) VALUES (2, 2)")
pg = create_postgres_fixture(Base, Rows(Thing(id=1, name="foo")))
def test_json_column(sqlite):
sqlite.execute(column_types_table.insert().values(id=1, json={"foo": 2}))
result = sqlite.execute(sqlalchemy.select([column_types_table.c.json
])).scalar()
assert result == {"foo": 2}
@pytest.mark.postgres
def test_json_column_pg(pg):
"""Included here as a way of showing that this mimics postgres' behavior
"""
pg.execute(column_types_table.insert().values(id=1, json={"foo": 2}))
result = pg.execute(sqlalchemy.select([column_types_table.c.json
"most_options",
superuser=True,
createdb=True,
createrole=True,
inherit=True,
login=True,
replication=True,
bypass_rls=True,
connection_limit=1,
valid_until=datetime(2999, 1, 1),
in_roles=["nooptions"],
),
)
pg = create_postgres_fixture()
def test_createall_role(pg):
Base.metadata.create_all(bind=pg)
result = pg.execute(
text(
"SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolconnlimit, rolvaliduntil, rolbypassrls, rolconfig FROM pg_roles WHERE rolname NOT LIKE :pg_name and rolname != :connected_name"
),
pg_name="pg_%",
connected_name=pg.pmr_credentials.username,
).fetchall()
expected_result = [
("nooptions", False, True, False, False, False, False, -1, None, False, None),
(
from sqlalchemy.ext.declarative import declarative_base
from pytest_mock_resources import create_postgres_fixture
from pytest_mock_resources.container.postgres import get_sqlalchemy_engine
from pytest_mock_resources.fixture.database.relational.postgresql import _create_clean_database
Base = declarative_base()
class Thing(Base):
__tablename__ = "thing"
id = Column(Integer, autoincrement=True, primary_key=True)
createdb_template_pg = create_postgres_fixture(Base,
createdb_template="template0")
def test_create_clean_database_createdb_template(pmr_postgres_config,
createdb_template_pg):
"""Assert `createdb_template` is included in emitted CREATE DATABASE statement."""
root_engine = get_sqlalchemy_engine(pmr_postgres_config,
pmr_postgres_config.root_database,
isolation_level="AUTOCOMMIT")
statement = ""
def before_execute(conn, clauseelement, multiparams, params,
execution_options):
# Search for our create database statement, so we can assert against it.
if "CREATE DATABASE" in clauseelement:
import pytest
from pytest_mock_resources import create_postgres_fixture
alembic_engine = create_postgres_fixture()
@pytest.fixture
def alembic_config():
return {
"before_revision_data": {
"bbbbbbbbbbbb": {
"__tablename__": "meow.foo",
"id": 9
}
}
}