Python sqlalchemy.dialects.mysql.dialect() Examples

def test_sql_expression_pk_autoinc_lastinserted(self):
        # test that postfetch isn't invoked for a SQL expression
        # in a primary key column.  the DB either needs to support a lastrowid
        # that can return it, or RETURNING.  [ticket:3133]
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", String),
        )

        stmt = table.insert().return_defaults().values(id=func.foobar())
        compiled = stmt.compile(dialect=sqlite.dialect(), column_keys=["data"])
        eq_(compiled.postfetch, [])
        eq_(compiled.returning, [])

        self.assert_compile(
            stmt,
            "INSERT INTO sometable (id, data) VALUES " "(foobar(), ?)",
            checkparams={"data": "foo"},
            params={"data": "foo"},
            dialect=sqlite.dialect(),
        ) 

def test_autocorrelate_error(self):
        users, addresses = self.tables.users, self.tables.addresses

        stmt = (
            users.update()
            .values(name="newname")
            .where(users.c.id == addresses.c.user_id)
            .where(
                ~exists()
                .where(addresses.c.user_id == users.c.id)
                .where(addresses.c.email_address == "foo")
            )
        )

        assert_raises_message(
            exc.InvalidRequestError,
            ".*returned no FROM clauses due to auto-correlation.*",
            stmt.compile,
            dialect=default.StrCompileDialect(),
        ) 

def test_update_from_join_mysql(self):
        users, addresses = self.tables.users, self.tables.addresses

        j = users.join(addresses)
        self.assert_compile(
            update(j)
            .values(name="newname")
            .where(addresses.c.email_address == "e1"),
            ""
            "UPDATE users "
            "INNER JOIN addresses ON users.id = addresses.user_id "
            "SET users.name=%s "
            "WHERE "
            "addresses.email_address = %s",
            checkparams={"email_address_1": "e1", "name": "newname"},
            dialect=mysql.dialect(),
        ) 

def test_prefix_with(self):
        table1 = self.tables.mytable

        stmt = (
            table1.insert()
            .prefix_with("A", "B", dialect="mysql")
            .prefix_with("C", "D")
        )

        self.assert_compile(
            stmt,
            "INSERT C D INTO mytable (myid, name, description) "
            "VALUES (:myid, :name, :description)",
        )

        self.assert_compile(
            stmt,
            "INSERT A B C D INTO mytable (myid, name, description) "
            "VALUES (%s, %s, %s)",
            dialect=mysql.dialect(),
        ) 

def test_prefix_with(self):
        table1 = self.tables.mytable

        stmt = (
            table1.update()
            .prefix_with("A", "B", dialect="mysql")
            .prefix_with("C", "D")
        )

        self.assert_compile(
            stmt,
            "UPDATE C D mytable SET myid=:myid, name=:name, "
            "description=:description",
        )

        self.assert_compile(
            stmt,
            "UPDATE A B C D mytable SET myid=%s, name=%s, description=%s",
            dialect=mysql.dialect(),
        ) 

def test_insert_from_select_returning(self):
        table1 = self.tables.mytable
        sel = select([table1.c.myid, table1.c.name]).where(
            table1.c.name == "foo"
        )
        ins = (
            self.tables.myothertable.insert()
            .from_select(("otherid", "othername"), sel)
            .returning(self.tables.myothertable.c.otherid)
        )
        self.assert_compile(
            ins,
            "INSERT INTO myothertable (otherid, othername) "
            "SELECT mytable.myid, mytable.name FROM mytable "
            "WHERE mytable.name = %(name_1)s RETURNING myothertable.otherid",
            checkparams={"name_1": "foo"},
            dialect="postgresql",
        ) 

def test_insert_from_select_seq(self):
        m = MetaData()

        t1 = Table(
            "t",
            m,
            Column("id", Integer, Sequence("id_seq"), primary_key=True),
            Column("data", String),
        )

        stmt = t1.insert().from_select(("data",), select([t1.c.data]))

        self.assert_compile(
            stmt,
            "INSERT INTO t (data, id) SELECT t.data, "
            "nextval('id_seq') AS next_value_1 FROM t",
            dialect=postgresql.dialect(),
        ) 

def test_insert_from_select_cte_follows_insert_two(self):
        dialect = default.DefaultDialect()
        dialect.cte_follows_insert = True
        table1 = self.tables.mytable

        cte = table1.select().cte("c")
        stmt = cte.select()
        ins = table1.insert().from_select(table1.c, stmt)

        self.assert_compile(
            ins,
            "INSERT INTO mytable (myid, name, description) "
            "WITH c AS (SELECT mytable.myid AS myid, mytable.name AS name, "
            "mytable.description AS description FROM mytable) "
            "SELECT c.myid, c.name, c.description FROM c",
            dialect=dialect,
        ) 

def test_anticipate_no_pk_composite_pk_implicit_returning(self):
        t = Table(
            "t",
            MetaData(),
            Column("x", Integer, primary_key=True),
            Column("y", Integer, primary_key=True),
        )
        d = postgresql.dialect()
        d.implicit_returning = True

        with expect_warnings(
            "Column 't.y' is marked as a member.*"
            "Note that as of SQLAlchemy 1.1,"
        ):
            self.assert_compile(
                t.insert(),
                "INSERT INTO t (x) VALUES (%(x)s)",
                params={"x": 5},
                dialect=d,
            ) 

def test_anticipate_no_pk_non_composite_pk_implicit_returning(self):
        t = Table(
            "t",
            MetaData(),
            Column("x", Integer, primary_key=True, autoincrement=False),
            Column("q", Integer),
        )
        d = postgresql.dialect()
        d.implicit_returning = True
        with expect_warnings(
            "Column 't.x' is marked as a member.*" "may not store NULL.$"
        ):
            self.assert_compile(
                t.insert(),
                "INSERT INTO t (q) VALUES (%(q)s)",
                params={"q": 5},
                dialect=d,
            ) 

def test_no_default_isolation_level(self):
        from sqlalchemy.testing import mock

        engine = engines.testing_engine()

        real_isolation_level = testing.db.dialect.get_isolation_level

        def fake_isolation_level(connection):
            connection = mock.Mock(
                cursor=mock.Mock(
                    return_value=mock.Mock(
                        fetchone=mock.Mock(return_value=None)
                    )
                )
            )
            return real_isolation_level(connection)

        with mock.patch.object(
            engine.dialect, "get_isolation_level", fake_isolation_level
        ):
            with expect_warnings(
                "Could not retrieve transaction isolation level for MySQL "
                "connection."
            ):
                engine.connect() 

def test_anticipate_no_pk_non_composite_pk_prefetch(self):
        t = Table(
            "t",
            MetaData(),
            Column("x", Integer, primary_key=True, autoincrement=False),
            Column("q", Integer),
        )
        d = postgresql.dialect()
        d.implicit_returning = False

        with expect_warnings(
            "Column 't.x' is marked as a member.*" "may not store NULL.$"
        ):
            self.assert_compile(
                t.insert(),
                "INSERT INTO t (q) VALUES (%(q)s)",
                params={"q": 5},
                dialect=d,
            ) 

def test_mysqlconnector_raise_on_warnings_arg(self):
        from sqlalchemy.dialects.mysql import mysqlconnector

        dialect = mysqlconnector.dialect()
        kw = dialect.create_connect_args(
            make_url(
                "mysql+mysqlconnector://u:p@host/db?raise_on_warnings=true"
            )
        )[1]
        eq_(kw["raise_on_warnings"], True)

        kw = dialect.create_connect_args(
            make_url(
                "mysql+mysqlconnector://u:p@host/db?raise_on_warnings=false"
            )
        )[1]
        eq_(kw["raise_on_warnings"], False)

        kw = dialect.create_connect_args(
            make_url("mysql+mysqlconnector://u:p@host/db")
        )[1]
        assert "raise_on_warnings" not in kw 

def _test_ssl_arguments(self, dialect):
        kwarg = dialect.create_connect_args(
            make_url(
                "mysql://scott:tiger@localhost:3306/test"
                "?ssl_ca=/ca.pem&ssl_cert=/cert.pem&ssl_key=/key.pem"
            )
        )[1]
        # args that differ among mysqldb and oursql
        for k in ("use_unicode", "found_rows", "client_flag"):
            kwarg.pop(k, None)
        eq_(
            kwarg,
            {
                "passwd": "tiger",
                "db": "test",
                "ssl": {
                    "ca": "/ca.pem",
                    "cert": "/cert.pem",
                    "key": "/key.pem",
                },
                "host": "localhost",
                "user": "scott",
                "port": 3306,
            },
        ) 

def compile_query(query):
    """ 把一个 sqlalchemy query object 编译成mysql风格的 sql 语句 """
    from sqlalchemy.sql import compiler
    from sqlalchemy.dialects import mysql as mysql_dialetct
    from pymysql.converters import conversions, escape_item, encoders

    dialect = mysql_dialetct.dialect()
    statement = query.statement
    comp = compiler.SQLCompiler(dialect, statement)
    comp.compile()
    enc = dialect.encoding
    comp_params = comp.params
    params = []
    for k in comp.positiontup:
        v = comp_params[k]
        if six.PY2 and isinstance(v, six.string_types) and not isinstance(v, six.text_type):
            v = v.decode("utf8")
        v = escape_item(v, conversions, encoders)
        params.append(v)
    return (comp.string % tuple(params)) 

def test_is_disconnect(
        self, arg0, message, exc_cls_name, dialect_name, is_disconnect
    ):
        class Error(Exception):
            pass

        dbapi = mock.Mock()
        dbapi.Error = Error
        dbapi.ProgrammingError = type("ProgrammingError", (Error,), {})
        dbapi.OperationalError = type("OperationalError", (Error,), {})
        dbapi.InterfaceError = type("InterfaceError", (Error,), {})
        dbapi.InternalError = type("InternalError", (Error,), {})

        dialect = getattr(mysql, dialect_name).dialect(dbapi=dbapi)

        error = getattr(dbapi, exc_cls_name)(arg0, message)
        eq_(dialect.is_disconnect(error, None, None), is_disconnect) 

def test_positional(self):
        table1 = self.tables.mytable

        values = [
            {"myid": 1, "name": "a", "description": "b"},
            {"myid": 2, "name": "c", "description": "d"},
            {"myid": 3, "name": "e", "description": "f"},
        ]

        checkpositional = (1, "a", "b", 2, "c", "d", 3, "e", "f")

        dialect = default.DefaultDialect()
        dialect.supports_multivalues_insert = True
        dialect.paramstyle = "format"
        dialect.positional = True

        self.assert_compile(
            table1.insert().values(values),
            "INSERT INTO mytable (myid, name, description) VALUES "
            "(%s, %s, %s), (%s, %s, %s), (%s, %s, %s)",
            checkpositional=checkpositional,
            dialect=dialect,
        ) 

def test_mysql_variants(self):
        self.assertEqual(
            "LONGTEXT",
            str(
                types.JsonEncodedDict(mysql_as_long=True).compile(
                    dialect=mysql.dialect())
            )
        )

        self.assertEqual(
            "MEDIUMTEXT",
            str(
                types.JsonEncodedDict(mysql_as_medium=True).compile(
                    dialect=mysql.dialect())
            )
        )

        self.assertRaises(
            TypeError,
            lambda: types.JsonEncodedDict(
                mysql_as_long=True,
                mysql_as_medium=True)
        ) 

def test_multi_multi(self):
        table1 = self.tables.mytable

        stmt = table1.insert().values([{"myid": 1, "name": "d1"}])

        stmt = stmt.values(
            [{"myid": 2, "name": "d2"}, {"myid": 3, "name": "d3"}]
        )

        self.assert_compile(
            stmt,
            "INSERT INTO mytable (myid, name) VALUES (%(myid_m0)s, "
            "%(name_m0)s), (%(myid_m1)s, %(name_m1)s), (%(myid_m2)s, "
            "%(name_m2)s)",
            checkparams={
                "myid_m0": 1,
                "name_m0": "d1",
                "myid_m1": 2,
                "name_m1": "d2",
                "myid_m2": 3,
                "name_m2": "d3",
            },
            dialect=postgresql.dialect(),
        ) 

def test_single_bool_ten(self):
        self.assert_compile(
            or_(False),
            "0 = 1",
            dialect=default.DefaultDialect(supports_native_boolean=False),
        ) 

def test_two_a(self):
        c = column("x", Boolean)
        self.assert_compile(
            select([c]).where(c),
            "SELECT x WHERE x = 1",
            dialect=self._dialect(False),
        ) 

def test_seven_a(self):
        t1 = table("t1", column("a"))
        t2 = table("t2", column("b"))
        self.assert_compile(
            join(t1, t2, onclause=true()),
            "t1 JOIN t2 ON 1 = 1",
            dialect=self._dialect(False),
        ) 

def test_four(self):
        c = column("x", Boolean)
        self.assert_compile(
            select([c]).where(~c),
            "SELECT x WHERE NOT x",
            dialect=self._dialect(True),
        ) 

def test_three_b(self):
        c = column("x", Boolean)
        self.assert_compile(
            select([c], whereclause=~c),
            "SELECT x WHERE x = 0",
            dialect=self._dialect(False),
        ) 

def test_sql_expression_pk_autoinc_returning(self):
        # test that return_defaults() works with a primary key where we are
        # sending a SQL expression, and we need to get the server-calculated
        # value back.  [ticket:3133]
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", String),
        )

        stmt = table.insert().return_defaults().values(id=func.foobar())
        returning_dialect = postgresql.dialect()
        returning_dialect.implicit_returning = True
        compiled = stmt.compile(
            dialect=returning_dialect, column_keys=["data"]
        )
        eq_(compiled.postfetch, [])
        eq_(compiled.returning, [table.c.id])

        self.assert_compile(
            stmt,
            "INSERT INTO sometable (id, data) VALUES "
            "(foobar(), %(data)s) RETURNING sometable.id",
            checkparams={"data": "foo"},
            params={"data": "foo"},
            dialect=returning_dialect,
        ) 

def test_three_a(self):
        c = column("x", Boolean)
        self.assert_compile(
            select([c]).where(~c),
            "SELECT x WHERE x = 0",
            dialect=self._dialect(False),
        ) 

def test_sql_expression_pk_noautoinc_returning(self):
        # test that return_defaults() works with a primary key where we are
        # sending a SQL expression, and we need to get the server-calculated
        # value back.  [ticket:3133]
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, autoincrement=False, primary_key=True),
            Column("data", String),
        )

        stmt = table.insert().return_defaults().values(id=func.foobar())
        returning_dialect = postgresql.dialect()
        returning_dialect.implicit_returning = True
        compiled = stmt.compile(
            dialect=returning_dialect, column_keys=["data"]
        )
        eq_(compiled.postfetch, [])
        eq_(compiled.returning, [table.c.id])

        self.assert_compile(
            stmt,
            "INSERT INTO sometable (id, data) VALUES "
            "(foobar(), %(data)s) RETURNING sometable.id",
            checkparams={"data": "foo"},
            params={"data": "foo"},
            dialect=returning_dialect,
        ) 

def test_server_default(self):
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", String),
            Column("foo", Integer, server_default=func.foobar()),
        )

        values = [
            {"id": 1, "data": "data1"},
            {"id": 2, "data": "data2", "foo": "plainfoo"},
            {"id": 3, "data": "data3"},
        ]

        checkparams = {
            "id_m0": 1,
            "id_m1": 2,
            "id_m2": 3,
            "data_m0": "data1",
            "data_m1": "data2",
            "data_m2": "data3",
        }

        self.assert_compile(
            table.insert().values(values),
            "INSERT INTO sometable (id, data) VALUES "
            "(%(id_m0)s, %(data_m0)s), "
            "(%(id_m1)s, %(data_m1)s), "
            "(%(id_m2)s, %(data_m2)s)",
            checkparams=checkparams,
            dialect=postgresql.dialect(),
        ) 

def test_one(self):
        c = column("x", Boolean)
        self.assert_compile(
            select([c]).where(c),
            "SELECT x WHERE x",
            dialect=self._dialect(True),
        ) 

def test_two_b(self):
        c = column("x", Boolean)
        self.assert_compile(
            select([c], whereclause=c),
            "SELECT x WHERE x = 1",
            dialect=self._dialect(False),
        )