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',
[]))
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)
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",
[],
),
)
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",
[],
),
)
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',
[]))
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',
[]))
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)
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())
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
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', []))
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)
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",
[],
),
)
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"]))
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']))
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], [])
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', []))
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'))
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', []))
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', []))
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']))
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'])
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"],
),
)
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"]),
)
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']))
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)
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',
[]))
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',
[]))
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']))
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"],
),
)
def fixData():
# p = Pool(100)
schools = SelectQuery(EdulistModel).select().where(
EdulistModel.postcode == '')
print(schools.count())
def __init__(self, *args, **kwargs):
BaseSelectQuery.__init__(self, *args, **kwargs)
gen.Future.__init__(self)
self._future = None
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]))
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']))
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:]
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
#! /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
#! /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
def create_select_query(cls, *selection):
return SelectQuery(cls, *selection)
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']))
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"],
),
)
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]))
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']))
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']))
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],
),
)
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']))
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']))
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',
[]))
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", []))
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()