def add_article():
form = ArticleForm(request.form)
if request.method == 'POST' and form.validate():
sql = sqlpuzzle.insert_into('article').values({
'title': form.title.data,
'text': form.text.data,
})
cur = get_db().cursor()
cur.execute(str(sql))
return redirect(url_for('article_page.articles'))
return render_template('article/add.html', form=form)
sql = sqlpuzzle.select('country.name', avg).from_(table)
sql.where(where).where(planet='Earth')
sql.group_by('user.country_id')
print(sql)
#
# output:
# (for better reading splited to more lines)
#
# SELECT `country`.`name`, AVG(`age`) AS "avgAge"
# FROM `user` LEFT JOIN `country` ON `user`.`country_id`=`country`.`id`
# WHERE (`enable` = 1 OR `vip` = 1) AND `planet` = 'Earth'
# GROUP BY `user`.`country_id`
#
table = sqlpuzzle.customsql('`user`')
set_ = sqlpuzzle.customsql('`age` = `age` + 1')
sql = sqlpuzzle.update(table).set(set_).where(where)
print(sql)
# output:
# UPDATE `user` SET `age` = `age` + 1 WHERE (`enable` = 1 OR `vip` = 1)
print(sqlpuzzle.delete_from(table).where(where))
# output:
# DELETE FROM `user` WHERE (`enable` = 1 OR `vip` = 1)
print(sqlpuzzle.insert_into(table).values(name='Alan'))
# output:
# INSERT INTO `user` (`name`) VALUES ("Alan")
# output: ORDER BY `id`
# All possible parts:
# * _columns
# * _group_by
# * _having
# * _into_outfile
# * _limit
# * _on_duplicate_key_update
# * _order_by
# * _tables
# * _values
# * _where
# * _select_options
sql = sqlpuzzle.insert_into('t').values(age=20, name='Michael')
print(sql._values)
# output: `age` = 20, `name` = "Michael"
# oops! output is for update, not insert!
# All these variables are instances:
print("(%s) VALUES (%s)" % (sql._values.columns(), sql._values.values()))
# output: (`age`, `name`) VALUES (20, "Michael")
# And now we have ouput for insert. :)
# !! This is not for usege! But it's possible. !!
print(sqlpuzzle._queryparts.Limit().limit(10))
print(sqlpuzzle._queryparts.orderby.OrderBy().order_by('id'))
# etc..
def test_insert_into(self):
sql = sqlpuzzle.insert_into('user').values(name='Harry')
self.assertEqual(str(sql),
'INSERT INTO "user" ("name") VALUES (\'Harry\')')
# output: ORDER BY `id`
# All possible parts:
# * _columns
# * _group_by
# * _having
# * _into_outfile
# * _limit
# * _on_duplicate_key_update
# * _order_by
# * _tables
# * _values
# * _where
# * _select_options
sql = sqlpuzzle.insert_into('t').values(age=20, name='Michael')
print(sql._values)
# output: `age` = 20, `name` = "Michael"
# oops! output is for update, not insert!
# All these variables are instances:
print("(%s) VALUES (%s)" % (sql._values.columns(), sql._values.values()))
# output: (`age`, `name`) VALUES (20, "Michael")
# And now we have ouput for insert. :)
# !! This is not for usege! But it's possible. !!
print(sqlpuzzle._queryparts.Limit().limit(10))
print(sqlpuzzle._queryparts.orderby.OrderBy().order_by('id'))
# etc..
try:
print(sql)
#
# output:
# (for better reading splited to more lines)
#
# SELECT `country`.`name`, AVG(`age`) AS "avgAge"
# FROM `user` LEFT JOIN `country` ON `user`.`country_id`=`country`.`id`
# WHERE (`enable` = 1 OR `vip` = 1) AND `planet` = 'Earth'
# GROUP BY `user`.`country_id`
#
table = sqlpuzzle.customsql('`user`')
set_ = sqlpuzzle.customsql('`age` = `age` + 1')
sql = sqlpuzzle.update(table).set(set_).where(where)
print(sql)
# output:
# UPDATE `user` SET `age` = `age` + 1 WHERE (`enable` = 1 OR `vip` = 1)
print(sqlpuzzle.delete_from(table).where(where))
# output:
# DELETE FROM `user` WHERE (`enable` = 1 OR `vip` = 1)
print(sqlpuzzle.insert_into(table).values(name='Alan'))
# output:
# INSERT INTO `user` (`name`) VALUES ("Alan")
def test_insert_into():
sql = sqlpuzzle.insert_into('user').values(name='Harry')
assert str(sql) == 'INSERT INTO "user" ("name") VALUES (\'Harry\')'
def test_insert_into(self):
sql = sqlpuzzle.insert_into('user').values(name='Harry')
self.assertEqual(str(sql), 'INSERT INTO "user" ("name") VALUES (\'Harry\')')
sql.values(name='Alan')
sql.values({
'enabled': True,
})
sql.values('age', 42)
sql.values(('salary', 12345.67), ('last_modify', datetime.datetime(2011, 6, 15)))
values = (
('age', 21),
('loginname', 'alan')
)
sql.values(values)
print(sql)
#
# output:
# (for better reading splited to more lines)
#
# INSERT INTO `table`
# (`salary`, `last_modify`, `loginname`, `name`, `age`, `enabled`)
# VALUES
# (12345.67000, "2011-06-15T00:00:00", "alan", "Alan", 21, 1)
#
print(sqlpuzzle.insert_into('table').values(values))
# output:
# INSERT INTO `table` (`age`, `loginname`) VALUES (21, "alan")
def test_on_duplicate_key_update(sqlite):
sql = sqlpuzzle.insert_into('user')
sql.values(id=1, name='Alan')
sql.on_duplicate_key_update()
assert str(sql) == 'REPLACE INTO "user" ("id", "name") VALUES (1, \'Alan\')'
def test_on_duplicate_key_update(sqlite):
sql = sqlpuzzle.insert_into('user')
sql.values(id=1, name='Alan')
sql.on_duplicate_key_update()
assert str(
sql) == 'REPLACE INTO "user" ("id", "name") VALUES (1, \'Alan\')'
def test_insert_into():
sql = sqlpuzzle.insert_into('user').values(name='Harry')
assert str(sql) == 'INSERT INTO "user" ("name") VALUES (\'Harry\')'
def test_on_duplicate_key_update(self):
sql = sqlpuzzle.insert_into("user")
sql.values(id=1, name="Alan")
sql.on_duplicate_key_update()
self.assertEqual(str(sql), 'REPLACE INTO "user" ("id", "name") VALUES (1, \'Alan\')')
