Пример #1
0
 def test_ordering_on_aggregates(self):
     sq = SelectQuery(Blog,
                      't1.*, COUNT(t2.pk) as count').join(Entry).order_by(
                          peewee.desc('count'))
     self.assertEqual(sq.sql(), (
         'SELECT t1.*, COUNT(t2.pk) as count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id ORDER BY count DESC',
         []))
Пример #2
0
    def test_count(self):
        for i in xrange(10):
            self.create_blog(title='a%d' % i)

        count = SelectQuery(Blog).count()
        self.assertEqual(count, 10)
        
        count = Blog.select().count()
        self.assertEqual(count, 10)
        
        for blog in SelectQuery(Blog):
            for i in xrange(20):
                self.create_entry(title='entry%d' % i, blog=blog)
        
        count = SelectQuery(Entry).count()
        self.assertEqual(count, 200)
        
        count = SelectQuery(Entry).join(Blog).where(title="a0").count()
        self.assertEqual(count, 20)
        
        count = SelectQuery(Entry).where(
            title__icontains="0"
        ).join(Blog).where(
            title="a5"
        ).count()
        self.assertEqual(count, 2)
Пример #3
0
    def test_select_with_models(self):
        sq = SelectQuery(Blog, {Blog: "*"})
        self.assertEqual(sq.sql(), ("SELECT * FROM blog", []))

        sq = SelectQuery(Blog, {Blog: ["title", "id"]})
        self.assertEqual(sq.sql(), ("SELECT title, id FROM blog", []))

        sq = SelectQuery(Blog, {Blog: ["title", "id"]}).join(Entry)
        self.assertEqual(
            sq.sql(), ("SELECT t1.title, t1.id FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id", [])
        )

        sq = SelectQuery(Blog, {Blog: ["title", "id"], Entry: [peewee.Count("pk")]}).join(Entry)
        self.assertEqual(
            sq.sql(),
            (
                "SELECT t1.title, t1.id, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id",
                [],
            ),
        )

        sq = SelectQuery(Blog, {Blog: ["title", "id"], Entry: [peewee.Max("pk")]}).join(Entry)
        self.assertEqual(
            sq.sql(),
            (
                "SELECT t1.title, t1.id, MAX(t2.pk) AS max FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id",
                [],
            ),
        )
Пример #4
0
 def test_ordering_on_aggregates(self):
     sq = SelectQuery(Blog, "t1.*, COUNT(t2.pk) as count").join(Entry).order_by(peewee.desc("count"))
     self.assertEqual(
         sq.sql(),
         (
             "SELECT t1.*, COUNT(t2.pk) as count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id ORDER BY count DESC",
             [],
         ),
     )
Пример #5
0
    def test_inner_joins(self):
        sql = SelectQuery(Blog).join(Entry).sql()
        self.assertEqual(sql, (
            'SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id',
            []))

        sql = SelectQuery(Entry).join(Blog).sql()
        self.assertEqual(sql, (
            'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id',
            []))
Пример #6
0
    def test_outer_joins(self):
        sql = SelectQuery(User).join(Blog).sql()
        self.assertEqual(sql, (
            'SELECT t1.* FROM users AS t1 LEFT OUTER JOIN blog AS t2 ON t1.blog_id = t2.id',
            []))

        sql = SelectQuery(Blog).join(User).sql()
        self.assertEqual(sql, (
            'SELECT t1.* FROM blog AS t1 LEFT OUTER JOIN users AS t2 ON t1.id = t2.blog_id',
            []))
Пример #7
0
def dataClean():

    if EdulistModel.table_exists() == False:
        EdulistModel.create_table()

    schools = SelectQuery(EdulistModel).select().where(
        EdulistModel.postcode == '')
    print(schools.count())
    for school in schools:
        # category = school.category
        print(school.postcode)
Пример #8
0
def get_row_count():
    nodes = SelectQuery(Node).select()
    ways = SelectQuery(Way).select()
    relations = SelectQuery(Relation).select()

    print("nodes count = ", nodes.count())
    print("ways count = ", ways.count())
    print("relations count = ", relations.count())
Пример #9
0
def paginate(query: peewee.SelectQuery,
             page: int = 0,
             per_page: int = 100) -> tuple[peewee.SelectQuery, int]:
    """Paginate the results of a query.

    Returns results and number of pages.
    """
    total_results = query.count()
    pages = math.ceil(total_results / per_page)
    if pages and page >= pages:
        raise utils.RequestError(3201)
    page = query.offset(page * per_page).limit(per_page)
    return page, pages
Пример #10
0
    def test_select_with_models(self):
        sq = SelectQuery(Blog, {Blog: '*'})
        self.assertEqual(sq.sql(), ('SELECT * FROM blog', []))

        sq = SelectQuery(Blog, {Blog: ['title', 'id']})
        self.assertEqual(sq.sql(), ('SELECT title, id FROM blog', []))
    
        sq = SelectQuery(Blog, {Blog: ['title', 'id']}).join(Entry)
        self.assertEqual(sq.sql(), ('SELECT t1.title, t1.id FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id', []))

        sq = SelectQuery(Blog, {Blog: ['title', 'id'], Entry: [peewee.Count('id')]}).join(Entry)
        self.assertEqual(sq.sql(), ('SELECT t1.title, t1.id, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id', []))

        sq = SelectQuery(Blog, {Blog: ['title', 'id'], Entry: [peewee.Max('id')]}).join(Entry)
        self.assertEqual(sq.sql(), ('SELECT t1.title, t1.id, MAX(t2.id) AS max FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id', []))
Пример #11
0
def auth_resource_post_save(_, instance, created):
    """资源保存后执行
    """
    if created:
        # insert 操作
        if not Database.current_user:
            log.error("unknown user executed")
            return
        roles = Database.current_user['roles']
        if roles is None or not instance.uri:
            return
        # 强制使用主库进行查找
        try:
            resource = SelectQuery(AuthResource).join(
                AuthSystem).where((AuthResource.uri == instance.uri)
                                  & (AuthSystem.id == instance.sys.id)).get()
        except DoesNotExist:
            return
        for role in roles:
            role_resource = AuthRoleResource()
            role_resource.role = AuthRole.one(role=role)
            role_resource.resource = resource
            role_resource.method = 'index'
            role_resource.save()
            log.info(
                'do post_save on AuthResource, role=%s, resource=%s, methd=index',
                role, resource.uri)
Пример #12
0
    def test_selecting_with_aggregation(self):
        sq = SelectQuery(Blog, "t1.*, COUNT(t2.pk) AS count").group_by("id").join(Entry)
        self.assertEqual(sq._where, {})
        self.assertEqual(sq._joins, [(Entry, None, None)])
        self.assertEqual(
            sq.sql(),
            (
                "SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id",
                [],
            ),
        )

        sq = sq.having("count > 2")
        self.assertEqual(
            sq.sql(),
            (
                "SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2",
                [],
            ),
        )

        sq = SelectQuery(Blog, {Blog: ["*"], Entry: [peewee.Count("pk")]}).group_by("id").join(Entry)
        self.assertEqual(
            sq.sql(),
            (
                "SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id",
                [],
            ),
        )

        sq = sq.having("count > 2")
        self.assertEqual(
            sq.sql(),
            (
                "SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2",
                [],
            ),
        )

        sq = sq.order_by(("count", "desc"))
        self.assertEqual(
            sq.sql(),
            (
                "SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2 ORDER BY count desc",
                [],
            ),
        )
Пример #13
0
    def test_select(self):
        sq = SelectQuery(Blog, "*")
        self.assertEqual(sq.sql(), ("SELECT * FROM blog", []))

        sq = SelectQuery(Blog, "*").where(title="a")
        self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE title = ?", ["a"]))

        sq = SelectQuery(Blog, "*").where(title="a", id=1)
        self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (id = ? AND title = ?)", [1, "a"]))

        # check that chaining works as expected
        sq = SelectQuery(Blog, "*").where(title="a").where(id=1)
        self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE title = ? AND id = ?", ["a", 1]))

        # check that IN query special-case works
        sq = SelectQuery(Blog, "*").where(title__in=["a", "b"])
        self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE title IN (?,?)", ["a", "b"]))
Пример #14
0
    def test_select(self):
        sq = SelectQuery(Blog, '*')
        self.assertEqual(sq.sql(), ('SELECT * FROM blog', []))

        sq = SelectQuery(Blog, '*').where(title='a')
        self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE title = ?', ['a']))
        
        sq = SelectQuery(Blog, '*').where(title='a', id=1)
        self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (id = ? AND title = ?)', [1, 'a']))
        
        # check that chaining works as expected
        sq = SelectQuery(Blog, '*').where(title='a').where(id=1)
        self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE title = ? AND id = ?', ['a', 1]))
        
        # check that IN query special-case works
        sq = SelectQuery(Blog, '*').where(title__in=['a', 'b'])
        self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE title IN (?,?)', ['a', 'b']))
Пример #15
0
    def test_delete(self):
        InsertQuery(Blog, title='a').execute()
        InsertQuery(Blog, title='b').execute()
        InsertQuery(Blog, title='c').execute()

        dq = DeleteQuery(Blog).where(title='b')
        self.assertEqual(dq.sql(), ('DELETE FROM blog WHERE title = ?', ['b']))
        self.assertEqual(dq.execute(), 1)

        sq = SelectQuery(Blog).order_by('id')
        self.assertEqual([x.title for x in sq], ['a', 'c'])

        dq = DeleteQuery(Blog)
        self.assertEqual(dq.sql(), ('DELETE FROM blog', []))
        self.assertEqual(dq.execute(), 2)

        sq = SelectQuery(Blog).order_by('id')
        self.assertEqual([x.title for x in sq], [])
Пример #16
0
 def test_selecting_with_ordering(self):        
     sq = SelectQuery(Blog).order_by('title')
     self.assertEqual(sq.sql(), ('SELECT * FROM blog ORDER BY title ASC', []))
     
     sq = SelectQuery(Blog).order_by(peewee.desc('title'))
     self.assertEqual(sq.sql(), ('SELECT * FROM blog ORDER BY title DESC', []))
     
     sq = SelectQuery(Entry).order_by(peewee.desc('title')).join(Blog).where(title='a')
     self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE t2.title = ? ORDER BY t1.title DESC', ['a']))
     
     sq = SelectQuery(Entry).join(Blog).order_by(peewee.desc('title'))
     self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id ORDER BY t2.title DESC', []))
Пример #17
0
def checkout():
    if 'logged_in' in session:
        context = dict (
            user = User.get(User.username == session['client_name']),
            products = Inventory.select(),
            current_dir = 'checkout',
            categories = set(item.prod_type for item in SelectQuery(Inventory, Inventory.prod_type))
        )
        return render_template("checkout.html", **context)
    return redirect(url_for('home'))
Пример #18
0
 def test_selecting_with_aggregation(self):
     sq = SelectQuery(Blog, 't1.*, COUNT(t2.id) AS count').group_by('id').join(Entry)
     self.assertEqual(sq._where, {})
     self.assertEqual(sq._joins, [(Entry, None, None)])
     self.assertEqual(sq.sql(), ('SELECT t1.*, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id', []))
     
     sq = sq.having('count > 2')
     self.assertEqual(sq.sql(), ('SELECT t1.*, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2', []))
     
     sq = SelectQuery(Blog, {
         Blog: ['*'],
         Entry: [peewee.Count('id')]
     }).group_by('id').join(Entry)
     self.assertEqual(sq.sql(), ('SELECT t1.*, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id', []))
     
     sq = sq.having('count > 2')
     self.assertEqual(sq.sql(), ('SELECT t1.*, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2', []))
     
     sq = sq.order_by(('count', 'desc'))
     self.assertEqual(sq.sql(), ('SELECT t1.*, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2 ORDER BY count desc', []))
Пример #19
0
    def test_pagination(self):
        sq = SelectQuery(Blog).paginate(1, 20)
        self.assertEqual(sq.sql(),
                         ('SELECT * FROM blog LIMIT 20 OFFSET 0', []))

        sq = SelectQuery(Blog).paginate(3, 30)
        self.assertEqual(sq.sql(),
                         ('SELECT * FROM blog LIMIT 30 OFFSET 60', []))
Пример #20
0
    def test_selecting_across_joins(self):
        sq = SelectQuery(Entry, '*').where(title='a1').join(Blog).where(title='a')
        self.assertEqual(sq._joins, [(Blog, None, None)])
        self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE t1.title = ? AND t2.title = ?', ['a1', 'a']))
        
        sq = SelectQuery(Blog, '*').join(Entry).where(title='a1')        
        self.assertEqual(sq._joins, [(Entry, None, None)])
        self.assertEqual(sq.sql(), ('SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id WHERE t2.title = ?', ['a1']))

        sq = SelectQuery(EntryTag, '*').join(Entry).join(Blog).where(title='a')        
        self.assertEqual(sq._joins, [(Entry, None, None), (Blog, None, None)])
        self.assertEqual(sq.sql(), ('SELECT t1.* FROM entrytag AS t1 INNER JOIN entry AS t2 ON t1.entry_id = t2.id\nINNER JOIN blog AS t3 ON t2.blog_id = t3.id WHERE t3.title = ?', ['a']))
        
        sq = SelectQuery(Blog, '*').join(Entry).join(EntryTag).where(tag='t2')
        self.assertEqual(sq._joins, [(Entry, None, None), (EntryTag, None, None)])
        self.assertEqual(sq.sql(), ('SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id\nINNER JOIN entrytag AS t3 ON t2.id = t3.entry_id WHERE t3.tag = ?', ['t2']))
Пример #21
0
 def test_update(self):
     iq = InsertQuery(Blog, title='a').execute()
     
     uq = UpdateQuery(Blog, title='A').where(id=1)
     self.assertEqual(uq.sql(), ('UPDATE blog SET title=? WHERE id = ?', ['A', 1]))
     self.assertEqual(uq.execute(), 1)
     
     iq2 = InsertQuery(Blog, title='b').execute()
     
     uq = UpdateQuery(Blog, title='B').where(id=2)
     self.assertEqual(uq.sql(), ('UPDATE blog SET title=? WHERE id = ?', ['B', 2]))
     self.assertEqual(uq.execute(), 1)
     
     sq = SelectQuery(Blog).order_by('id')
     self.assertEqual([x.title for x in sq], ['A', 'B'])
Пример #22
0
    def test_selecting_across_joins(self):
        sq = SelectQuery(Entry, "*").where(title="a1").join(Blog).where(title="a")
        self.assertEqual(sq._joins, [(Blog, None, None)])
        self.assertEqual(
            sq.sql(),
            (
                "SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE t1.title = ? AND t2.title = ?",
                ["a1", "a"],
            ),
        )

        sq = SelectQuery(Blog, "*").join(Entry).where(title="a1")
        self.assertEqual(sq._joins, [(Entry, None, None)])
        self.assertEqual(
            sq.sql(),
            ("SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id WHERE t2.title = ?", ["a1"]),
        )

        sq = SelectQuery(EntryTag, "*").join(Entry).join(Blog).where(title="a")
        self.assertEqual(sq._joins, [(Entry, None, None), (Blog, None, None)])
        self.assertEqual(
            sq.sql(),
            (
                "SELECT t1.* FROM entrytag AS t1 INNER JOIN entry AS t2 ON t1.entry_id = t2.pk\nINNER JOIN blog AS t3 ON t2.blog_id = t3.id WHERE t3.title = ?",
                ["a"],
            ),
        )

        sq = SelectQuery(Blog, "*").join(Entry).join(EntryTag).where(tag="t2")
        self.assertEqual(sq._joins, [(Entry, None, None), (EntryTag, None, None)])
        self.assertEqual(
            sq.sql(),
            (
                "SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id\nINNER JOIN entrytag AS t3 ON t2.pk = t3.entry_id WHERE t3.tag = ?",
                ["t2"],
            ),
        )
Пример #23
0
 def test_selecting_with_switching(self):
     sq = SelectQuery(Blog, "*").join(Entry).switch(Blog).where(title="a")
     self.assertEqual(
         sq.sql(),
         ("SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id WHERE t1.title = ?", ["a"]),
     )
Пример #24
0
 def test_select_with_negation(self):
     sq = SelectQuery(Blog, '*').where(~Q(title='a'))
     self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE NOT title = ?', ['a']))
     
     sq = SelectQuery(Blog, '*').where(~Q(title='a') | Q(title='b'))
     self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (NOT title = ? OR title = ?)', ['a', 'b']))
     
     sq = SelectQuery(Blog, '*').where(~Q(title='a') | ~Q(title='b'))
     self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (NOT title = ? OR NOT title = ?)', ['a', 'b']))
     
     sq = SelectQuery(Blog, '*').where(~(Q(title='a') | Q(title='b')))
     self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (NOT (title = ? OR title = ?))', ['a', 'b']))
     
     # chaining?
     sq = SelectQuery(Blog, '*').where(~(Q(title='a') | Q(id=1))).where(Q(id=3))
     self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (NOT (title = ? OR id = ?)) AND id = ?', ['a', 1, 3]))
     
     # mix n'match?
     sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(~(Q(title='c') | Q(id=3)), title='b')
     self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND (NOT (title = ? OR id = ?)) AND title = ?', ['a', 1, 'c', 3, 'b']))
Пример #25
0
def dashboard(subdir):
    user_role = session['client_role']

    if 'logged_in' in session and (user_role == "Super Admin" or user_role == "Admin"):
        if subdir and subdir not in ['inventory', 'transactions', 'users', 'reports']:
            abort(404)


        context = dict (
            user = User.get(User.username == session['client_name']),
            products = Inventory.select(),
            current_dir = 'dashboard'
        )

        if subdir == 'inventory':
            categories = set(item.prod_type for item in SelectQuery(Inventory, Inventory.prod_type))
            return render_template("products.html", **context, categories=categories)

        elif subdir == 'transactions':
            try:
                date_obj = dict(
                    cur = (Transactions.select().
                           where(Transactions.date_sold.
                           between(dt.today(), dt.today() + timedelta(days=1)))
                          ),
                    min = Transactions.get(Transactions.transID == 1).date_sold,
                    max = Transactions.select().order_by(Transactions.transID.desc()).get().date_sold
                )
                return render_template("logs.html", **context, date = date_obj)
            except DoesNotExist:
                return render_template("logs.html", **context)

        elif subdir == 'users':
            users = User.select()
            return render_template("users.html", **context, users = users)

        elif subdir == 'reports':
            low_stock_query = """
                                SELECT * FROM `inventory`
                                WHERE `prod_stock` != 0 AND ROUND((`prod_stock` / `prod_max_stock`) * 100) <= 20
                              """

            no_stock_query = 'SELECT * FROM `inventory` WHERE `prod_stock` = 0'
            stock_sum_query = 'SELECT SUM(prod_stock) FROM `inventory`'
            max_stock_sum_query = 'SELECT SUM(prod_max_stock) FROM `inventory`'

            low_stock_items = []
            no_stock_items = []
            [low_stock_items.append(item) for item in Inventory.raw(low_stock_query)]
            [no_stock_items.append(item) for item in Inventory.raw(no_stock_query)]

            reports = dict(
                daily = fetch_report('DAY'),
                weekly = fetch_report('YEARWEEK'),
                monthly = fetch_report('MONTH'),
                low_stock = { 'length': len(low_stock_items), 'items': low_stock_items},
                no_stock = { 'length': len(no_stock_items), 'items': no_stock_items },
                total_stock = db.execute_sql(stock_sum_query).fetchone()[0],
                total_max_stock = db.execute_sql(max_stock_sum_query).fetchone()[0]
            )

            return render_template("reports.html", **context, reports = reports)

        else:
            return render_template("default.html", **context)
    abort(400)
Пример #26
0
    def test_selecting_with_ordering(self):
        sq = SelectQuery(Blog).order_by('title')
        self.assertEqual(sq.sql(),
                         ('SELECT * FROM blog ORDER BY title ASC', []))

        sq = SelectQuery(Blog).order_by(peewee.desc('title'))
        self.assertEqual(sq.sql(),
                         ('SELECT * FROM blog ORDER BY title DESC', []))

        sq = SelectQuery(Entry).order_by(
            peewee.desc('title')).join(Blog).where(title='a')
        self.assertEqual(sq.sql(), (
            'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE t2.title = ? ORDER BY t1.title DESC',
            ['a']))

        sq = SelectQuery(Entry).join(Blog).order_by(peewee.desc('title'))
        self.assertEqual(sq.sql(), (
            'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id ORDER BY t2.title DESC',
            []))
Пример #27
0
    def test_selecting_with_aggregation(self):
        sq = SelectQuery(
            Blog, 't1.*, COUNT(t2.pk) AS count').group_by('id').join(Entry)
        self.assertEqual(sq._where, {})
        self.assertEqual(sq._joins, [(Entry, None, None)])
        self.assertEqual(sq.sql(), (
            'SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id',
            []))

        sq = sq.having('count > 2')
        self.assertEqual(sq.sql(), (
            'SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2',
            []))

        sq = SelectQuery(Blog, {
            Blog: ['*'],
            Entry: [peewee.Count('pk')]
        }).group_by('id').join(Entry)
        self.assertEqual(sq.sql(), (
            'SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id',
            []))

        sq = sq.having('count > 2')
        self.assertEqual(sq.sql(), (
            'SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2',
            []))

        sq = sq.order_by(('count', 'desc'))
        self.assertEqual(sq.sql(), (
            'SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2 ORDER BY count desc',
            []))
Пример #28
0
    def test_select_with_q(self):
        sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1))
        self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?)', ['a', 1]))
        
        sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1) | Q(id=3))
        self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ? OR id = ?)', ['a', 1, 3]))
        
        # test simple chaining
        sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(Q(id=3))
        self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND id = ?', ['a', 1, 3]))
        
        sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(id=3)
        self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND id = ?', ['a', 1, 3]))
        
        # test chaining with Q objects
        sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where((Q(title='c') | Q(id=3)))
        self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND (title = ? OR id = ?)', ['a', 1, 'c', 3]))

        # test mixing it all up
        sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where((Q(title='c') | Q(id=3)), title='b')
        self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND (title = ? OR id = ?) AND title = ?', ['a', 1, 'c', 3, 'b']))
Пример #29
0
    def test_select_with_negation(self):
        sq = SelectQuery(Blog, "*").where(~Q(title="a"))
        self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE NOT title = ?", ["a"]))

        sq = SelectQuery(Blog, "*").where(~Q(title="a") | Q(title="b"))
        self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (NOT title = ? OR title = ?)", ["a", "b"]))

        sq = SelectQuery(Blog, "*").where(~Q(title="a") | ~Q(title="b"))
        self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (NOT title = ? OR NOT title = ?)", ["a", "b"]))

        sq = SelectQuery(Blog, "*").where(~(Q(title="a") | Q(title="b")))
        self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (NOT (title = ? OR title = ?))", ["a", "b"]))

        # chaining?
        sq = SelectQuery(Blog, "*").where(~(Q(title="a") | Q(id=1))).where(Q(id=3))
        self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (NOT (title = ? OR id = ?)) AND id = ?", ["a", 1, 3]))

        # mix n'match?
        sq = SelectQuery(Blog, "*").where(Q(title="a") | Q(id=1)).where(~(Q(title="c") | Q(id=3)), title="b")
        self.assertEqual(
            sq.sql(),
            (
                "SELECT * FROM blog WHERE (title = ? OR id = ?) AND (NOT (title = ? OR id = ?)) AND title = ?",
                ["a", 1, "c", 3, "b"],
            ),
        )
Пример #30
0
def fixData():
    # p = Pool(100)
    schools = SelectQuery(EdulistModel).select().where(
        EdulistModel.postcode == '')
    print(schools.count())
Пример #31
0
    def __init__(self, *args, **kwargs):
        BaseSelectQuery.__init__(self, *args, **kwargs)
        gen.Future.__init__(self)

        self._future = None
Пример #32
0
    def test_selecting_across_joins_with_q(self):
        sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(id=1)).join(Blog).where(title='e')
        self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.id = ?) AND t2.title = ?', ['a', 1, 'e']))
        
        sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(id=1) | Q(title='b')).join(Blog).where(title='e')
        self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.id = ? OR t1.title = ?) AND t2.title = ?', ['a', 1, 'b', 'e']))

        # test simple chaining
        sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(id=1)).where(Q(title='b')).join(Blog).where(title='e')
        self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.id = ?) AND t1.title = ? AND t2.title = ?', ['a', 1, 'b', 'e']))
        
        sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(id=1)).where(title='b').join(Blog).where(title='e')
        self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.id = ?) AND t1.title = ? AND t2.title = ?', ['a', 1, 'b', 'e']))

        # test q on both models
        sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(id=1)).join(Blog).where(Q(title='e') | Q(id=2))
        self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.id = ?) AND (t2.title = ? OR t2.id = ?)', ['a', 1, 'e', 2]))
    
        # test q on both with nesting
        sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(id=1)).join(Blog).where((Q(title='e') | Q(id=2)) & (Q(title='f') | Q(id=3)))
        self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.id = ?) AND ((t2.title = ? OR t2.id = ?) AND (t2.title = ? OR t2.id = ?))', ['a', 1, 'e', 2, 'f', 3]))
Пример #33
0
 def test_selecting_with_switching(self):
     sq = SelectQuery(Blog, '*').join(Entry).switch(Blog).where(title='a')
     self.assertEqual(sq.sql(), ('SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id WHERE t1.title = ?', ['a']))
Пример #34
0
from models import *
from csv import DictReader
from peewee import SelectQuery

db.create_tables([Voter, Voter_Geocode, Voter_History], True)

voter_count = SelectQuery(Voter).count()

if voter_count < 121795:

    print '   Importing voter records...'

    with open('VRTAPE01KBIA_W_HEADERS.CSV') as in_file:

        reader = DictReader(in_file)

        for row in reader:

            if row['vr_dob'] == '0':
                row['vr_dob'] = None
            else:
                row['birth_year'] = row['vr_dob'][:4]
                if len(row['vr_dob']) < 8:
                    row['vr_dob'] = None
                elif row['vr_dob'][4:6] == '00' or row['vr_dob'][6:] == '00':
                    row['vr_dob'] = None
                elif row['vr_dob'][:4] == '9999':
                    row['vr_dob'] = None
                else:
                    row['vr_dob'] = row['vr_dob'][:4] + '-' + row['vr_dob'][
                        4:6] + '-' + row['vr_dob'][6:]
Пример #35
0
 def select(cls, *selection):
     cls._meta.remove_field('id')
     query = SelectQuery(cls, *selection)
     if cls._meta.order_by:
         query = query.order_by(*cls._meta.order_by)
     return query
Пример #36
0
#! /usr/bin/python
# -*- coding:utf-8 -*-
"""
@author: abc
@file: main.py
@date: 2017-02-04
"""
__author__ = "abc"

from peewee import SelectQuery
from model.db.tb_raw import TbRaw

if __name__ == "__main__":
    # query = TbRaw.select().where(TbRaw.id > 0)
    query = SelectQuery(TbRaw).where(TbRaw.id > 0)
    print query.sql()[0] % tuple(query.sql()[1])
    for item in query.dicts().execute():
        print item

    # UNION查询
    query = TbRaw.select().where(TbRaw.id >= 2) | TbRaw.select().where(
        TbRaw.id < 2)
    print query.sql()[0] % tuple(query.sql()[1])
    for item in query.dicts().execute():
        print item
Пример #37
0
#! /usr/bin/python
# -*- coding:utf-8 -*-
"""
@author: abc
@file: main.py
@date: 2017-02-04
"""
__author__ = "abc"

from peewee import SelectQuery
from model.db.tb_raw import TbRaw

if __name__ == "__main__":
    # query = TbRaw.select().where(TbRaw.id > 0)
    query = SelectQuery(TbRaw).where(TbRaw.id > 0)
    print query.sql()[0] % tuple(query.sql()[1])
    for item in query.dicts().execute():
        print item

    # UNION查询
    query = TbRaw.select().where(TbRaw.id >= 2) | TbRaw.select().where(TbRaw.id < 2)
    print query.sql()[0] % tuple(query.sql()[1])
    for item in query.dicts().execute():
        print item
Пример #38
0
 def create_select_query(cls, *selection):
     return SelectQuery(cls, *selection)
Пример #39
0
    def test_selecting_across_joins(self):
        sq = SelectQuery(Entry,
                         '*').where(title='a1').join(Blog).where(title='a')
        self.assertEqual(sq._joins, [(Blog, None, None)])
        self.assertEqual(sq.sql(), (
            'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE t1.title = ? AND t2.title = ?',
            ['a1', 'a']))

        sq = SelectQuery(Blog, '*').join(Entry).where(title='a1')
        self.assertEqual(sq._joins, [(Entry, None, None)])
        self.assertEqual(sq.sql(), (
            'SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id WHERE t2.title = ?',
            ['a1']))

        sq = SelectQuery(EntryTag, '*').join(Entry).join(Blog).where(title='a')
        self.assertEqual(sq._joins, [(Entry, None, None), (Blog, None, None)])
        self.assertEqual(sq.sql(), (
            'SELECT t1.* FROM entrytag AS t1 INNER JOIN entry AS t2 ON t1.entry_id = t2.pk\nINNER JOIN blog AS t3 ON t2.blog_id = t3.id WHERE t3.title = ?',
            ['a']))

        sq = SelectQuery(Blog, '*').join(Entry).join(EntryTag).where(tag='t2')
        self.assertEqual(sq._joins, [(Entry, None, None),
                                     (EntryTag, None, None)])
        self.assertEqual(sq.sql(), (
            'SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id\nINNER JOIN entrytag AS t3 ON t2.pk = t3.entry_id WHERE t3.tag = ?',
            ['t2']))
Пример #40
0
    def test_select_with_q(self):
        sq = SelectQuery(Blog, "*").where(Q(title="a") | Q(id=1))
        self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (title = ? OR id = ?)", ["a", 1]))

        sq = SelectQuery(Blog, "*").where(Q(title="a") | Q(id=1) | Q(id=3))
        self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (title = ? OR id = ? OR id = ?)", ["a", 1, 3]))

        # test simple chaining
        sq = SelectQuery(Blog, "*").where(Q(title="a") | Q(id=1)).where(Q(id=3))
        self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (title = ? OR id = ?) AND id = ?", ["a", 1, 3]))

        sq = SelectQuery(Blog, "*").where(Q(title="a") | Q(id=1)).where(id=3)
        self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (title = ? OR id = ?) AND id = ?", ["a", 1, 3]))

        # test chaining with Q objects
        sq = SelectQuery(Blog, "*").where(Q(title="a") | Q(id=1)).where((Q(title="c") | Q(id=3)))
        self.assertEqual(
            sq.sql(), ("SELECT * FROM blog WHERE (title = ? OR id = ?) AND (title = ? OR id = ?)", ["a", 1, "c", 3])
        )

        # test mixing it all up
        sq = SelectQuery(Blog, "*").where(Q(title="a") | Q(id=1)).where((Q(title="c") | Q(id=3)), title="b")
        self.assertEqual(
            sq.sql(),
            (
                "SELECT * FROM blog WHERE (title = ? OR id = ?) AND (title = ? OR id = ?) AND title = ?",
                ["a", 1, "c", 3, "b"],
            ),
        )
Пример #41
0
    def test_selecting_across_joins_with_q(self):
        sq = SelectQuery(Entry,
                         '*').where(Q(title='a')
                                    | Q(pk=1)).join(Blog).where(title='e')
        self.assertEqual(sq.sql(), (
            'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND t2.title = ?',
            ['a', 1, 'e']))

        sq = SelectQuery(Entry,
                         '*').where(Q(title='a') | Q(pk=1)
                                    | Q(title='b')).join(Blog).where(title='e')
        self.assertEqual(sq.sql(), (
            'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ? OR t1.title = ?) AND t2.title = ?',
            ['a', 1, 'b', 'e']))

        # test simple chaining
        sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(pk=1)).where(
            Q(title='b')).join(Blog).where(title='e')
        self.assertEqual(sq.sql(), (
            'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND t1.title = ? AND t2.title = ?',
            ['a', 1, 'b', 'e']))

        sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(pk=1)).where(
            title='b').join(Blog).where(title='e')
        self.assertEqual(sq.sql(), (
            'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND t1.title = ? AND t2.title = ?',
            ['a', 1, 'b', 'e']))

        # test q on both models
        sq = SelectQuery(
            Entry,
            '*').where(Q(title='a')
                       | Q(pk=1)).join(Blog).where(Q(title='e') | Q(id=2))
        self.assertEqual(sq.sql(), (
            'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND (t2.title = ? OR t2.id = ?)',
            ['a', 1, 'e', 2]))

        # test q on both with nesting
        sq = SelectQuery(
            Entry,
            '*').where(Q(title='a')
                       | Q(pk=1)).join(Blog).where((Q(title='e') | Q(id=2))
                                                   & (Q(title='f') | Q(id=3)))
        self.assertEqual(sq.sql(), (
            'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND ((t2.title = ? OR t2.id = ?) AND (t2.title = ? OR t2.id = ?))',
            ['a', 1, 'e', 2, 'f', 3]))
Пример #42
0
    def test_select(self):
        sq = SelectQuery(Blog, '*')
        self.assertEqual(sq.sql(), ('SELECT * FROM blog', []))

        sq = SelectQuery(Blog, '*').where(title='a')
        self.assertEqual(sq.sql(),
                         ('SELECT * FROM blog WHERE title = ?', ['a']))

        sq = SelectQuery(Blog, '*').where(title='a', id=1)
        self.assertEqual(
            sq.sql(),
            ('SELECT * FROM blog WHERE (id = ? AND title = ?)', [1, 'a']))

        # check that chaining works as expected
        sq = SelectQuery(Blog, '*').where(title='a').where(id=1)
        self.assertEqual(
            sq.sql(),
            ('SELECT * FROM blog WHERE title = ? AND id = ?', ['a', 1]))

        # check that IN query special-case works
        sq = SelectQuery(Blog, '*').where(title__in=['a', 'b'])
        self.assertEqual(
            sq.sql(), ('SELECT * FROM blog WHERE title IN (?,?)', ['a', 'b']))
Пример #43
0
 def test_selecting_with_switching(self):
     sq = SelectQuery(Blog, '*').join(Entry).switch(Blog).where(title='a')
     self.assertEqual(sq.sql(), (
         'SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id WHERE t1.title = ?',
         ['a']))
Пример #44
0
    def test_selecting_across_joins_with_q(self):
        sq = SelectQuery(Entry, "*").where(Q(title="a") | Q(pk=1)).join(Blog).where(title="e")
        self.assertEqual(
            sq.sql(),
            (
                "SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND t2.title = ?",
                ["a", 1, "e"],
            ),
        )

        sq = SelectQuery(Entry, "*").where(Q(title="a") | Q(pk=1) | Q(title="b")).join(Blog).where(title="e")
        self.assertEqual(
            sq.sql(),
            (
                "SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ? OR t1.title = ?) AND t2.title = ?",
                ["a", 1, "b", "e"],
            ),
        )

        # test simple chaining
        sq = SelectQuery(Entry, "*").where(Q(title="a") | Q(pk=1)).where(Q(title="b")).join(Blog).where(title="e")
        self.assertEqual(
            sq.sql(),
            (
                "SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND t1.title = ? AND t2.title = ?",
                ["a", 1, "b", "e"],
            ),
        )

        sq = SelectQuery(Entry, "*").where(Q(title="a") | Q(pk=1)).where(title="b").join(Blog).where(title="e")
        self.assertEqual(
            sq.sql(),
            (
                "SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND t1.title = ? AND t2.title = ?",
                ["a", 1, "b", "e"],
            ),
        )

        # test q on both models
        sq = SelectQuery(Entry, "*").where(Q(title="a") | Q(pk=1)).join(Blog).where(Q(title="e") | Q(id=2))
        self.assertEqual(
            sq.sql(),
            (
                "SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND (t2.title = ? OR t2.id = ?)",
                ["a", 1, "e", 2],
            ),
        )

        # test q on both with nesting
        sq = (
            SelectQuery(Entry, "*")
            .where(Q(title="a") | Q(pk=1))
            .join(Blog)
            .where((Q(title="e") | Q(id=2)) & (Q(title="f") | Q(id=3)))
        )
        self.assertEqual(
            sq.sql(),
            (
                "SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND ((t2.title = ? OR t2.id = ?) AND (t2.title = ? OR t2.id = ?))",
                ["a", 1, "e", 2, "f", 3],
            ),
        )
Пример #45
0
    def test_select_with_q(self):
        sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1))
        self.assertEqual(
            sq.sql(),
            ('SELECT * FROM blog WHERE (title = ? OR id = ?)', ['a', 1]))

        sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1) | Q(id=3))
        self.assertEqual(
            sq.sql(),
            ('SELECT * FROM blog WHERE (title = ? OR id = ? OR id = ?)',
             ['a', 1, 3]))

        # test simple chaining
        sq = SelectQuery(Blog,
                         '*').where(Q(title='a') | Q(id=1)).where(Q(id=3))
        self.assertEqual(
            sq.sql(),
            ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND id = ?',
             ['a', 1, 3]))

        sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(id=3)
        self.assertEqual(
            sq.sql(),
            ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND id = ?',
             ['a', 1, 3]))

        # test chaining with Q objects
        sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(
            (Q(title='c') | Q(id=3)))
        self.assertEqual(sq.sql(), (
            'SELECT * FROM blog WHERE (title = ? OR id = ?) AND (title = ? OR id = ?)',
            ['a', 1, 'c', 3]))

        # test mixing it all up
        sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(
            (Q(title='c') | Q(id=3)), title='b')
        self.assertEqual(sq.sql(), (
            'SELECT * FROM blog WHERE (title = ? OR id = ?) AND (title = ? OR id = ?) AND title = ?',
            ['a', 1, 'c', 3, 'b']))
Пример #46
0
    def test_select_with_negation(self):
        sq = SelectQuery(Blog, '*').where(~Q(title='a'))
        self.assertEqual(sq.sql(),
                         ('SELECT * FROM blog WHERE NOT title = ?', ['a']))

        sq = SelectQuery(Blog, '*').where(~Q(title='a') | Q(title='b'))
        self.assertEqual(
            sq.sql(), ('SELECT * FROM blog WHERE (NOT title = ? OR title = ?)',
                       ['a', 'b']))

        sq = SelectQuery(Blog, '*').where(~Q(title='a') | ~Q(title='b'))
        self.assertEqual(
            sq.sql(),
            ('SELECT * FROM blog WHERE (NOT title = ? OR NOT title = ?)',
             ['a', 'b']))

        sq = SelectQuery(Blog, '*').where(~(Q(title='a') | Q(title='b')))
        self.assertEqual(
            sq.sql(),
            ('SELECT * FROM blog WHERE (NOT (title = ? OR title = ?))',
             ['a', 'b']))

        # chaining?
        sq = SelectQuery(Blog,
                         '*').where(~(Q(title='a') | Q(id=1))).where(Q(id=3))
        self.assertEqual(
            sq.sql(),
            ('SELECT * FROM blog WHERE (NOT (title = ? OR id = ?)) AND id = ?',
             ['a', 1, 3]))

        # mix n'match?
        sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(
            ~(Q(title='c') | Q(id=3)), title='b')
        self.assertEqual(sq.sql(), (
            'SELECT * FROM blog WHERE (title = ? OR id = ?) AND (NOT (title = ? OR id = ?)) AND title = ?',
            ['a', 1, 'c', 3, 'b']))
Пример #47
0
    def test_select_with_models(self):
        sq = SelectQuery(Blog, {Blog: '*'})
        self.assertEqual(sq.sql(), ('SELECT * FROM blog', []))

        sq = SelectQuery(Blog, {Blog: ['title', 'id']})
        self.assertEqual(sq.sql(), ('SELECT title, id FROM blog', []))

        sq = SelectQuery(Blog, {Blog: ['title', 'id']}).join(Entry)
        self.assertEqual(sq.sql(), (
            'SELECT t1.title, t1.id FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id',
            []))

        sq = SelectQuery(Blog, {
            Blog: ['title', 'id'],
            Entry: [peewee.Count('pk')]
        }).join(Entry)
        self.assertEqual(sq.sql(), (
            'SELECT t1.title, t1.id, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id',
            []))

        sq = SelectQuery(Blog, {
            Blog: ['title', 'id'],
            Entry: [peewee.Max('pk')]
        }).join(Entry)
        self.assertEqual(sq.sql(), (
            'SELECT t1.title, t1.id, MAX(t2.pk) AS max FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id',
            []))
Пример #48
0
    def test_pagination(self):
        sq = SelectQuery(Blog).paginate(1, 20)
        self.assertEqual(sq.sql(), ("SELECT * FROM blog LIMIT 20 OFFSET 0", []))

        sq = SelectQuery(Blog).paginate(3, 30)
        self.assertEqual(sq.sql(), ("SELECT * FROM blog LIMIT 30 OFFSET 60", []))
Пример #49
0
for row in cursor:
    if len(row[0]) > 0 and len(row[2]) > 0:
        data_source.append({
            'realname': row[1],
            'studnum': row[0],
            'cardnum': row[2],
            'cardtype': row[3],
            'userid': row[4],
        })

# Close the cursor and the database connection
cursor.close()
conn.close()

print("%d records in total" % (len(data_source)))

with mysql_db.atomic():
    TempAccountInfo.insert_many(data_source).execute()

print("%d records in temp table" % (SelectQuery(TempAccountInfo).count()))

cursor = mysql_db.get_cursor()
cursor.execute(
    'UPDATE {0} INNER JOIN {1} ON {0}.studnum = {1}.studnum SET {0}.cardnum = {1}.cardnum WHERE {0}.cardnum != {1}.cardnum'
    .format(acct_tbl, tmp_tbl))
print("%d records updated" % (mysql_db.rows_affected(cursor)))
cursor.execute('INSERT IGNORE INTO {0} SELECT * FROM {1}'.format(
    acct_tbl, tmp_tbl))
print("%d new records added" % (mysql_db.rows_affected(cursor)))
mysql_db.commit()