import os.path
import sqlite3
import unittest


class DataTestCase(unittest.TestCase):
    def test_create(self):
        sql_script_path = os.path.join(os.path.dirname(__file__), "scripts/data/Data.sql")
        with open(sql_script_path, "rt", encoding="utf-8") as sql_script_file:
            sql_script = sql_script_file.read()

        connection = sqlite3.connect(":memory:", autocommit=False)
        cursor = connection.cursor()
        cursor.executescript(sql_script)
        connection.commit()

        cursor.execute("INSERT INTO authors VALUES (1, 'Alice')")
        cursor.execute("INSERT INTO authors VALUES (2, 'Bob')")
        cursor.execute("INSERT INTO authors VALUES (3, 'Carol')")

        cursor.execute("SELECT id, name FROM authors WHERE id > 1 ORDER BY id")
        rows = cursor.fetchall()

        self.assertEqual(rows, [(2, "Bob"), (3, "Carol")])