def test_nested(self): # countries on the same continent as India or Iran compare_queries( """ SELECT name, continent FROM country WHERE continent IN ( SELECT continent FROM country WHERE name IN ('India', 'Iran') ) """, sql((x.name, x.continent) for x in db.country if x.continent in (y.continent for y in db.country if y.name in ['India', 'Iran']))) # countries in the same continent as Belize or Belgium compare_queries( """ SELECT w.name, w.continent FROM country w WHERE w.continent in ( SELECT z.continent FROM country z WHERE z.name = 'Belize' OR z.name = 'Belgium' ) """, sql((c.name, c.continent) for c in db.country if c.continent in (x.continent for x in db.country if (x.name == 'Belize') | (x.name == 'Belgium'))))
def test_join(self): # number of cities in Asia compare_queries( """ SELECT COUNT(t.name) FROM country c JOIN city t ON (t.country_code = c.code) WHERE c.continent = 'Asia' """, sql( func.count(t.name) for c in db.country for t in db.city if t.country_code == c.code if c.continent == 'Asia')) # name and population for each country and city where the city's # population is more than half the country's compare_queries( """ SELECT t.name, t.population, c.name, c.population FROM country c JOIN city t ON t.country_code = c.code WHERE t.population > c.population / 2 """, sql((t.name, t.population, c.name, c.population) for c in db.country for t in db.city if t.country_code == c.code if t.population > c.population / 2), lambda x: sorted(map(str, x)))
def test_aggregate(self): # the population of the world compare_queries("SELECT SUM(population) FROM country", sql(func.sum(x.population) for x in db.country)) # number of countries whose area is at least 1000000 compare_queries( "select count(*) from country where surface_area >= 1000000", sql( func.count(x.name) for x in db.country if x.surface_area >= 1000000))
def test_basic(self): # all countries in europe compare_queries( "SELECT name FROM country WHERE continent = 'Europe'", sql(x.name for x in db.country if x.continent == 'Europe')) # countries whose area is bigger than 10000000 compare_queries( "SELECT name, surface_area FROM country WHERE surface_area > 10000000", sql((x.name, x.surface_area) for x in db.country if x.surface_area > 10000000))
def test_aggregate(self): # the population of the world compare_queries( "SELECT SUM(population) FROM country", sql(func.sum(x.population) for x in db.country) ) # number of countries whose area is at least 1000000 compare_queries( "select count(*) from country where surface_area >= 1000000", sql(func.count(x.name) for x in db.country if x.surface_area >= 1000000) )
def test_basic(self): # all countries in europe compare_queries( "SELECT name FROM country WHERE continent = 'Europe'", sql(x.name for x in db.country if x.continent == 'Europe') ) # countries whose area is bigger than 10000000 compare_queries( "SELECT name, surface_area FROM country WHERE surface_area > 10000000", sql((x.name, x.surface_area) for x in db.country if x.surface_area > 10000000) )
def test_join_complicated(self): compare_queries( """ SELECT t.name, t.population, c.name, c.population FROM country c JOIN city t ON t.country_code = c.code AND t.population * 1.0 / c.population = ( SELECT MAX(tt.population * 1.0 / c.population) FROM city tt WHERE tt.country_code = t.country_code ) """, sql( (t.name, t.population, c.name, c.population) for c in db.country for t in db.city if t.country_code == c.code if t.population * 1.0 / c.population == ( func.max(tt.population * 1.0 / c.population) for tt in db.city if tt.country_code == t.country_code ) ), lambda x: sorted(map(str, x)) )
def test_order_group(self): # the name of every country sorted in order compare_queries( "SELECT c.name FROM country c ORDER BY c.population", sql((c.name for c in db.country).order_by(c.population))) # sum up the population of every country using GROUP BY instead of a JOIN compare_queries( """ SELECT t.country_code, sum(t.population) FROM city t GROUP BY t.country_code ORDER BY sum(t.population) """, sql(((t.country_code, func.sum(t.population)) for t in db.city).group_by(t.country_code).order_by( func.sum(t.population))))
def test_limit_offset(self): # bottom 10 countries by population compare_queries( "SELECT c.name FROM country c ORDER BY c.population LIMIT 10", sql((c.name for c in db.country).order_by(c.population).limit(10))) # bottom 100 to 110 countries by population compare_queries( "SELECT c.name FROM country c ORDER BY c.population LIMIT 10 OFFSET 100", sql((c.name for c in db.country).order_by( c.population).limit(10).offset(100))) # top 10 countries by population compare_queries( "SELECT c.name FROM country c ORDER BY c.population DESC LIMIT 10", sql((c.name for c in db.country).order_by(c.population.desc()).limit(10)))
def test_limit_offset(self): # bottom 10 countries by population compare_queries( "SELECT c.name FROM country c ORDER BY c.population LIMIT 10", sql((c.name for c in db.country).order_by(c.population).limit(10)) ) # bottom 100 to 110 countries by population compare_queries( "SELECT c.name FROM country c ORDER BY c.population LIMIT 10 OFFSET 100", sql((c.name for c in db.country).order_by(c.population).limit(10).offset(100)) ) # top 10 countries by population compare_queries( "SELECT c.name FROM country c ORDER BY c.population DESC LIMIT 10", sql((c.name for c in db.country).order_by(c.population.desc()).limit(10)) )
def test_order_group(self): # the name of every country sorted in order compare_queries( "SELECT c.name FROM country c ORDER BY c.population", sql((c.name for c in db.country).order_by(c.population)) ) # sum up the population of every country using GROUP BY instead of a JOIN compare_queries( """ SELECT t.country_code, sum(t.population) FROM city t GROUP BY t.country_code ORDER BY sum(t.population) """, sql(( (t.country_code, func.sum(t.population)) for t in db.city ).group_by(t.country_code) .order_by(func.sum(t.population))) )
def test_query_macro(self): query = sql( func.distinct(x.continent) for x in db.country if (func.sum(w.population) for w in db.country if w.continent == x.continent) > 100000000) sql_results = engine.execute(query).fetchall() query_macro_results = query( func.distinct(x.continent) for x in db.country if (func.sum(w.population) for w in db.country if w.continent == x.continent) > 100000000) assert sql_results == query_macro_results
def test_nested(self): # countries on the same continent as India or Iran compare_queries( """ SELECT name, continent FROM country WHERE continent IN ( SELECT continent FROM country WHERE name IN ('India', 'Iran') ) """, sql( (x.name, x.continent) for x in db.country if x.continent in ( y.continent for y in db.country if y.name in ['India', 'Iran'] ) ) ) # countries in the same continent as Belize or Belgium compare_queries( """ SELECT w.name, w.continent FROM country w WHERE w.continent in ( SELECT z.continent FROM country z WHERE z.name = 'Belize' OR z.name = 'Belgium' ) """, sql( (c.name, c.continent) for c in db.country if c.continent in ( x.continent for x in db.country if (x.name == 'Belize') | (x.name == 'Belgium') ) ) )
def test_join(self): # number of cities in Asia compare_queries( """ SELECT COUNT(t.name) FROM country c JOIN city t ON (t.country_code = c.code) WHERE c.continent = 'Asia' """, sql( func.count(t.name) for c in db.country for t in db.city if t.country_code == c.code if c.continent == 'Asia' ) ) # name and population for each country and city where the city's # population is more than half the country's compare_queries( """ SELECT t.name, t.population, c.name, c.population FROM country c JOIN city t ON t.country_code = c.code WHERE t.population > c.population / 2 """, sql( (t.name, t.population, c.name, c.population) for c in db.country for t in db.city if t.country_code == c.code if t.population > c.population / 2 ), lambda x: sorted(map(str, x)) )
def test_operators(self): # countries in europe with a DNP per capita larger than the UK compare_queries( """ SELECT name FROM country WHERE gnp/population > ( SELECT gnp/population FROM country WHERE name = 'United Kingdom' ) AND continent = 'Europe' """, sql(x.name for x in db.country if x.gnp / x.population > (y.gnp / y.population for y in db.country if y.name == 'United Kingdom') if (x.continent == 'Europe')))
def test_aliased(self): # continents whose total population is greater than 100000000 compare_queries( """ SELECT DISTINCT(x.continent) FROM country x WHERE 100000000 < ( SELECT SUM(w.population) from country w WHERE w.continent = x.continent ) """, sql( func.distinct(x.continent) for x in db.country if (func.sum(w.population) for w in db.country if w.continent == x.continent).as_scalar() > 100000000))
def test_query_macro(self): query = sql( func.distinct(x.continent) for x in db.country if ( func.sum(w.population) for w in db.country if w.continent == x.continent ) > 100000000 ) sql_results = engine.execute(query).fetchall() query_macro_results = query( func.distinct(x.continent) for x in db.country if ( func.sum(w.population) for w in db.country if w.continent == x.continent ) > 100000000 ) assert sql_results == query_macro_results
def test_operators(self): # countries in europe with a DNP per capita larger than the UK compare_queries( """ SELECT name FROM country WHERE gnp/population > ( SELECT gnp/population FROM country WHERE name = 'United Kingdom' ) AND continent = 'Europe' """, sql( x.name for x in db.country if x.gnp / x.population > ( y.gnp / y.population for y in db.country if y.name == 'United Kingdom' ) if (x.continent == 'Europe') ) )
def test_join_complicated(self): compare_queries( """ SELECT t.name, t.population, c.name, c.population FROM country c JOIN city t ON t.country_code = c.code AND t.population * 1.0 / c.population = ( SELECT MAX(tt.population * 1.0 / c.population) FROM city tt WHERE tt.country_code = t.country_code ) """, sql((t.name, t.population, c.name, c.population) for c in db.country for t in db.city if t.country_code == c.code if t.population * 1.0 / c.population == (func.max(tt.population * 1.0 / c.population) for tt in db.city if tt.country_code == t.country_code)), lambda x: sorted(map(str, x)))
def test_aliased(self): # continents whose total population is greater than 100000000 compare_queries( """ SELECT DISTINCT(x.continent) FROM country x WHERE 100000000 < ( SELECT SUM(w.population) from country w WHERE w.continent = x.continent ) """, sql( func.distinct(x.continent) for x in db.country if ( func.sum(w.population) for w in db.country if w.continent == x.continent ).as_scalar() > 100000000 ) )