Python源码示例：sqlalchemy.dialects.postgresql.dialect()
示例1
def create(self, bind=None, checkfirst=True):
"""Emit ``CREATE TYPE`` for this
:class:`~.postgresql.ENUM`.
If the underlying dialect does not support
Postgresql CREATE TYPE, no action is taken.
:param bind: a connectable :class:`.Engine`,
:class:`.Connection`, or similar object to emit
SQL.
:param checkfirst: if ``True``, a query against
the PG catalog will be first performed to see
if the type does not exist already before
creating.
"""
if not bind.dialect.supports_native_enum:
return
if not checkfirst or \
not bind.dialect.has_type(
bind, self.name, schema=self.schema):
bind.execute(CreateEnumType(self))
示例2
def create(self, bind=None, checkfirst=True):
"""Emit ``CREATE TYPE`` for this
:class:`~.postgresql.ENUM`.
If the underlying dialect does not support
Postgresql CREATE TYPE, no action is taken.
:param bind: a connectable :class:`.Engine`,
:class:`.Connection`, or similar object to emit
SQL.
:param checkfirst: if ``True``, a query against
the PG catalog will be first performed to see
if the type does not exist already before
creating.
"""
if not bind.dialect.supports_native_enum:
return
if not checkfirst or \
not bind.dialect.has_type(
bind, self.name, schema=self.schema):
bind.execute(CreateEnumType(self))
示例3
def get_strict_matching_stickers(session, context):
"""Query all strictly matching stickers for given tags."""
matching_stickers = get_strict_matching_query(session, context)
limit = context.limit if context.limit else 50
matching_stickers = matching_stickers.offset(context.offset).limit(limit)
# if config['logging']['debug']:
# print(matching_stickers.statement.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True}))
# print(matching_stickers.statement.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True}).params)
matching_stickers = matching_stickers.all()
if config["logging"]["debug"]:
pprint("Strict results:")
pprint(matching_stickers)
return matching_stickers
示例4
def get_fuzzy_matching_stickers(session, context):
"""Get fuzzy matching stickers."""
limit = context.limit if context.limit else 50
matching_stickers = (
get_fuzzy_matching_query(session, context)
.offset(context.fuzzy_offset)
.limit(limit)
)
# if config['logging']['debug']:
# print(matching_stickers.statement.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True}))
# print(matching_stickers.statement.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True}).params)
matching_stickers = matching_stickers.all()
if config["logging"]["debug"]:
pprint("Fuzzy results:")
pprint(matching_stickers)
return matching_stickers
示例5
def _already_in_filter(self, names):
''' Checks if the parameter name already added into the filter '''
infilter = None
if names:
if not isinstance(self.query, type(None)):
if not isinstance(self.query.whereclause, type(None)):
wc = str(self.query.whereclause.compile(dialect=postgresql.dialect(),
compile_kwargs={'literal_binds': True}))
infilter = any([name in wc for name in names])
return infilter
#
# Methods specific to functional queries
#
示例6
def create(self, bind=None, checkfirst=True):
"""Emit ``CREATE TYPE`` for this
:class:`~.postgresql.ENUM`.
If the underlying dialect does not support
Postgresql CREATE TYPE, no action is taken.
:param bind: a connectable :class:`.Engine`,
:class:`.Connection`, or similar object to emit
SQL.
:param checkfirst: if ``True``, a query against
the PG catalog will be first performed to see
if the type does not exist already before
creating.
"""
if not bind.dialect.supports_native_enum:
return
if not checkfirst or \
not bind.dialect.has_type(
bind, self.name, schema=self.schema):
bind.execute(CreateEnumType(self))
示例7
def get_sql_string(sqlalchemy_query):
"""
Return SQL string compiled from the given sqlalchemy query (using the PostgreSQL dialect).
Parameters
----------
sqlalchemy_query : sqlalchemy.sql.Selectable
SQLAlchemy query
Returns
-------
str
SQL string compiled from the sqlalchemy query.
"""
assert isinstance(sqlalchemy_query, Selectable)
compiled_query = sqlalchemy_query.compile(
dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True}
)
sql = str(compiled_query)
return sql
示例8
def test_standalone_enum(self):
metadata = MetaData(testing.db)
etype = Enum(
"four", "five", "six", name="fourfivesixtype", metadata=metadata
)
etype.create()
try:
assert testing.db.dialect.has_type(testing.db, "fourfivesixtype")
finally:
etype.drop()
assert not testing.db.dialect.has_type(
testing.db, "fourfivesixtype"
)
metadata.create_all()
try:
assert testing.db.dialect.has_type(testing.db, "fourfivesixtype")
finally:
metadata.drop_all()
assert not testing.db.dialect.has_type(
testing.db, "fourfivesixtype"
)
示例9
def test_custom_subclass(self, connection):
class MyEnum(TypeDecorator):
impl = Enum("oneHI", "twoHI", "threeHI", name="myenum")
def process_bind_param(self, value, dialect):
if value is not None:
value += "HI"
return value
def process_result_value(self, value, dialect):
if value is not None:
value += "THERE"
return value
t1 = Table("table1", self.metadata, Column("data", MyEnum()))
self.metadata.create_all(testing.db)
connection.execute(t1.insert(), {"data": "two"})
eq_(connection.scalar(select([t1.c.data])), "twoHITHERE")
示例10
def test_numeric_codes(self):
from sqlalchemy.dialects.postgresql import (
pg8000,
pygresql,
psycopg2,
psycopg2cffi,
base,
)
dialects = (
pg8000.dialect(),
pygresql.dialect(),
psycopg2.dialect(),
psycopg2cffi.dialect(),
)
for dialect in dialects:
typ = Numeric().dialect_impl(dialect)
for code in (
base._INT_TYPES + base._FLOAT_TYPES + base._DECIMAL_TYPES
):
proc = typ.result_processor(dialect, code)
val = 23.7
if proc is not None:
val = proc(val)
assert val in (23.7, decimal.Decimal("23.7"))
示例11
def test_format(self):
seq = Sequence("my_seq_no_schema")
dialect = postgresql.dialect()
assert (
dialect.identifier_preparer.format_sequence(seq)
== "my_seq_no_schema"
)
seq = Sequence("my_seq", schema="some_schema")
assert (
dialect.identifier_preparer.format_sequence(seq)
== "some_schema.my_seq"
)
seq = Sequence("My_Seq", schema="Some_Schema")
assert (
dialect.identifier_preparer.format_sequence(seq)
== '"Some_Schema"."My_Seq"'
)
示例12
def test_create_index_with_using(self):
m = MetaData()
tbl = Table("testtbl", m, Column("data", String))
idx1 = Index("test_idx1", tbl.c.data)
idx2 = Index("test_idx2", tbl.c.data, postgresql_using="btree")
idx3 = Index("test_idx3", tbl.c.data, postgresql_using="hash")
self.assert_compile(
schema.CreateIndex(idx1),
"CREATE INDEX test_idx1 ON testtbl " "(data)",
dialect=postgresql.dialect(),
)
self.assert_compile(
schema.CreateIndex(idx2),
"CREATE INDEX test_idx2 ON testtbl " "USING btree (data)",
dialect=postgresql.dialect(),
)
self.assert_compile(
schema.CreateIndex(idx3),
"CREATE INDEX test_idx3 ON testtbl " "USING hash (data)",
dialect=postgresql.dialect(),
)
示例13
def bind_processor(self, dialect):
if self.as_uuid:
def process(value):
if value is not None:
value = util.text_type(value)
return value
return process
else:
return None
示例14
def result_processor(self, dialect, coltype):
if self.as_uuid:
def process(value):
if value is not None:
value = _python_UUID(value)
return value
return process
else:
return None
示例15
def bind_processor(self, dialect):
item_proc = self.item_type.\
dialect_impl(dialect).\
bind_processor(dialect)
def process(value):
if value is None:
return value
else:
return self._proc_array(
value,
item_proc,
self.dimensions,
list)
return process
示例16
def result_processor(self, dialect, coltype):
item_proc = self.item_type.\
dialect_impl(dialect).\
result_processor(dialect, coltype)
def process(value):
if value is None:
return value
else:
return self._proc_array(
value,
item_proc,
self.dimensions,
tuple if self.as_tuple else list)
return process
示例17
def render_literal_value(self, value, type_):
value = super(PGCompiler, self).render_literal_value(value, type_)
if self.dialect._backslash_escapes:
value = value.replace('\\', '\\\\')
return value
示例18
def get_column_specification(self, column, **kwargs):
colspec = self.preparer.format_column(column)
impl_type = column.type.dialect_impl(self.dialect)
if isinstance(impl_type, sqltypes.TypeDecorator):
impl_type = impl_type.impl
if column.primary_key and \
column is column.table._autoincrement_column and \
(
self.dialect.supports_smallserial or
not isinstance(impl_type, sqltypes.SmallInteger)
) and (
column.default is None or
(
isinstance(column.default, schema.Sequence) and
column.default.optional
)):
if isinstance(impl_type, sqltypes.BigInteger):
colspec += " BIGSERIAL"
elif isinstance(impl_type, sqltypes.SmallInteger):
colspec += " SMALLSERIAL"
else:
colspec += " SERIAL"
else:
colspec += " " + self.dialect.type_compiler.process(column.type,
type_expression=column)
default = self.get_column_default_string(column)
if default is not None:
colspec += " DEFAULT " + default
if not column.nullable:
colspec += " NOT NULL"
return colspec
示例19
def visit_enum(self, type_, **kw):
if not type_.native_enum or not self.dialect.supports_native_enum:
return super(PGTypeCompiler, self).visit_enum(type_, **kw)
else:
return self.visit_ENUM(type_, **kw)
示例20
def visit_ENUM(self, type_, **kw):
return self.dialect.identifier_preparer.format_type(type_)
示例21
def get_table_oid(self, table_name, schema=None):
"""Return the OID for the given table name."""
return self.dialect.get_table_oid(self.bind, table_name, schema,
info_cache=self.info_cache)
示例22
def get_foreign_table_names(self, schema=None):
"""Return a list of FOREIGN TABLE names.
Behavior is similar to that of :meth:`.Inspector.get_table_names`,
except that the list is limited to those tables tha report a
``relkind`` value of ``f``.
.. versionadded:: 1.0.0
"""
schema = schema or self.default_schema_name
return self.dialect._get_foreign_table_names(self.bind, schema)
示例23
def fire_sequence(self, seq, type_):
return self._execute_scalar((
"select nextval('%s')" %
self.dialect.identifier_preparer.format_sequence(seq)), type_)
示例24
def explain(self, req, **_):
req_comp = req.compile(dialect=postgresql.dialect())
arg_dic = {}
for k in req_comp.params:
arg_dic[k] = repr(req_comp.params[k])
req_cur = self.db.execute(text("EXPLAIN " + req_comp.string % arg_dic))
return "\n".join(map(" ".join, req_cur.fetchall()))
示例25
def _insert_query_params(statement_str, parameters, dialect):
""" Compile a statement by inserting *unquoted* parameters into the query """
return statement_str % parameters
示例26
def stmt2sql(stmt):
""" Convert an SqlAlchemy statement into a string """
# See: http://stackoverflow.com/a/4617623/134904
# This intentionally does not escape values!
dialect = pg.dialect()
query = stmt.compile(dialect=dialect)
return _insert_query_params(query.string, query.params, pg.dialect())
示例27
def sa_type_to_postgres_str(sa_type):
"""Map a Postgres-compatible sqlalchemy type to a Postgres-appropriate
string"""
if callable(sa_type):
sa_type = sa_type()
return sa_type.compile(dialect=sa_postgres_dialect())
示例28
def copy_to(source, dest, engine_or_conn, **flags):
"""Export a query or select to a file. For flags, see the PostgreSQL
documentation at http://www.postgresql.org/docs/9.5/static/sql-copy.html.
Examples: ::
select = MyTable.select()
with open('/path/to/file.tsv', 'w') as fp:
copy_to(select, fp, conn)
query = session.query(MyModel)
with open('/path/to/file/csv', 'w') as fp:
copy_to(query, fp, engine, format='csv', null='.')
:param source: SQLAlchemy query or select
:param dest: Destination file pointer, in write mode
:param engine_or_conn: SQLAlchemy engine, connection, or raw_connection
:param **flags: Options passed through to COPY
If an existing connection is passed to `engine_or_conn`, it is the caller's
responsibility to commit and close.
"""
dialect = postgresql.dialect()
statement = getattr(source, 'statement', source)
compiled = statement.compile(dialect=dialect)
conn, autoclose = raw_connection_from(engine_or_conn)
cursor = conn.cursor()
query = cursor.mogrify(compiled.string, compiled.params).decode()
formatted_flags = '({})'.format(format_flags(flags)) if flags else ''
copy = 'COPY ({}) TO STDOUT {}'.format(query, formatted_flags)
cursor.copy_expert(copy, dest)
if autoclose:
conn.close()
示例29
def bind_processor(self, dialect):
if self.as_uuid:
def process(value):
if value is not None:
value = util.text_type(value)
return value
return process
else:
return None
示例30
def result_processor(self, dialect, coltype):
if self.as_uuid:
def process(value):
if value is not None:
value = _python_UUID(value)
return value
return process
else:
return None
