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''')
