def test_schema_model_multiple_bases(self): """ Multiple declarative bases on same Connector should fail. """ BaseX = declarative_base() BaseY = declarative_base() class Cats(BaseX): __tablename__ = 'cat' id = Column(Integer, primary_key=True) name = Column(String(250)) class Dogs(BaseY): __tablename__ = 'dog' id = Column(Integer, primary_key=True) name = Column(String(250)) db_file = "{}/pets.db".format(self.working_directory()) c = SqlAlchemyDatabaseConnector(engine_url=f"sqlite:///{db_file}", schema_model=[Cats, Dogs], access=ayeaye.AccessMode.READWRITE ) with self.assertRaises(ValueError) as context: c.connect() self.assertIn("Models passed to `schema_model` must share the same declarative base", str(context.exception) )
def test_add_orm_data_single(self): c = SqlAlchemyDatabaseConnector(engine_url="sqlite://", schema_builder=people_schema, access=ayeaye.AccessMode.READWRITE ) c.create_table_schema() c.add({'surname': 'Cornwallis'}) c.add({'surname': 'Brunel'}) c.commit() # read them back all_the_people = [f"{p.id} {p.surname}" for p in c] expected = "1 Cornwallis 2 Brunel" self.assertEqual(expected, " ".join(all_the_people))
def test_schema_builder_model_exclusive(self): PeoplCls = people_schema(declarative_base=declarative_base()) with self.assertRaises(ValueError): SqlAlchemyDatabaseConnector(engine_url="sqlite:////tmp/wontbecreated.db", schema_builder=people_schema, schema_model=PeoplCls, access=ayeaye.AccessMode.READWRITE )
def test_schema_access_single(self): """ One ORM model in schema has different way of accessing with .schema """ c = SqlAlchemyDatabaseConnector(engine_url="sqlite://", schema_builder=people_schema, ) # method resolution order super_classes = [cc.__name__ for cc in c.schema.mro()] expected = ['Person', 'Base', 'object'] # class above, declarative base, py obj self.assertEqual(expected, super_classes)
def test_schema_access_multiple(self): """ list of ORM models in schema """ c = SqlAlchemyDatabaseConnector(engine_url="sqlite://", schema_builder=fruit_schemas, ) # method resolution order super_classes = [cc.__name__ for cc in c.schema.Pear.mro()] expected = ['Pear', 'Base', 'object'] # class above, declarative base, py obj self.assertEqual(expected, super_classes)
def test_schema_model_single(self): """ Instead of passing a callable (i.e. schema_builder argument) pass an SqlAlchemy model which already has a declarative base. """ Base = declarative_base() class Rodents(Base): __tablename__ = 'rodent' id = Column(Integer, primary_key=True) species = Column(String(250), nullable=False) db_file = "{}/rodents.db".format(self.working_directory()) rodents = SqlAlchemyDatabaseConnector(engine_url=f"sqlite:///{db_file}", schema_model=Rodents, access=ayeaye.AccessMode.READWRITE ) rodents.create_table_schema() rodents.add({'species': 'Yellow-necked mouse'}) rodents.commit() rodents.close_connection()
def test_sql_direct(self): """ SQL queries without SqlAlchemy ORM models. """ c = SqlAlchemyDatabaseConnector(engine_url="sqlite://") c.sql("CREATE TABLE nice_colours (colour varchar(20))") c.sql("INSERT INTO nice_colours values ('blue'), ('green'), ('black')") results = c.sql("SELECT colour FROM nice_colours where colour <> :not_really_a_colour", not_really_a_colour='black' ) final_colours = set() for r in results: final_colours.add(dict(r)['colour']) assert set(['blue', 'green']) == final_colours
def test_create_db_schema(self): c = SqlAlchemyDatabaseConnector(engine_url="sqlite://", schema_builder=fruit_schemas, access=ayeaye.AccessMode.WRITE, ) c.connect() # check there aren't any tables in the DB here with self.assertRaises(OperationalError) as context: c.session.query(c.schema.Pear).all() self.assertIn("no such table: pear", str(context.exception)) c.create_table_schema() # but there are tables now (but no data in them) all_the_pears = c.session.query(c.schema.Pear).all() self.assertIsInstance(all_the_pears, list) self.assertEqual(0, len(all_the_pears))
def test_schema_model_multiple(self): """ see :method:`` but with a list, same idea as how the schema_builder argument can return a single schema or list. """ Base = declarative_base() class Cats(Base): __tablename__ = 'cat' id = Column(Integer, primary_key=True) name = Column(String(250)) class Dogs(Base): __tablename__ = 'dog' id = Column(Integer, primary_key=True) name = Column(String(250)) db_file = "{}/pets.db".format(self.working_directory()) pets = SqlAlchemyDatabaseConnector(engine_url=f"sqlite:///{db_file}", schema_model=[Cats, Dogs], access=ayeaye.AccessMode.READWRITE ) pets.create_table_schema() with self.assertRaises(ValueError) as context: pets.add({'name': 'Lady'}) self.assertIn("Dictionary can only be used in single schema mode", str(context.exception)) pets.add(pets.schema.Cats(name="Lady")) pets.add(pets.schema.Dogs(name="Lady")) pets.commit() pets.close_connection()
def test_two_databases(self): """ The declarative base is created by each SqlAlchemyDatabaseConnector. Ensure models passed to each Connector stay in their own engines. """ db_file = "{}/fruit.db".format(self.working_directory()) fruit = SqlAlchemyDatabaseConnector(engine_url=f"sqlite:///{db_file}", schema_builder=fruit_schemas, access=ayeaye.AccessMode.READWRITE ) fruit.create_table_schema() db_file = "{}/people.db".format(self.working_directory()) people = SqlAlchemyDatabaseConnector(engine_url=f"sqlite:///{db_file}", schema_builder=people_schema, access=ayeaye.AccessMode.READWRITE ) people.create_table_schema() # Tables creates in correct DB # ---------------------- inspector = inspect(fruit.engine) fruit_tables = {table_name for table_name in inspector.get_table_names()} self.assertEqual({'bananna', 'pear'}, fruit_tables) inspector = inspect(people.engine) people_tables = {table_name for table_name in inspector.get_table_names()} self.assertEqual({'person'}, people_tables) # Tables can be used in the normal way # ---------------------- fruit.add(fruit.schema.Pear(variety="Comice")) fruit.commit() fruit.close_connection() people.add({'surname': 'Attenborough'}) people.commit() people.close_connection()
def test_double_close_sqlite(self): """ TODO - can't reproduce the "Cannot operate on a closed database." Sqlite error. """ db_file = "{}/fruit.db".format(self.working_directory()) c = SqlAlchemyDatabaseConnector(engine_url=f"sqlite:///{db_file}", schema_builder=fruit_schemas, access=ayeaye.AccessMode.READWRITE ) c.connect() c.create_table_schema() c.add(c.schema.Pear(variety="Williams")) # c.commit() c.close_connection() c.__del__()
def test_on_disk(self): """ All the other tests are in memory. Ensure to disk works. This test is also being created because windows is refusing to delete open files so confirmation that close_connection() is working was experimented with using lsof under OSX. But the file handle isn't left open so can't be part of this test. """ db_file = "{}/fruit.db".format(self.working_directory()) c = SqlAlchemyDatabaseConnector(engine_url=f"sqlite:///{db_file}", schema_builder=fruit_schemas, access=ayeaye.AccessMode.READWRITE ) c.connect() c.create_table_schema() c.add(c.schema.Pear(variety="Comice")) c.commit() c.close_connection() self.assertTrue(os.access(db_file, os.R_OK))
def test_add_orm_data_multiple(self): c = SqlAlchemyDatabaseConnector(engine_url="sqlite://", schema_builder=fruit_schemas, access=ayeaye.AccessMode.READWRITE ) c.connect() c.create_table_schema() with self.assertRaises(ValueError) as context: c.add({'variety': 'Cavendish'}) self.assertIn("Dictionary can only be used in single schema mode", str(context.exception)) c.add(c.schema.Pear(variety="D'Anjou")) c.add(c.schema.Bananna(variety="Cavendish")) c.commit() # read back mixed types with primary key values belonging to each table (i.e. both are 1) mixed_records = [r.__tablename__ + str(r.id) for r in c] expected = "pear1 bananna1" self.assertEqual(expected, " ".join(mixed_records))