Example #1
0
    def testcast(self):
        tbl = table('casttest',
                    column('id', Integer),
                    column('v1', Float),
                    column('v2', Float),
                    column('ts', TIMESTAMP),
                    )
        
        def check_results(dialect, expected_results, literal):
            self.assertEqual(len(expected_results), 5, 'Incorrect number of expected results')
            self.assertEqual(str(cast(tbl.c.v1, Numeric).compile(dialect=dialect)), 'CAST(casttest.v1 AS %s)' %expected_results[0])
            self.assertEqual(str(cast(tbl.c.v1, Numeric(12, 9)).compile(dialect=dialect)), 'CAST(casttest.v1 AS %s)' %expected_results[1])
            self.assertEqual(str(cast(tbl.c.ts, Date).compile(dialect=dialect)), 'CAST(casttest.ts AS %s)' %expected_results[2])
            self.assertEqual(str(cast(1234, TEXT).compile(dialect=dialect)), 'CAST(%s AS %s)' %(literal, expected_results[3]))
            self.assertEqual(str(cast('test', String(20)).compile(dialect=dialect)), 'CAST(%s AS %s)' %(literal, expected_results[4]))
            sel = select([tbl, cast(tbl.c.v1, Numeric)]).compile(dialect=dialect) 
            self.assertEqual(str(sel), "SELECT casttest.id, casttest.v1, casttest.v2, casttest.ts, CAST(casttest.v1 AS NUMERIC(10, 2)) \nFROM casttest")            
        # first test with Postgres engine
        check_results(postgres.dialect(), ['NUMERIC(10, 2)', 'NUMERIC(12, 9)', 'DATE', 'TEXT', 'VARCHAR(20)'], '%(literal)s')

        # then the Oracle engine
        check_results(oracle.dialect(), ['NUMERIC(10, 2)', 'NUMERIC(12, 9)', 'DATE', 'CLOB', 'VARCHAR(20)'], ':literal')

        # then the sqlite engine
        check_results(sqlite.dialect(), ['NUMERIC(10, 2)', 'NUMERIC(12, 9)', 'DATE', 'TEXT', 'VARCHAR(20)'], '?')

        # MySQL seems to only support DATE types for cast
        self.assertEqual(str(cast(tbl.c.ts, Date).compile(dialect=mysql.dialect())), 'CAST(casttest.ts AS DATE)')
        self.assertEqual(str(cast(tbl.c.ts, Numeric).compile(dialect=mysql.dialect())), 'casttest.ts')
Example #2
0
    def testtextbinds(self):
        self.runtest(
            text("select * from foo where lala=:bar and hoho=:whee"), 
                "select * from foo where lala=:bar and hoho=:whee", 
                checkparams={'bar':4, 'whee': 7},
                params={'bar':4, 'whee': 7, 'hoho':10},
        )
        
        dialect = postgres.dialect()
        self.runtest(
            text("select * from foo where lala=:bar and hoho=:whee"), 
                "select * from foo where lala=%(bar)s and hoho=%(whee)s", 
                checkparams={'bar':4, 'whee': 7},
                params={'bar':4, 'whee': 7, 'hoho':10},
                dialect=dialect
        )

        dialect = sqlite.dialect()
        self.runtest(
            text("select * from foo where lala=:bar and hoho=:whee"), 
                "select * from foo where lala=? and hoho=?", 
                checkparams=[4, 7],
                params={'bar':4, 'whee': 7, 'hoho':10},
                dialect=dialect
        )
Example #3
0
    def test_insert_returning(self):
        dialect = postgres.dialect()
        table1 = table(
            'mytable',
            column('myid', Integer),
            column('name', String(128)),
            column('description', String(128)),
        )

        i = insert(table1,
                   values=dict(name='foo'),
                   postgres_returning=[table1.c.myid, table1.c.name])
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) VALUES (%(name)s) RETURNING mytable.myid, mytable.name",
            dialect=dialect)

        i = insert(table1,
                   values=dict(name='foo'),
                   postgres_returning=[table1])
        self.assert_compile(i, "INSERT INTO mytable (name) VALUES (%(name)s) "\
            "RETURNING mytable.myid, mytable.name, mytable.description", dialect=dialect)

        i = insert(table1,
                   values=dict(name='foo'),
                   postgres_returning=[func.length(table1.c.name)])
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) VALUES (%(name)s) RETURNING length(mytable.name)",
            dialect=dialect)
Example #4
0
    def test_update_returning(self):
        dialect = postgres.dialect()
        table1 = table(
            'mytable',
            column('myid', Integer),
            column('name', String(128)),
            column('description', String(128)),
        )

        u = update(table1,
                   values=dict(name='foo'),
                   postgres_returning=[table1.c.myid, table1.c.name])
        self.assert_compile(
            u,
            "UPDATE mytable SET name=%(name)s RETURNING mytable.myid, mytable.name",
            dialect=dialect)

        u = update(table1,
                   values=dict(name='foo'),
                   postgres_returning=[table1])
        self.assert_compile(u, "UPDATE mytable SET name=%(name)s "\
            "RETURNING mytable.myid, mytable.name, mytable.description", dialect=dialect)

        u = update(table1,
                   values=dict(name='foo'),
                   postgres_returning=[func.length(table1.c.name)])
        self.assert_compile(
            u,
            "UPDATE mytable SET name=%(name)s RETURNING length(mytable.name)",
            dialect=dialect)
Example #5
0
    def test_insert_returning(self):
        dialect = postgres.dialect()
        table1 = table('mytable',
            column('myid', Integer),
            column('name', String(128)),
            column('description', String(128)),
        )

        i = insert(table1, values=dict(name='foo'), postgres_returning=[table1.c.myid, table1.c.name])
        self.assert_compile(i, "INSERT INTO mytable (name) VALUES (%(name)s) RETURNING mytable.myid, mytable.name", dialect=dialect)

        i = insert(table1, values=dict(name='foo'), postgres_returning=[table1])
        self.assert_compile(i, "INSERT INTO mytable (name) VALUES (%(name)s) "\
            "RETURNING mytable.myid, mytable.name, mytable.description", dialect=dialect)

        i = insert(table1, values=dict(name='foo'), postgres_returning=[func.length(table1.c.name)])
        self.assert_compile(i, "INSERT INTO mytable (name) VALUES (%(name)s) RETURNING length(mytable.name)", dialect=dialect)
Example #6
0
    def test_update_returning(self):
        dialect = postgres.dialect()
        table1 = table('mytable',
            column('myid', Integer),
            column('name', String(128)),
            column('description', String(128)),
        )

        u = update(table1, values=dict(name='foo'), postgres_returning=[table1.c.myid, table1.c.name])
        self.assert_compile(u, "UPDATE mytable SET name=%(name)s RETURNING mytable.myid, mytable.name", dialect=dialect)

        u = update(table1, values=dict(name='foo'), postgres_returning=[table1])
        self.assert_compile(u, "UPDATE mytable SET name=%(name)s "\
            "RETURNING mytable.myid, mytable.name, mytable.description", dialect=dialect)

        u = update(table1, values=dict(name='foo'), postgres_returning=[func.length(table1.c.name)])
        self.assert_compile(u, "UPDATE mytable SET name=%(name)s RETURNING length(mytable.name)", dialect=dialect)
Example #7
0
    def testouterjoin(self):
        # test an outer join.  the oracle module should take the ON clause of the join and
        # move it up to the WHERE clause of its parent select, and append (+) to all right-hand-side columns
        # within the original onclause, but leave right-hand-side columns unchanged outside of the onclause
        # parameters.
        
        query = select(
                [table1, table2],
                and_(
                    table1.c.name == 'fred',
                    table1.c.myid == 10,
                    table2.c.othername != 'jack',
                    "EXISTS (select yay from foo where boo = lar)"
                ),
                from_obj = [ outerjoin(table1, table2, table1.c.myid == table2.c.otherid) ]
                )
                
        self.runtest(query, 
            "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername \
FROM mytable LEFT OUTER JOIN myothertable ON mytable.myid = myothertable.otherid \
WHERE mytable.name = %(mytable_name)s AND mytable.myid = %(mytable_myid)s AND \
myothertable.othername != %(myothertable_othername)s AND \
EXISTS (select yay from foo where boo = lar)",
            dialect=postgres.dialect()
            )

        self.runtest(query, 
            "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername \
FROM mytable, myothertable WHERE mytable.myid = myothertable.otherid(+) AND \
mytable.name = :mytable_name AND mytable.myid = :mytable_myid AND \
myothertable.othername != :myothertable_othername AND EXISTS (select yay from foo where boo = lar)",
            dialect=oracle.OracleDialect(use_ansi = False))

        query = table1.outerjoin(table2, table1.c.myid==table2.c.otherid).outerjoin(table3, table3.c.userid==table2.c.otherid)
        self.runtest(query.select(), "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable LEFT OUTER JOIN myothertable ON mytable.myid = myothertable.otherid LEFT OUTER JOIN thirdtable ON thirdtable.userid = myothertable.otherid")
        self.runtest(query.select(), "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable, myothertable, thirdtable WHERE mytable.myid = myothertable.otherid(+) AND thirdtable.userid(+) = myothertable.otherid", dialect=oracle.dialect(use_ansi=False))    
Example #8
0
class CompileTest(TestBase, AssertsCompiledSQL):
    __dialect__ = postgres.dialect()

    def test_update_returning(self):
        dialect = postgres.dialect()
        table1 = table(
            'mytable',
            column('myid', Integer),
            column('name', String(128)),
            column('description', String(128)),
        )

        u = update(table1,
                   values=dict(name='foo'),
                   postgres_returning=[table1.c.myid, table1.c.name])
        self.assert_compile(
            u,
            "UPDATE mytable SET name=%(name)s RETURNING mytable.myid, mytable.name",
            dialect=dialect)

        u = update(table1,
                   values=dict(name='foo'),
                   postgres_returning=[table1])
        self.assert_compile(u, "UPDATE mytable SET name=%(name)s "\
            "RETURNING mytable.myid, mytable.name, mytable.description", dialect=dialect)

        u = update(table1,
                   values=dict(name='foo'),
                   postgres_returning=[func.length(table1.c.name)])
        self.assert_compile(
            u,
            "UPDATE mytable SET name=%(name)s RETURNING length(mytable.name)",
            dialect=dialect)

    def test_insert_returning(self):
        dialect = postgres.dialect()
        table1 = table(
            'mytable',
            column('myid', Integer),
            column('name', String(128)),
            column('description', String(128)),
        )

        i = insert(table1,
                   values=dict(name='foo'),
                   postgres_returning=[table1.c.myid, table1.c.name])
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) VALUES (%(name)s) RETURNING mytable.myid, mytable.name",
            dialect=dialect)

        i = insert(table1,
                   values=dict(name='foo'),
                   postgres_returning=[table1])
        self.assert_compile(i, "INSERT INTO mytable (name) VALUES (%(name)s) "\
            "RETURNING mytable.myid, mytable.name, mytable.description", dialect=dialect)

        i = insert(table1,
                   values=dict(name='foo'),
                   postgres_returning=[func.length(table1.c.name)])
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) VALUES (%(name)s) RETURNING length(mytable.name)",
            dialect=dialect)

    def test_extract(self):
        t = table('t', column('col1'))

        for field in 'year', 'month', 'day':
            self.assert_compile(
                select([extract(field, t.c.col1)]),
                "SELECT EXTRACT(%s FROM t.col1::timestamp) AS anon_1 "
                "FROM t" % field)