def test_create_table_with_select_and_columns_fails(self): select = Query.from_(self.existing_table).select(self.existing_table.foo, self.existing_table.bar) with self.subTest("for columns before as_select"): with self.assertRaises(AttributeError): Query.create_table(self.new_table).columns(self.foo, self.bar).as_select(select) with self.subTest("for as_select before columns"): with self.assertRaises(AttributeError): Query.create_table(self.new_table).as_select(select).columns(self.foo, self.bar)
def test_create_table_with_columns(self): with self.subTest("without temporary keyword"): q = Query.create_table(self.new_table).columns(self.foo, self.bar) self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100))', str(q)) with self.subTest("with temporary keyword"): q = (Query.create_table(self.new_table).temporary().columns( self.foo, self.bar)) self.assertEqual( 'CREATE TEMPORARY TABLE "abc" ("a" INT,"b" VARCHAR(100))', str(q))
def test_create_table_with_columns(self): with self.subTest("with nullable"): a = Column("a", "INT", True) b = Column("b", "VARCHAR(100)", False) q = Query.create_table(self.new_table).columns(a, b) self.assertEqual('CREATE TABLE "abc" ("a" INT NULL,"b" VARCHAR(100) NOT NULL)', str(q)) with self.subTest("with defaults"): a = Column("a", "INT", default=ValueWrapper(42)) b = Column("b", "VARCHAR(100)", default=ValueWrapper("foo")) q = Query.create_table(self.new_table).columns(a, b) self.assertEqual('CREATE TABLE "abc" ("a" INT DEFAULT 42,"b" VARCHAR(100) DEFAULT \'foo\')', str(q)) with self.subTest("with period for"): a = Column("id", "INT") b = Column("valid_from", "DATETIME") c = Column("valid_to", "DATETIME") q = Query.create_table(self.new_table).columns(a, b, c).period_for('valid_period', b, c) self.assertEqual( 'CREATE TABLE "abc" (' '"id" INT,' '"valid_from" DATETIME,' '"valid_to" DATETIME,' 'PERIOD FOR "valid_period" ("valid_from","valid_to"))', str(q), ) with self.subTest("without temporary keyword"): q = Query.create_table(self.new_table).columns(self.foo, self.bar) self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100))', str(q)) with self.subTest("with temporary keyword"): q = Query.create_table(self.new_table).temporary().columns(self.foo, self.bar) self.assertEqual('CREATE TEMPORARY TABLE "abc" ("a" INT,"b" VARCHAR(100))', str(q)) with self.subTest("with primary key"): q = Query.create_table(self.new_table).columns(self.foo, self.bar).primary_key(self.foo, self.bar) self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100),PRIMARY KEY ("a","b"))', str(q)) with self.subTest("with unique keys"): q = ( Query.create_table(self.new_table) .columns(self.foo, self.bar) .unique(self.foo, self.bar) .unique(self.foo) ) self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100),UNIQUE ("a","b"),UNIQUE ("a"))', str(q)) with self.subTest("with system versioning"): q = Query.create_table(self.new_table).columns(self.foo, self.bar).with_system_versioning() self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100)) WITH SYSTEM VERSIONING', str(q))
def test_create_table_with_select(self): select = Query.from_(self.existing_table).select(self.existing_table.foo, self.existing_table.bar) with self.subTest("without temporary keyword"): q = Query.create_table(self.new_table).as_select(select) self.assertEqual('CREATE TABLE "abc" AS (SELECT "foo","bar" FROM "efg")', str(q)) with self.subTest("with temporary keyword"): q = Query.create_table(self.new_table).temporary().as_select(select) self.assertEqual( 'CREATE TEMPORARY TABLE "abc" AS (SELECT "foo","bar" FROM "efg")', str(q), )
def make_table(table_def, conn_params): table = Table(table_def["name"]) cols = [Column(k, v) for k, v, in table_def["columns"].items()] drop = Query.drop_table(table).if_exists() create = Query.create_table(table).columns(*cols) execute_query(str(drop) + "\n" + str(create), conn_params)
def create_table(self, table: str) -> int: target = Table(table, schema=self.schema) columns = [ Column(name, python_type_to_sql(annotation)) for name, annotation in Entry.__annotations__.items() ] query = Query.create_table(target).columns(*columns) sql = str(query) logging.debug(sql) with self.transaction() as cursor: return cast(int, cursor.execute(sql))
def createTableIfNotExists(repo: Dolt, table: str): query: CreateQueryBuilder = Query.create_table(table=table) \ .columns( Column("id", "bigint unsigned", nullable=False), Column("twitter_user_id", "bigint unsigned", nullable=False), Column("date", "datetime", nullable=False), Column("text", "longtext", nullable=False), Column("device", "longtext", nullable=False), Column("favorites", "bigint unsigned", nullable=False), Column("retweets", "bigint unsigned", nullable=False), Column("quoteTweets", "bigint unsigned"), Column("replies", "bigint unsigned"), Column("isRetweet", "tinyint", nullable=False), Column("isDeleted", "tinyint", nullable=False), Column("repliedToTweetId", "bigint unsigned"), Column("repliedToUserId", "bigint unsigned"), Column("repliedToTweetDate", "datetime"), Column("retweetedTweetId", "bigint unsigned"), Column("retweetedUserId", "bigint unsigned"), Column("retweetedTweetDate", "datetime"), Column("expandedUrls", "longtext"), Column("json", "longtext"), Column("json_v1", "longtext"), Column("notes", "longtext") ).primary_key("id") # TODO: Figure Out How To Add The Below Parameters # -------------------------------------------------------------------------------------------------------------- # KEY `twitter_user_id_idx` (`twitter_user_id`), # CONSTRAINT `twitter_user_id_ref` FOREIGN KEY (`twitter_user_id`) REFERENCES `government` (`twitter_user_id`) # -------------------------------------------------------------------------------------------------------------- # ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; # -------------------------------------------------------------------------------------------------------------- repo.sql(query=query.get_sql(quote_char=None), result_format="csv")
def test_create_table_as_select_not_query_raises_error(self): with self.assertRaises(TypeError): Query.create_table(self.new_table).as_select("abc")
def test_create_table_without_columns_or_select_empty(self): q = Query.create_table(self.new_table) self.assertEqual("", str(q))
def test_create_table_with_columns(self): with self.subTest("with nullable"): a = Column("a", "INT", True) b = Column("b", "VARCHAR(100)", False) q = Query.create_table(self.new_table).columns(a, b) self.assertEqual( 'CREATE TABLE "abc" ("a" INT NULL,"b" VARCHAR(100) NOT NULL)', str(q)) with self.subTest("with defaults"): a = Column("a", "INT", default=ValueWrapper(42)) b = Column("b", "VARCHAR(100)", default=ValueWrapper("foo")) q = Query.create_table(self.new_table).columns(a, b) self.assertEqual( 'CREATE TABLE "abc" ("a" INT DEFAULT 42,"b" VARCHAR(100) DEFAULT \'foo\')', str(q)) with self.subTest("with unwrapped defaults"): a = Column("a", "INT", default=42) b = Column("b", "VARCHAR(100)", default="foo") q = Query.create_table(self.new_table).columns(a, b) self.assertEqual( 'CREATE TABLE "abc" ("a" INT DEFAULT 42,"b" VARCHAR(100) DEFAULT \'foo\')', str(q)) with self.subTest("with period for"): a = Column("id", "INT") b = Column("valid_from", "DATETIME") c = Column("valid_to", "DATETIME") q = Query.create_table(self.new_table).columns(a, b, c).period_for( 'valid_period', b, c) self.assertEqual( 'CREATE TABLE "abc" (' '"id" INT,' '"valid_from" DATETIME,' '"valid_to" DATETIME,' 'PERIOD FOR "valid_period" ("valid_from","valid_to"))', str(q), ) with self.subTest("without temporary keyword"): q = Query.create_table(self.new_table).columns(self.foo, self.bar) self.assertEqual('CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100))', str(q)) with self.subTest("with temporary keyword"): q = Query.create_table(self.new_table).temporary().columns( self.foo, self.bar) self.assertEqual( 'CREATE TEMPORARY TABLE "abc" ("a" INT,"b" VARCHAR(100))', str(q)) with self.subTest("with primary key"): q = Query.create_table(self.new_table).columns( self.foo, self.bar).primary_key(self.foo, self.bar) self.assertEqual( 'CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100),PRIMARY KEY ("a","b"))', str(q)) with self.subTest("with simple foreign key"): cref, dref = Columns(("c", "INT"), ("d", "VARCHAR(100)")) q = (Query.create_table(self.new_table).columns( self.foo, self.bar).foreign_key([self.foo, self.bar], self.existing_table, [cref, dref])) self.assertEqual( 'CREATE TABLE "abc" (' '"a" INT,' '"b" VARCHAR(100),' 'FOREIGN KEY ("a","b") REFERENCES "efg" ("c","d"))', str(q), ) with self.subTest("with foreign key reference options"): cref, dref = Columns(("c", "INT"), ("d", "VARCHAR(100)")) q = (Query.create_table(self.new_table).columns( self.foo, self.bar).foreign_key( [self.foo, self.bar], self.existing_table, [cref, dref], on_delete=ReferenceOption.cascade, on_update=ReferenceOption.restrict, )) self.assertEqual( 'CREATE TABLE "abc" (' '"a" INT,' '"b" VARCHAR(100),' 'FOREIGN KEY ("a","b") REFERENCES "efg" ("c","d") ON DELETE CASCADE ON UPDATE RESTRICT)', str(q), ) with self.subTest("with unique keys"): q = (Query.create_table(self.new_table).columns( self.foo, self.bar).unique(self.foo, self.bar).unique(self.foo)) self.assertEqual( 'CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100),UNIQUE ("a","b"),UNIQUE ("a"))', str(q)) with self.subTest("with system versioning"): q = Query.create_table(self.new_table).columns( self.foo, self.bar).with_system_versioning() self.assertEqual( 'CREATE TABLE "abc" ("a" INT,"b" VARCHAR(100)) WITH SYSTEM VERSIONING', str(q)) with self.subTest("with unlogged keyword"): q = Query.create_table(self.new_table).unlogged().columns( self.foo, self.bar) self.assertEqual( 'CREATE UNLOGGED TABLE "abc" ("a" INT,"b" VARCHAR(100))', str(q)) with self.subTest("with if not exists keyword"): q = Query.create_table(self.new_table).if_not_exists().columns( self.foo, self.bar) self.assertEqual( 'CREATE TABLE IF NOT EXISTS "abc" ("a" INT,"b" VARCHAR(100))', str(q))
from pypika import Query, Column from dotenv import load_dotenv from db import make_connection def execute(statement): try: con.cursor().execute(statement) except Exception as e: errors.append(e) load_dotenv() con = make_connection() errors = [] execute( Query.create_table('distances').columns( Column('distance', 'REAL', nullable=False), Column('timestamp', 'INT', nullable=False)).get_sql()) execute( Query.create_table('settings').columns( Column('key', 'TEXT', nullable=False), Column('value', 'TEXT', nullable=False)).unique("key").get_sql()) con.commit() con.close() for e in errors: print(f"error: {e}")