| 29 | assert "newtest" in tables |
| 30 | |
| 31 | def testQueries(self, db): |
| 32 | # Test insert |
| 33 | for i in range(100): |
| 34 | db.execute("INSERT INTO test ?", {"test_id": i, "title": "Test #%s" % i}) |
| 35 | |
| 36 | assert db.execute("SELECT COUNT(*) AS num FROM test").fetchone()["num"] == 100 |
| 37 | |
| 38 | # Test single select |
| 39 | assert db.execute("SELECT COUNT(*) AS num FROM test WHERE ?", {"test_id": 1}).fetchone()["num"] == 1 |
| 40 | |
| 41 | # Test multiple select |
| 42 | assert db.execute("SELECT COUNT(*) AS num FROM test WHERE ?", {"test_id": [1, 2, 3]}).fetchone()["num"] == 3 |
| 43 | assert db.execute( |
| 44 | "SELECT COUNT(*) AS num FROM test WHERE ?", |
| 45 | {"test_id": [1, 2, 3], "title": "Test #2"} |
| 46 | ).fetchone()["num"] == 1 |
| 47 | assert db.execute( |
| 48 | "SELECT COUNT(*) AS num FROM test WHERE ?", |
| 49 | {"test_id": [1, 2, 3], "title": ["Test #2", "Test #3", "Test #4"]} |
| 50 | ).fetchone()["num"] == 2 |
| 51 | |
| 52 | # Test multiple select using named params |
| 53 | assert db.execute("SELECT COUNT(*) AS num FROM test WHERE test_id IN :test_id", {"test_id": [1, 2, 3]}).fetchone()["num"] == 3 |
| 54 | assert db.execute( |
| 55 | "SELECT COUNT(*) AS num FROM test WHERE test_id IN :test_id AND title = :title", |
| 56 | {"test_id": [1, 2, 3], "title": "Test #2"} |
| 57 | ).fetchone()["num"] == 1 |
| 58 | assert db.execute( |
| 59 | "SELECT COUNT(*) AS num FROM test WHERE test_id IN :test_id AND title IN :title", |
| 60 | {"test_id": [1, 2, 3], "title": ["Test #2", "Test #3", "Test #4"]} |
| 61 | ).fetchone()["num"] == 2 |
| 62 | |
| 63 | # Large ammount of IN values |
| 64 | assert db.execute( |
| 65 | "SELECT COUNT(*) AS num FROM test WHERE ?", |
| 66 | {"not__test_id": list(range(2, 3000))} |
| 67 | ).fetchone()["num"] == 2 |
| 68 | assert db.execute( |
| 69 | "SELECT COUNT(*) AS num FROM test WHERE ?", |
| 70 | {"test_id": list(range(50, 3000))} |
| 71 | ).fetchone()["num"] == 50 |
| 72 | |
| 73 | assert db.execute( |
| 74 | "SELECT COUNT(*) AS num FROM test WHERE ?", |
| 75 | {"not__title": ["Test #%s" % i for i in range(50, 3000)]} |
| 76 | ).fetchone()["num"] == 50 |
| 77 | |
| 78 | assert db.execute( |
| 79 | "SELECT COUNT(*) AS num FROM test WHERE ?", |
| 80 | {"title__like": "%20%"} |
| 81 | ).fetchone()["num"] == 1 |
| 82 | |
| 83 | # Test named parameter escaping |
| 84 | assert db.execute( |
| 85 | "SELECT COUNT(*) AS num FROM test WHERE test_id = :test_id AND title LIKE :titlelike", |
| 86 | {"test_id": 1, "titlelike": "Test%"} |
| 87 | ).fetchone()["num"] == 1 |
| 88 | |