Exemple #1
0
 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)
Exemple #2
0
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')
Exemple #3
0
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))
Exemple #4
0

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()
Exemple #5
0
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()
Exemple #6
0
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)
Exemple #7
0
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)