class QueryBuilderTest(unittest.TestCase): def setUp(self): self.builder = QueryBuilder() def test_select_column_from_table(self): self.builder.select("account_name").from_table("account") sql = self.builder.generate_sql() self.assertEqual("SELECT account_name FROM account;", sql) def test_select_star_from_table(self): self.builder.select("*").from_table("account") sql = self.builder.generate_sql() self.assertEqual("SELECT * FROM account;", sql) def test_column_defaults_to_star(self): self.builder.from_table("account") sql = self.builder.generate_sql() self.assertEqual("SELECT * FROM account;", sql) def test_column_can_take_list(self): self.builder.select(["col1", "col2"]).from_table("account") sql = self.builder.generate_sql() self.assertEqual("SELECT col1, col2 FROM account;", sql) def test_select_with_where_clause(self): self.builder.select("account_name").from_table("account").where("account_id", 13977) sql = self.builder.generate_sql() self.assertEqual("SELECT account_name FROM account WHERE account_id=13977;", sql)
class QueryBuilderTest(unittest.TestCase): def setUp(self): self.builder = QueryBuilder() def test_select_column_from_table(self): self.builder.select('account_name').from_table('account') sql = self.builder.generate_sql() self.assertEqual('SELECT account_name FROM account;', sql) def test_select_star_from_table(self): self.builder.select('*').from_table('account') sql = self.builder.generate_sql() self.assertEqual('SELECT * FROM account;', sql) def test_column_defaults_to_star(self): self.builder.from_table('account') sql = self.builder.generate_sql() self.assertEqual('SELECT * FROM account;', sql) def test_column_can_take_list(self): self.builder.select(['col1', 'col2']).from_table('account') sql = self.builder.generate_sql() self.assertEqual('SELECT col1, col2 FROM account;', sql) def test_select_with_where_clause(self): self.builder.select('account_name').from_table('account').where( 'account_id', 13977) sql = self.builder.generate_sql() self.assertEqual( 'SELECT account_name FROM account WHERE account_id=13977;', sql)
def testSqlite3(self): import sqlite3 builder = QueryBuilder() conn = sqlite3.connect('tests/Chinook_Sqlite_AutoIncrementPKs.sqlite') builder.add_table_with_fields('album', 'a', conn) query = builder.select(['albumid', 'artistid']) self.assertQuery(query, 'select a.albumid albumid, a.artistid artistid from album a')
def testPostgresql(self): import psycopg2 builder = QueryBuilder() conn = psycopg2.connect("dbname=test host=%s port=%s user=%s password=%s" % ( \ self.config.get('postgresql', 'host'), self.config.get('postgresql', 'port'), self.config.get('postgresql', 'user'), self.config.get('postgresql', 'passwd'))) c = conn.cursor() c.execute('drop table if exists album') c.execute('''create table album (AlbumId int, Title char(100), ArtistId int)''') c.close() builder.add_table_with_fields('album', 'a', conn) query = builder.select(['albumid', 'artistid']) self.assertQuery(query, 'select a.albumid albumid, a.artistid artistid from album a')
def testMySql(self): import mysql.connector as mdb builder = QueryBuilder() conn = mdb.connect( host=self.config.get('mysql', 'host'), port=self.config.get('mysql', 'port'), user=self.config.get('mysql', 'user'), passwd=self.config.get('mysql', 'passwd')) c = conn.cursor() c.execute('drop table if exists test.album') c.execute('''create table if not exists test.album (AlbumId int, Title varchar(100), ArtistId int)''') c.close() builder.add_table_with_fields('test.album', 'a', conn) query = builder.select(['albumid', 'artistid']) self.assertQuery(query, 'select a.albumid albumid, a.artistid artistid from test.album a')
class TestQueryBuilderAggregation(TestQueryBuilderBase): def setUp(self): self.builder = QueryBuilder() self.builder.add_table('table_a', 'a') self.builder.add_field('sum(a.f1)', 'fa') self.builder.add_field('a.f2', 'ga') self.builder.add_table('table_b', 'b') self.builder.add_field('count(b.f)', 'fb') self.builder.link_tables('a.id = b.id') self.builder.add_field('sum(a.f1) / count(b.f)', 'fab') self.builder.add_field('concat(a.f2, b.f)', 'cab') self.builder.add_field('0', 'c') def testSelectSumA(self): query = self.builder.select(['fa']) self.assertQuery(query, ''' select sum(a.f1) fa from table_a a''') def testSelectCountB(self): query = self.builder.select(['fb']) self.assertQuery(query, ''' select count(b.f) fb from table_b b''') def testSelectAvgAB(self): query = self.builder.select(['fab']) self.assertQuery(query, ''' select sum(a.f1) / count(b.f) fab from table_a a, table_b b where a.id = b.id''') def testGroupBy(self): query = self.builder.group_by(['ga']).select(['ga', 'fa']) self.assertQuery(query, ''' select a.f2 ga, sum(a.f1) fa from table_a a group by a.f2''') query = self.builder.group_by(['ga']).select(['fa']) self.assertQuery(query, ''' select sum(a.f1) fa from table_a a group by a.f2''') def testGroupByFormula(self): query = self.builder.group_by(['cab']).select(['fa']) self.assertQuery(query, ''' select sum(a.f1) fa from table_a a, table_b b where a.id = b.id group by concat(a.f2, b.f)''') query = self.builder.group_by(['ga', 'cab']).select(['fa']) self.assertQuery(query, ''' select sum(a.f1) fa from table_a a, table_b b where a.id = b.id group by a.f2, concat(a.f2, b.f)''') def testHaving(self): query = self.builder.having('fa > 1').select(['fa']) self.assertQuery(query, ''' select sum(a.f1) fa from table_a a having fa > 1''') query = self.builder.group_by(['ga']).having('fa > 1').select(['fa']) self.assertQuery(query, ''' select sum(a.f1) fa from table_a a group by a.f2 having fa > 1''') def testHavingContainUnselectedAlias(self): with self.assertRaisesRegexp(Exception, "alias 'cab' needs to be selected"): self.builder.having('cab = "abc"').select(['fa']) def testSelectConstant(self): query = self.builder.select(['c']) self.assertQuery(query, 'select 0 c') query = self.builder.select(['fa', 'c']) self.assertQuery(query, ''' select sum(a.f1) fa, 0 c from table_a a''')
class TestQueryBuilder(TestQueryBuilderBase): def setUp(self): # table relationships self.builder = QueryBuilder() self.builder.add_table('table_a', 'a') self.builder.add_field('a.f', 'fa') self.builder.add_table('table_b', 'b') self.builder.link_tables('a.id = b.id') self.builder.add_field('b.f', 'fb') self.builder.add_table('table_c', 'c') self.builder.link_tables('a.id = c.id') self.builder.add_field('c.f', 'fc') self.builder.add_table('table_d', 'd') self.builder.link_tables('c.id = d.id') self.builder.add_field('d.f', 'fd') def testSelectA(self): query = self.builder.select(['fa']) self.assertQuery(query, 'select a.f fa from table_a a') def testSelectB(self): query = self.builder.select(['fb']) self.assertQuery(query, 'select b.f fb from table_b b') def testSelectC(self): query = self.builder.select(['fc']) self.assertQuery(query, 'select c.f fc from table_c c') def testSelectD(self): query = self.builder.select(['fd']) self.assertQuery(query, 'select d.f fd from table_d d') def testSelectAB(self): query = self.builder.select(['fa', 'fb']) self.assertQuery(query, ''' select a.f fa, b.f fb from table_a a, table_b b where a.id = b.id''') def testSelectAC(self): query = self.builder.select(['fa', 'fc']) self.assertQuery(query, ''' select a.f fa, c.f fc from table_a a, table_c c where a.id = c.id''') def testSelectAD(self): query = self.builder.select(['fa', 'fd']) self.assertQuery(query, ''' select a.f fa, d.f fd from table_a a, table_c c, table_d d where a.id = c.id and c.id = d.id''') def testSelectBC(self): query = self.builder.select(['fb', 'fc']) self.assertQuery(query, ''' select b.f fb, c.f fc from table_a a, table_b b, table_c c where a.id = b.id and a.id = c.id''') def testSelectBD(self): query = self.builder.select(['fb', 'fd']) self.assertQuery(query, ''' select b.f fb, d.f fd from table_a a, table_b b, table_c c, table_d d where a.id = b.id and a.id = c.id and c.id = d.id''') def testSelectCD(self): query = self.builder.select(['fc', 'fd']) self.assertQuery(query, ''' select c.f fc, d.f fd from table_c c, table_d d where c.id = d.id''') def testSelectABC(self): query = self.builder.select(['fa', 'fb', 'fc']) self.assertQuery(query, ''' select a.f fa, b.f fb, c.f fc from table_a a, table_b b, table_c c where a.id = b.id and a.id = c.id''') def testSelectABCD(self): query = self.builder.select(['fa', 'fb', 'fc', 'fd']) self.assertQuery(query, ''' select a.f fa, b.f fb, c.f fc, d.f fd from table_a a, table_b b, table_c c, table_d d where a.id = b.id and a.id = c.id and c.id = d.id''') def testSelectAWhereA(self): query = self.builder.where('fa = 1').select(['fa']) self.assertQuery(query, ''' select a.f fa from table_a a where a.f = 1''') query = self.builder.where('fa = "a"').select(['fa']) self.assertQuery(query, ''' select a.f fa from table_a a where a.f = "a"''') query = self.builder.where('fa = \'a\'').select(['fa']) self.assertQuery(query, ''' select a.f fa from table_a a where a.f = \'a\'''') def testSelectAWhereB(self): query = self.builder.where('fb = 1').select(['fa']) self.assertQuery(query, ''' select a.f fa from table_a a, table_b b where a.id = b.id and b.f = 1''') def testWhereChainable(self): query = self.builder.where('fa = 1').where('fb = 1').select(['fa']) self.assertQuery(query, ''' select a.f fa from table_a a, table_b b where a.id = b.id and a.f = 1 and b.f = 1''') def testWhereReturnNewInstance(self): self.builder.where('fb = 1').select(['fa']) query = self.builder.where('fa = 1').select(['fa']) self.assertQuery(query, ''' select a.f fa from table_a a where a.f = 1''')