def setUpClass(self): db_name = 'sqlite' db_strs = { 'postgresql': 'postgresql://*****:*****@localhost/sqlpharmacy', 'sqlite': 'sqlite:///sqlpharmacy/testsuite/sqlpharmacy.db', 'mysql': 'mysql://*****:*****@localhost/sqlpharmacy?charset=utf8', } db_str = db_strs[db_name] self.db = Database(db_str) self.db.drop_tables() self.db.create_tables() self.db.load_data(fixtures)
from sqlalchemy import Column, String __metaclass__ = Database.DefaultMeta class User: name = Column(String(30)) @Database.many_to_many(User, ref_name='users', backref_name='roles', middle_table_name='user_role') class Role: name = Column(String(30)) Database.register() if __name__ == '__main__': db = Database('sqlite://') db.create_tables() user1 = User(name='Tyler Long') user2 = User(name='Peter Lau') role = Role(name='Administrator', users=[user1, user2]) db.session.add_then_commit(role) admin_role = db.session.query(Role).filter_by(name='Administrator').one() print(', '.join([user.name for user in admin_role.users]), 'are administrators')
from sqlpharmacy.core import Database from sqlalchemy import Column, String __metaclass__ = Database.DefaultMeta class User: name = Column(String(70)) Database.register() if __name__ == '__main__': db = Database('sqlite://') db.create_tables() count = db.engine.execute('select count(name) from user').scalar() print('There are {0} users in total'.format(count))
class Group(metaclass=Database.DefaultMeta): name = Column(String(36), nullable = False, unique = True) @Database.many_to_one(User, backref_name = 'blog_entries') class BlogEntry(metaclass=Database.DefaultMeta): title = Column(String(64), nullable = False) content = Column(Text) class Topic(metaclass=Database.DefaultMeta): name = Column(String(64), nullable = False) @Database.many_to_one(User) class Post(metaclass=Database.DefaultMeta): content = Column(Text) @Database.many_to_many(Topic) class Question(Post): title = Column(String(64), nullable = False) class Answer(Post): pass Database.register()
class Group(metaclass=Database.DefaultMeta): name = Column(String(36), nullable=False, unique=True) @Database.many_to_one(User, backref_name='blog_entries') class BlogEntry(metaclass=Database.DefaultMeta): title = Column(String(64), nullable=False) content = Column(Text) class Topic(metaclass=Database.DefaultMeta): name = Column(String(64), nullable=False) @Database.many_to_one(User) class Post(metaclass=Database.DefaultMeta): content = Column(Text) @Database.many_to_many(Topic) class Question(Post): title = Column(String(64), nullable=False) class Answer(Post): pass Database.register()
class CoreTestCase(unittest.TestCase): """The default test case""" @classmethod def setUpClass(self): db_name = 'sqlite' db_strs = { 'postgresql': 'postgresql://*****:*****@localhost/sqlpharmacy', 'sqlite': 'sqlite:///sqlpharmacy/testsuite/sqlpharmacy.db', 'mysql': 'mysql://*****:*****@localhost/sqlpharmacy?charset=utf8', } db_str = db_strs[db_name] self.db = Database(db_str) self.db.drop_tables() self.db.create_tables() self.db.load_data(fixtures) def tearDown(self): self.db.session.remove() def test_many_to_one(self): """Test many_to_one relationship""" user = self.db.session.query(User).filter_by(name='simon').first() assert user assert user.blog_entries.count() > 0 assert user.blog_entries.first().user.name == 'simon' def test_many_to_many(self): """Test many_to_many relationship""" user = self.db.session.query(User).filter_by(name='simon').first() assert user assert user.groups.count() > 0 assert user.groups.first().users.count() > 0 assert any(u.name == 'simon' for u in user.groups.first().users) def test_many_to_one_cascade(self): """Test cascade in a many_to_one relationship. When record from the one side gets deleted, records from the many side are deleted automatically. Make sure no ignorant records are deleted. """ user_query = self.db.session.query(User) user_count = user_query.count() blog_query = self.db.session.query(BlogEntry) blog_count = blog_query.count() user = self.db.session.query(User).filter_by(name='peter').first() assert user user_blog_count = user.blog_entries.count() blog_entry = user.blog_entries.first() assert blog_entry self.db.session.delete_then_commit(user) blog_entry = self.db.session.query(BlogEntry).get(blog_entry.id) assert not blog_entry assert user_count - 1 == user_query.count() assert blog_count - user_blog_count == blog_query.count() def test_many_to_many_cascade(self): """Test cascade in a many_to_many relationship. when record from either side gets deleted, records from the middle table are deleted automatically, records from the other side retain. make sure no ignorant records are deleted. """ user_query = self.db.session.query(User) user_count = user_query.count() group_query = self.db.session.query(Group) group_count = group_query.count() middle_query_str = 'select count(*) from user_group' middle_count = self.db.engine.execute(middle_query_str).scalar() user = self.db.session.query(User).filter_by(name='tyler').first() assert user query = 'select count(*) from user_group where user_id={0}' query = query.format(user.id) user_middle_count = self.db.engine.execute(query).scalar() group = self.db.session.query(Group).filter_by(name='editor').first() assert group query_str = ('select count(*) from user_group where user_id={0} and ' 'group_id={1}'.format(user.id, group.id)) assert self.db.engine.execute(query_str).scalar() == 1 self.db.session.delete_then_commit(user) assert self.db.engine.execute(query_str).scalar() == 0 group = self.db.session.query(Group).filter_by(name='editor').first() assert group assert user_count - 1 == user_query.count() assert group_count == group_query.count() assert (middle_count - user_middle_count == self.db.engine.execute(middle_query_str).scalar()) def test_table_inheritance(self): """Test table inheritance""" question_query = self.db.session.query(Question) question_count = question_query.count() assert question_count > 0 query = self.db.session.query(Question) question = query.filter_by(title='question_title_1').first() assert question assert question.user assert any(question.title == q.title for q in question.user.questions)
from sqlpharmacy.core import Database from sqlalchemy import Column, String __metaclass__ = Database.DefaultMeta class User: name = Column(String(30)) Database.register() if __name__ == '__main__': db = Database('sqlite://') # database urls: http://docs.sqlalchemy.org/en/latest/core/engines.html#database-urls db.create_tables() # create tables, you don't have to write any SQL. user = User(name = 'Hello World') db.session.add_then_commit(user) # commit user to database. user = db.session.query(User).get(1) print('My name is', user.name) print('created_at', user.created_at) # created_at and updated_at timestamps are added automatically. print('updated_at', user.updated_at) user.name = 'Tyler Long' db.session.commit() # commit changes to database. print('My name is', user.name) print('created_at', user.created_at) print('updated_at', user.updated_at)