class PostgresTests(SQLTests):
@classmethod
def setUpClass(self):
self.db = SQL("postgresql://postgres@localhost/test")
def setUp(self):
self.db.execute(
"CREATE TABLE cs50 (id SERIAL PRIMARY KEY, val VARCHAR(16))")
def test_commit(self):
self.db.execute("BEGIN")
self.db.execute("INSERT INTO cs50 (val) VALUES('foo')")
self.db.execute("COMMIT")
# Load a new database instance to confirm the INSERT was committed
db2 = SQL(self.db_url)
self.assertEqual(db2.execute("SELECT val FROM cs50"), [{"val": "foo"}])
class MySQLTests(SQLTests):
@classmethod
def setUpClass(self):
self.db = SQL("mysql://root@localhost/test")
def setUp(self):
self.db.execute(
"CREATE TABLE cs50 (id INTEGER NOT NULL AUTO_INCREMENT, val VARCHAR(16), PRIMARY KEY (id))"
)
def test_autocommit(self):
self.assertEqual(
self.db.execute("INSERT INTO cs50(val) VALUES('foo')"), 1)
self.assertEqual(
self.db.execute("INSERT INTO cs50(val) VALUES('bar')"), 2)
# Load a new database instance to confirm the INSERTs were committed
db2 = SQL(self.db_url)
self.assertEqual(db2.execute("DELETE FROM cs50 WHERE id < 3"), 2)
class SQLiteTests(SQLTests):
@classmethod
def setUpClass(self):
self.db = SQL("sqlite:///test.db")
def setUp(self):
self.db.execute("CREATE TABLE cs50(id INTEGER PRIMARY KEY, val TEXT)")
def multi_inserts_enabled(self):
return False
class PostgresTests(SQLTests):
@classmethod
def setUpClass(self):
self.db = SQL("postgresql://postgres@localhost/test")
def setUp(self):
self.db.execute(
"CREATE TABLE cs50 (id SERIAL PRIMARY KEY, val VARCHAR(16), bin BYTEA)"
)
def test_cte(self):
self.assertEqual(
self.db.execute(
"WITH foo AS ( SELECT 1 AS bar ) SELECT bar FROM foo"),
[{
"bar": 1
}])
def setUpClass(self):
self.db = SQL("postgresql://postgres@localhost/test")
def setUpClass(self):
self.db = SQL("mysql://root@localhost/test")
def setUpClass(self):
open("test.db", "w").close()
self.db = SQL("sqlite:///test.db")
open("test1.db", "w").close()
self.db1 = SQL("sqlite:///test1.db", foreign_keys=True)
class SQLiteTests(SQLTests):
@classmethod
def setUpClass(self):
open("test.db", "w").close()
self.db = SQL("sqlite:///test.db")
open("test1.db", "w").close()
self.db1 = SQL("sqlite:///test1.db", foreign_keys=True)
def setUp(self):
self.db.execute("DROP TABLE IF EXISTS cs50")
self.db.execute("CREATE TABLE cs50(id INTEGER PRIMARY KEY, val TEXT)")
def test_foreign_key_support(self):
self.db.execute("DROP TABLE IF EXISTS foo")
self.db.execute("CREATE TABLE foo(id INTEGER PRIMARY KEY)")
self.db.execute("DROP TABLE IF EXISTS bar")
self.db.execute(
"CREATE TABLE bar(foo_id INTEGER, FOREIGN KEY (foo_id) REFERENCES foo(id))"
)
self.assertEqual(self.db.execute("INSERT INTO bar VALUES(50)"), 1)
self.db1.execute("DROP TABLE IF EXISTS foo")
self.db1.execute("CREATE TABLE foo(id INTEGER PRIMARY KEY)")
self.db1.execute("DROP TABLE IF EXISTS bar")
self.db1.execute(
"CREATE TABLE bar(foo_id INTEGER, FOREIGN KEY (foo_id) REFERENCES foo(id))"
)
self.assertEqual(self.db1.execute("INSERT INTO bar VALUES(50)"), None)
def test_qmark(self):
self.db.execute("DROP TABLE IF EXISTS foo")
self.db.execute("CREATE TABLE foo (firstname STRING, lastname STRING)")
self.db.execute("INSERT INTO foo VALUES (?, 'bar')", "baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "baz",
"lastname": "bar"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES ('bar', ?)", "baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (?, ?)", "bar", "baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("INSERT INTO foo VALUES ('qux', 'quux')")
self.assertEqual(
self.db.execute("SELECT * FROM foo WHERE firstname = ?", 'qux'),
[{
"firstname": "qux",
"lastname": "quux"
}])
self.assertEqual(
self.db.execute(
"SELECT * FROM foo WHERE firstname = ? AND lastname = ?",
"qux", "quux"), [{
"firstname": "qux",
"lastname": "quux"
}])
self.assertEqual(
self.db.execute(
"SELECT * FROM foo WHERE firstname = ? AND lastname = ?",
("qux", "quux")), [{
"firstname": "qux",
"lastname": "quux"
}])
self.assertEqual(
self.db.execute(
"SELECT * FROM foo WHERE firstname = ? AND lastname = ?",
["qux", "quux"]), [{
"firstname": "qux",
"lastname": "quux"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (?, ?)", ("bar", "baz"))
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (?, ?)", ["bar", "baz"])
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (?,?)", "bar", "baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("DROP TABLE IF EXISTS bar")
self.db.execute("CREATE TABLE bar (firstname STRING)")
self.db.execute("INSERT INTO bar VALUES (?)", "baz")
self.assertEqual(self.db.execute("SELECT * FROM bar"),
[{
"firstname": "baz"
}])
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (?)")
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (?, ?)")
# self.assertRaises(RuntimeError, self.db.execute, "INSERT INTO foo VALUES (?)", ('bar', 'baz'))
# self.assertRaises(RuntimeError, self.db.execute, "INSERT INTO foo VALUES (?)", ['bar', 'baz'])
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (?, ?)", 'bar', 'baz', 'qux')
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (?, ?)",
('bar', 'baz', 'qux'))
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (?, ?)",
['bar', 'baz', 'qux'])
self.assertRaises(RuntimeError,
self.db.execute,
"INSERT INTO foo VALUES (?, ?)",
'bar',
baz='baz')
def test_named(self):
self.db.execute("DROP TABLE IF EXISTS foo")
self.db.execute("CREATE TABLE foo (firstname STRING, lastname STRING)")
self.db.execute("INSERT INTO foo VALUES (:baz, 'bar')", baz="baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "baz",
"lastname": "bar"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES ('bar', :baz)", baz="baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (:bar, :baz)",
bar="bar",
baz="baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("INSERT INTO foo VALUES ('qux', 'quux')")
self.assertEqual(
self.db.execute("SELECT * FROM foo WHERE firstname = :qux",
qux='qux'), [{
"firstname": "qux",
"lastname": "quux"
}])
self.assertEqual(
self.db.execute(
"SELECT * FROM foo WHERE firstname = :qux AND lastname = :quux",
qux="qux",
quux="quux"), [{
"firstname": "qux",
"lastname": "quux"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (:bar,:baz)",
bar="bar",
baz="baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("DROP TABLE IF EXISTS bar")
self.db.execute("CREATE TABLE bar (firstname STRING)")
self.db.execute("INSERT INTO bar VALUES (:baz)", baz="baz")
self.assertEqual(self.db.execute("SELECT * FROM bar"),
[{
"firstname": "baz"
}])
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (:bar)")
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (:bar, :baz)")
self.assertRaises(RuntimeError,
self.db.execute,
"INSERT INTO foo VALUES (:bar, :baz)",
bar='bar',
baz='baz',
qux='qux')
self.assertRaises(RuntimeError,
self.db.execute,
"INSERT INTO foo VALUES (:bar, :baz)",
'baz',
bar='bar')
def test_numeric(self):
self.db.execute("DROP TABLE IF EXISTS foo")
self.db.execute("CREATE TABLE foo (firstname STRING, lastname STRING)")
self.db.execute("INSERT INTO foo VALUES (:1, 'bar')", "baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "baz",
"lastname": "bar"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES ('bar', :1)", "baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (:1, :2)", "bar", "baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("INSERT INTO foo VALUES ('qux', 'quux')")
self.assertEqual(
self.db.execute("SELECT * FROM foo WHERE firstname = :1", 'qux'),
[{
"firstname": "qux",
"lastname": "quux"
}])
self.assertEqual(
self.db.execute(
"SELECT * FROM foo WHERE firstname = :1 AND lastname = :2",
"qux", "quux"), [{
"firstname": "qux",
"lastname": "quux"
}])
self.assertEqual(
self.db.execute(
"SELECT * FROM foo WHERE firstname = :1 AND lastname = :2",
("qux", "quux")), [{
"firstname": "qux",
"lastname": "quux"
}])
self.assertEqual(
self.db.execute(
"SELECT * FROM foo WHERE firstname = :1 AND lastname = :2",
["qux", "quux"]), [{
"firstname": "qux",
"lastname": "quux"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (:1, :2)", ("bar", "baz"))
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (:1, :2)", ["bar", "baz"])
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (:1,:2)", "bar", "baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("DROP TABLE IF EXISTS bar")
self.db.execute("CREATE TABLE bar (firstname STRING)")
self.db.execute("INSERT INTO bar VALUES (:1)", "baz")
self.assertEqual(self.db.execute("SELECT * FROM bar"),
[{
"firstname": "baz"
}])
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (:1)")
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (:1, :2)")
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (:1, :2)", 'bar', 'baz',
'qux')
self.assertRaises(RuntimeError,
self.db.execute,
"INSERT INTO foo VALUES (:1, :2)",
'bar',
baz='baz')
class SQLiteTests(SQLTests):
@classmethod
def setUpClass(self):
self.db = SQL("sqlite:///test.db")
self.db1 = SQL("sqlite:///test1.db", foreign_keys=True)
def setUp(self):
self.db.execute("CREATE TABLE cs50(id INTEGER PRIMARY KEY, val TEXT)")
def test_foreign_key_support(self):
self.db.execute("CREATE TABLE foo(id INTEGER PRIMARY KEY)")
self.db.execute(
"CREATE TABLE bar(foo_id INTEGER, FOREIGN KEY (foo_id) REFERENCES foo(id))"
)
self.assertEqual(self.db.execute("INSERT INTO bar VALUES(50)"), 1)
self.db1.execute("CREATE TABLE foo(id INTEGER PRIMARY KEY)")
self.db1.execute(
"CREATE TABLE bar(foo_id INTEGER, FOREIGN KEY (foo_id) REFERENCES foo(id))"
)
self.assertEqual(self.db1.execute("INSERT INTO bar VALUES(50)"), None)
def setUpClass(self):
self.db = SQL("sqlite:///test.db")
def setUpClass(self):
open("test.db", "w").close()
self.db_url = "sqlite:///test.db"
self.db = SQL(self.db_url)
print("\nSQLite tests")
def setUpClass(self):
self.db_url = "postgresql://postgres@localhost/test"
self.db = SQL(self.db_url)
print("\nPOSTGRES tests")
def setUpClass(self):
self.db_url = "mysql://root@localhost/test"
self.db = SQL(self.db_url)
print("\nMySQL tests")
class SQLiteTests(SQLTests):
@classmethod
def setUpClass(self):
open("test.db", "w").close()
self.db = SQL("sqlite:///test.db")
def setUp(self):
self.db.execute(
"CREATE TABLE IF NOT EXISTS cs50 (id INTEGER PRIMARY KEY, val TEXT, bin BLOB)"
)
self.db.execute("DELETE FROM cs50")
def test_lastrowid(self):
self.db.execute(
"CREATE TABLE foo(id INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT)"
)
self.assertEqual(
self.db.execute(
"INSERT INTO foo (firstname, lastname) VALUES('firstname', 'lastname')"
), 1)
self.assertRaises(
ValueError, self.db.execute,
"INSERT INTO foo (id, firstname, lastname) VALUES(1, 'firstname', 'lastname')"
)
self.assertEqual(
self.db.execute(
"INSERT OR IGNORE INTO foo (id, firstname, lastname) VALUES(1, 'firstname', 'lastname')"
), None)
def test_integrity_constraints(self):
self.db.execute("CREATE TABLE foo(id INTEGER PRIMARY KEY)")
self.assertEqual(self.db.execute("INSERT INTO foo VALUES(1)"), 1)
self.assertRaises(ValueError, self.db.execute,
"INSERT INTO foo VALUES(1)")
def test_foreign_key_support(self):
self.db.execute("CREATE TABLE foo(id INTEGER PRIMARY KEY)")
self.db.execute(
"CREATE TABLE bar(foo_id INTEGER, FOREIGN KEY (foo_id) REFERENCES foo(id))"
)
self.assertRaises(ValueError, self.db.execute,
"INSERT INTO bar VALUES(50)")
def test_qmark(self):
self.db.execute("CREATE TABLE foo (firstname STRING, lastname STRING)")
self.db.execute("INSERT INTO foo VALUES (?, 'bar')", "baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "baz",
"lastname": "bar"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES ('bar', ?)", "baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (?, ?)", "bar", "baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("INSERT INTO foo VALUES ('qux', 'quux')")
self.assertEqual(
self.db.execute("SELECT * FROM foo WHERE firstname = ?", 'qux'),
[{
"firstname": "qux",
"lastname": "quux"
}])
self.assertEqual(
self.db.execute(
"SELECT * FROM foo WHERE firstname = ? AND lastname = ?",
"qux", "quux"), [{
"firstname": "qux",
"lastname": "quux"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (?)", ("bar", "baz"))
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (?)", ["bar", "baz"])
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (?,?)", "bar", "baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("CREATE TABLE bar (firstname STRING)")
self.db.execute("INSERT INTO bar VALUES (?)", "baz")
self.assertEqual(self.db.execute("SELECT * FROM bar"),
[{
"firstname": "baz"
}])
self.db.execute("DELETE FROM bar")
self.db.execute("INSERT INTO bar VALUES (?)", "baz")
self.db.execute("INSERT INTO bar VALUES (?)", "qux")
self.assertEqual(
self.db.execute("SELECT * FROM bar WHERE firstname IN (?)",
("baz", "qux")), [{
"firstname": "baz"
}, {
"firstname": "qux"
}])
self.db.execute("DELETE FROM bar")
self.db.execute("INSERT INTO bar VALUES (?)", "baz")
self.db.execute("INSERT INTO bar VALUES (?)", "qux")
self.assertEqual(
self.db.execute("SELECT * FROM bar WHERE firstname IN (?)",
["baz", "qux"]), [{
"firstname": "baz"
}, {
"firstname": "qux"
}])
self.db.execute("DELETE FROM bar")
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (?)")
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (?, ?)")
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (?, ?)", 'bar', 'baz', 'qux')
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (?, ?)",
('bar', 'baz', 'qux'))
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (?, ?)",
['bar', 'baz', 'qux'])
self.assertRaises(RuntimeError,
self.db.execute,
"INSERT INTO foo VALUES (?, ?)",
'bar',
baz='baz')
def test_named(self):
self.db.execute("CREATE TABLE foo (firstname STRING, lastname STRING)")
self.db.execute("INSERT INTO foo VALUES (:baz, 'bar')", baz="baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "baz",
"lastname": "bar"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES ('bar', :baz)", baz="baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (:bar, :baz)",
bar="bar",
baz="baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("INSERT INTO foo VALUES ('qux', 'quux')")
self.assertEqual(
self.db.execute("SELECT * FROM foo WHERE firstname = :qux",
qux='qux'), [{
"firstname": "qux",
"lastname": "quux"
}])
self.assertEqual(
self.db.execute(
"SELECT * FROM foo WHERE firstname = :qux AND lastname = :quux",
qux="qux",
quux="quux"), [{
"firstname": "qux",
"lastname": "quux"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (:bar,:baz)",
bar="bar",
baz="baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("CREATE TABLE bar (firstname STRING)")
self.db.execute("INSERT INTO bar VALUES (:baz)", baz="baz")
self.assertEqual(self.db.execute("SELECT * FROM bar"),
[{
"firstname": "baz"
}])
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (:bar)")
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (:bar, :baz)")
self.assertRaises(RuntimeError,
self.db.execute,
"INSERT INTO foo VALUES (:bar, :baz)",
bar='bar',
baz='baz',
qux='qux')
self.assertRaises(RuntimeError,
self.db.execute,
"INSERT INTO foo VALUES (:bar, :baz)",
'baz',
bar='bar')
def test_numeric(self):
self.db.execute("CREATE TABLE foo (firstname STRING, lastname STRING)")
self.db.execute("INSERT INTO foo VALUES (:1, 'bar')", "baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "baz",
"lastname": "bar"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES ('bar', :1)", "baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (:1, :2)", "bar", "baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("INSERT INTO foo VALUES ('qux', 'quux')")
self.assertEqual(
self.db.execute("SELECT * FROM foo WHERE firstname = :1", 'qux'),
[{
"firstname": "qux",
"lastname": "quux"
}])
self.assertEqual(
self.db.execute(
"SELECT * FROM foo WHERE firstname = :1 AND lastname = :2",
"qux", "quux"), [{
"firstname": "qux",
"lastname": "quux"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("INSERT INTO foo VALUES (:1,:2)", "bar", "baz")
self.assertEqual(self.db.execute("SELECT * FROM foo"),
[{
"firstname": "bar",
"lastname": "baz"
}])
self.db.execute("DELETE FROM foo")
self.db.execute("CREATE TABLE bar (firstname STRING)")
self.db.execute("INSERT INTO bar VALUES (:1)", "baz")
self.assertEqual(self.db.execute("SELECT * FROM bar"),
[{
"firstname": "baz"
}])
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (:1)")
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (:1, :2)")
self.assertRaises(RuntimeError, self.db.execute,
"INSERT INTO foo VALUES (:1, :2)", 'bar', 'baz',
'qux')
self.assertRaises(RuntimeError,
self.db.execute,
"INSERT INTO foo VALUES (:1, :2)",
'bar',
baz='baz')
def test_cte(self):
self.assertEqual(
self.db.execute(
"WITH foo AS ( SELECT 1 AS bar ) SELECT bar FROM foo"),
[{
"bar": 1
}])
def setUpClass(self):
open("test.db", "w").close()
self.db = SQL("sqlite:///test.db")
def setUpClass(self):
self.db = SQL("sqlite:///test.db")
self.db1 = SQL("sqlite:///test1.db", foreign_keys=True)