def test_insert_single_row(self):
        G(User, id=1)

        query = Query().from_table(
            table=Account,
            fields=[
                'user_id',
                'first_name',
                'last_name'
            ]
        )

        rows = [
            [1, 'Test', 'User']
        ]

        sql, sql_params = query.get_insert_sql(rows)

        self.assertEqual(
            sql,
            (
                'INSERT INTO tests_account (user_id, first_name, last_name) VALUES (%s, %s, %s)'
            )
        )
        self.assertEqual(sql_params[0], 1)
        self.assertEqual(sql_params[1], 'Test')
        self.assertEqual(sql_params[2], 'User')

        query.insert(rows)
        sql = self.logger.get_log()[2]['sql']
        self.assertEqual(sql, "INSERT INTO tests_account (user_id, first_name, last_name) VALUES (1, 'Test', 'User')")

    def test_clear_log(self):
        """
        Makes sure queries are cleared
        """
        logger_one = Logger('one')
        logger_one.start_logging()
        query = Query().from_table(Account)

        # run a query and update the logger's query list
        query.select()
        logger_one.update_log()

        # the index should be at 1
        self.assertEqual(1, logger_one.query_index)

        # increment the connection query count
        query.select()

        # clear the log
        logger_one.clear_log()

        # make sure no queries
        self.assertEqual(0, len(logger_one.queries))

        # query index should match that of the connection log
        self.assertEqual(2, logger_one.query_index)

    def test_join_model_fields_extract(self):
        query = Query().from_table(
            table=Account,
            fields=[
                '*',
            ]
        ).join(
            Order,
            fields=[
                '*'
            ]
        )

        query_str = query.get_sql()
        expected_query = (
            'SELECT tests_account.*, '
            'tests_order.id, '
            'tests_order.account_id, '
            'tests_order.revenue, '
            'tests_order.margin, '
            'tests_order.margin_percent, '
            'tests_order.time '
            'FROM tests_account '
            'JOIN tests_order ON tests_order.account_id = tests_account.id'
        )
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_join_model_fields_prefix(self):
        query = Query().from_table(
            table=Account,
            fields=[
                '*',
            ]
        ).join(
            Order,
            fields=[
                'id',
                'margin',
            ],
            prefix_fields=True
        )

        query_str = query.get_sql()

        expected_query = (
            'SELECT tests_account.*, '
            'tests_order.id AS "order__id", '
            'tests_order.margin AS "order__margin" '
            'FROM tests_account '
            'JOIN tests_order ON tests_order.account_id = tests_account.id'
        )
        self.assertEqual(query_str, expected_query)

 def test_select_fields_two_tables_alias(self):
     query = Query().from_table(
         table={
             'table_one': Account
         },
         fields=[{
             'f1': 'field_one'
         }, {
             'f2': 'field_two'
         }]
     ).from_table(
         table={
             'table_two': 'second_table'
         },
         fields=[{
             'f3': 'field_three'
         }, {
             'f4': 'field_four'
         }]
     )
     query_str = query.get_sql()
     expected_query = (
         'SELECT table_one.field_one AS "f1", '
         'table_one.field_two AS "f2", '
         'table_two.field_three AS "f3", '
         'table_two.field_four AS "f4" '
         'FROM tests_account AS table_one, '
         'second_table AS table_two'
     )
     self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_join_model_fields(self):
        query = Query().from_table(
            table=Account,
            fields=[
                'one',
                'two',
            ]
        ).join(
            Order,
            fields=[{
                'three': 'one'
            }, {
                'four': 'two'
            }]
        )

        query_str = query.get_sql()
        expected_query = (
            'SELECT tests_account.one, '
            'tests_account.two, '
            'tests_order.one AS "three", '
            'tests_order.two AS "four" '
            'FROM tests_account '
            'JOIN tests_order ON tests_order.account_id = tests_account.id'
        )
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_where_complex(self):
        query = Query().from_table(
            table='test_table'
        ).where(Q(
            one=1
        )).where(Q(
            two__gt=2
        )).where(~Q(
            three__gte=3
        )).where(~Q(
            four__lt=4
        ), OR).where(Q(
            five__lte=5
        ), OR).where(Q(
            six__contains='six'
        )).where(~Q(
            seven__startswith='seven'
        )).where(Q(
            eight=8
        ) & Q(
            nine=9
        ) | Q(
            ten=10
        ) | ~Q(
            eleven=11
        ))

        query_str = query.get_sql()
        expected_query = ''.join([
            'SELECT test_table.* FROM test_table WHERE ',
            '(((one = %(A0)s AND two > %(A1)s AND (NOT(three >= %(A2)s))) OR (NOT(four < %(A3)s)) ',
            'OR five <= %(A4)s) AND (six LIKE %(A5)s) AND (NOT(seven LIKE %(A6)s)) AND ',
            '((eight = %(A7)s AND nine = %(A8)s) OR ten = %(A9)s OR (NOT(eleven = %(A10)s))))'
        ])
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

 def test_select_all_from_model(self):
     query = Query().from_table(
         table=Account
     )
     query_str = query.get_sql()
     expected_query = 'SELECT tests_account.* FROM tests_account'
     self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_insert_multiple_rows(self):
        query = Query().from_table(
            table=Account,
            fields=[
                'user_id',
                'first_name',
                'last_name'
            ]
        )

        rows = [
            [1, 'Test', 'User'],
            [2, 'Test2', 'User2'],
        ]

        sql, sql_params = query.get_insert_sql(rows)

        self.assertEqual(
            sql,
            'INSERT INTO tests_account (user_id, first_name, last_name) VALUES (%s, %s, %s), (%s, %s, %s)'
        )
        self.assertEqual(sql_params[0], 1)
        self.assertEqual(sql_params[1], 'Test')
        self.assertEqual(sql_params[2], 'User')
        self.assertEqual(sql_params[3], 2)
        self.assertEqual(sql_params[4], 'Test2')
        self.assertEqual(sql_params[5], 'User2')

        query.insert(rows)
        sql = self.logger.get_log()[0]['sql']
        self.assertEqual(
            sql,
            ("INSERT INTO tests_account (user_id, first_name, last_name) "
             "VALUES (1, 'Test', 'User'), (2, 'Test2', 'User2')")
        )

    def test_get_alias(self):
        """
        Tests the different cases of getting the alias of a field
        """
        field = SimpleField(field="revenue")
        query = Query().from_table(table=Order, fields=[field])
        expected_query = "SELECT tests_order.revenue FROM tests_order"
        self.assertEqual(expected_query, query.get_sql())

        field.auto_alias = "my_auto_alias"
        query = Query().from_table(table=Order, fields=[field])
        expected_query = 'SELECT tests_order.revenue AS "my_auto_alias" FROM tests_order'
        self.assertEqual(expected_query, query.get_sql())

        field.alias = "my_alias"
        query = Query().from_table(table=Order, fields=[field])
        expected_query = 'SELECT tests_order.revenue AS "my_alias" FROM tests_order'
        self.assertEqual(expected_query, query.get_sql())

        query = Query().from_table(table=Order, fields=[field], prefix_fields=True, field_prefix="my_field_prefix")
        expected_query = 'SELECT tests_order.revenue AS "my_field_prefix__my_alias" FROM tests_order'
        self.assertEqual(expected_query, query.get_sql())

        field.alias = None
        field.auto_alias = None
        query = Query().from_table(table=Order, fields=[field], prefix_fields=True, field_prefix="my_field_prefix")
        expected_query = 'SELECT tests_order.revenue AS "my_field_prefix__revenue" FROM tests_order'
        self.assertEqual(expected_query, query.get_sql())

    def test_cast(self):
        query = Query().from_table(
            table=Account,
            fields=[
                CountField(
                    'id',
                    alias='count',
                    cast='float'
                )
            ]
        )

        query_str = query.get_sql()
        expected_query = 'SELECT CAST(COUNT(tests_account.id) AS FLOAT) AS count FROM tests_account'
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

        received = query.select()[0]['count']
        expected = float(len(User.objects.all()))
        self.assertEqual(
            received,
            expected,
            'Expected {0} but received {1}'.format(
                expected,
                received
            )
        )

    def test_inner_alias(self):
        inner_query = Query().from_table(Account)
        query = Query().from_table({"Q0": inner_query})

        query_str = query.get_sql()
        expected_query = "WITH Q0 AS (SELECT tests_account.* FROM tests_account) SELECT Q0.* FROM Q0"
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

 def test_week_grouping(self):
     """
     Verifies that the week grouping query and result is correct
     """
     query = Query().from_table(
         table=Order,
         fields=[
             Week('time', auto=True),
             SumField('margin')
         ]
     )
     expected_query = (
         "SELECT CAST(EXTRACT(year FROM tests_order.time) AS INT) AS time__year, "
         "CAST(EXTRACT(week FROM tests_order.time) AS INT) AS time__week, "
         "CAST(EXTRACT(epoch FROM date_trunc('week', tests_order.time)) AS INT) AS time__epoch, "
         "SUM(tests_order.margin) AS margin_sum "
         "FROM tests_order "
         "GROUP BY time__year, time__week, time__epoch "
         "ORDER BY time__epoch ASC"
     )
     self.assertEqual(expected_query, query.get_sql())
     rows = query.select()
     self.assertEqual(1, len(rows))
     week_dt = datetime.datetime(2012, 10, 19)
     week_unix_time = unix_time(floor(week_dt, week=1))
     self.assertEqual(825.0, rows[0]['margin_sum'])
     self.assertEqual(week_unix_time, rows[0]['time__epoch'])
     self.assertEqual(2012, rows[0]['time__year'])
     self.assertEqual(42, rows[0]['time__week'])

    def test_num_stddev(self):
        query = Query().from_table(
            table=Order,
            fields=[
                '*',
                NumStdDevField(
                    'margin',
                    over=QueryWindow()
                )
            ]
        ).order_by(
            '-margin_num_stddev'
        )

        query_str = query.get_sql()
        expected_query = (
            'SELECT tests_order.*, '
            '(CASE WHEN (STDDEV(tests_order.margin) OVER ()) <> 0 '
            'THEN ((tests_order.margin - ('
            'AVG(tests_order.margin) OVER ())) / (STDDEV(tests_order.margin) OVER ())) '
            'ELSE 0 '
            'END) '
            'AS "margin_num_stddev" '
            'FROM tests_order '
            'ORDER BY margin_num_stddev '
            'DESC'
        )
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

 def test_select_all_from_string(self):
     query = Query().from_table(
         table='test_table'
     )
     query_str = query.get_sql()
     expected_query = 'SELECT test_table.* FROM test_table'
     self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_inner_outer_args_many(self):
        inner_query = Query().from_table(
            Account
        ).where(
            Q(id__gt=1) & Q(id__lt=10)
        )

        inner_query2 = Query().from_table(
            Account
        ).where(
            Q(id__gt=1) & Q(id__lt=10)
        )

        query = Query().from_table(
            inner_query
        ).from_table(
            inner_query2
        ).where(
            ~Q(id=0)
        )

        query_str = query.get_sql()
        expected_query = (
            'WITH T1 AS '
            '(SELECT tests_account.* FROM tests_account WHERE (id > %(T1A0)s AND id < %(T1A1)s)), '
            'T0 AS ('
            'SELECT tests_account.* '
            'FROM tests_account '
            'WHERE (id > %(T0A0)s AND id < %(T0A1)s)) '
            'SELECT T0.*, T1.* '
            'FROM T0, T1 '
            'WHERE ((NOT(id = %(A0)s)))'
        )
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_cannot_mix_distincts(self):
        query = Query().from_table(
            table=Account
        ).distinct_on('field1').distinct()

        with self.assertRaises(ValueError):
            query.get_sql()

 def test_limit(self):
     query = Query().from_table(
         table='test_table'
     ).limit(10)
     query_str = query.get_sql()
     expected_query = 'SELECT test_table.* FROM test_table LIMIT 10'
     self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

 def test_rank(self):
     query = Query().from_table(
         table=Order,
         fields=[
             'id',
             RankField(
                 over=QueryWindow().partition_by(
                     'account_id'
                 ).order_by(
                     'id'
                 )
             )
         ]
     ).order_by(
         '-rank'
     )
     query_str = query.get_sql()
     expected_query = (
         'SELECT tests_order.id, '
         'RANK() OVER (PARTITION BY account_id ORDER BY id ASC) AS "rank" '
         'FROM tests_order '
         'ORDER BY rank '
         'DESC'
     )
     self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_single_model(self):
        query = Query().from_table(Account)
        rows = query.select(True)

        self.assertGreater(len(rows), 0, "No records")

        for row in rows:
            self.assertIsInstance(row, Account, "Row is not model instance")