sql-connectors 1.0.2

功能

• 用于处理不同SQL数据源的标准化客户端，包括用于定义连接配置的标准化格式
• 一个基于sqlalchemyEngine的sqlclient接口，带有一些有用的函数，如pandasread_sql和利用sqlalchemy中的reflection的函数

安装

$ pip install --process-dependency-links sql_connectors

$ git clone git://github.com/aiguofer/sql_connectors
$cd sql_connectors
$ pip install --process-dependency-links -e .[dev]

配置

通过实现自己的Storage，可以将配置存储在任何需要的地方。但是，默认值是LocalStorage从~/.config/sql_connectors读取配置文件。

可以使用SQL_CONNECTORS_STORAGE环境变量（例如sql_connectors.storage.LocalStorage）更改Storage类，还可以使用SQL_CONNECTORS_PATH_OR_URI指定不同的配置目录或uri。

example_connection.json文件是作为模板提供的；可以用自己的连接详细信息替换它并重新命名该文件。

示例文件的内容是：

{"drivername":"sqlite","relative_paths":["database"],"default_env":"default","default":{"database":"example_connection.db"}}

这些字段的含义如下：

drivername (string)

This required field is a SQLAlchemy dialect or dialect+driver. See the SQLAlchemy Engine documentation for more details. You may first have to install the required python modules for your dialect+driver to work if it’s a third party plug-in.

relative_paths (list of strings)

This optional field lets you specify if an option for your connection needs to load a file relative to your config directory. For example, if you had a connection that needed to use a cert, you could add ^{tt12}$ to this list, set ^{tt13}$, and drop the cert in ^{tt14}$.

default_env (string)

This optional field lets you specify which environment should be used by default. If not included, it will use ^{tt15}$.

default_schema (string)

This optional field lets you specify which schema should be used by default. If not included, it will use ^{tt16}$.

default_reflect (boolean)

This optional field lets you specify whether it should reflect the data source by default. If not included, it will use ^{tt17}$.

env.username (string)

This optional field specifies the username for the connection. If it’s left out or set to null and the driver is not ‘sqlite’, the user will be prompte when they try to create the client. If the connection doesn’t have credentials, set this to an empty string. Should not be set for ‘sqlite’.

env.password (string)

This optional field specifies the password for the connection. If it’s left out or set to null and the driver is not ‘sqlite’, the user will be prompte when they try to create the client. If the connection doesn’t have credentials, set this to an empty string. Should not be set for ‘sqlite’.

env.host (string)

This optional field specifies the host for the connection. Should not be set for ‘sqlite’.

env.port (string or integer)

This optional field specifies the port for the connection. Should not be set for ‘sqlite’.

env.database (string)

This optional field specifies the database name for the connection. If it’s a ‘sqlite’ connection and left empty, it will use ^{tt18}$. Otherwise, you can specify a relative path or an absolute path; if you want the file in your config directory, you can use the ^{tt19}$ property.

env.query (object)

This optional field is a json object with options to pass onto the dialect and/or DBAPI upon connect.

env.allowed_hosts (list of strings)

This optional field is a list of strings containing hostnames where the given credentials are accepted. If the hostname is not in the list, it will prompt the user for credentials. This was added due to some specific usecase where we share service credentials but they’re only allowed on our common servers.

如何

模块将检查可用的连接配置，并在顶层模块中为每个配置创建变量。它将为每个配置创建2个变量，connection_name和connection_name_envs；这两个都是函数，第一个函数将返回一个get_client函数，并根据配置设置一些默认值，第二个函数将返回一个get_available_envs函数，当调用该函数时，该函数将返回给定数据源的可用环境。启用reflection时，客户端将保存有关可用表的元数据。

这是一个基本的用法示例，假设示例配置文件存在：

fromsql_connectorsimportconnectionsclient=connections.example_connection()client.read_sql('select 1')

下面是一个更复杂的示例，它相当多余，但显示了更多功能

fromsql_connectorsimportconnectionsavailable_envs=connections.example_connection_envs()client=connections.example_connection(env=available_envs[0],reflect=True)client.read_sql('select 1').to_sql('example_table',client,if_exists='replace')available_tables=client.table_names()table1=client.get_table(available_tables[0])df=client.read_sql(table1.select())

学分

这个包是用Cookiecutter和audreyr/cookiecutter-pypackage项目模板创建的。