Skip to content

Commit eb053b3

Browse files
meierj-wayscriptmeierj-wayscript
authored
Support MySQL & SQL Server (#7)
* Save example * WIP: docker-compose * Install SQL Server & MySQL clients * Add connection kwargs functions for supported DBs * Add basic README on db usage
1 parent 5c2cd50 commit eb053b3

File tree

10 files changed

+271
-78
lines changed

10 files changed

+271
-78
lines changed

‎Pipfile‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,8 @@ name = "pypi"
66
[packages]
77
slack-sdk = "*"
88
psycopg2 = "*"
9+
pymssql = "*"
10+
mysql-connector-python = "*"
911

1012
[dev-packages]
1113
pytest = "*"

‎Pipfile.lock‎

Lines changed: 112 additions & 1 deletion
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎README.md‎

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1 +1,15 @@
1-
# wayscript-python
1+
# wayscript-python
2+
3+
4+
## Integrations
5+
6+
### SQL
7+
8+
To connect to a postgres resource, use the following snippet:
9+
```
10+
import psycopg2
11+
from wayscript.ingegrations import sql
12+
13+
kwargs = sql.get_psycopg2_connection_kwargs(_id)
14+
connection = psycopg2.connect(**kwargs)
15+
```

‎docker-compose.yml‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,9 +9,11 @@ services:
99
- TWINE_USERNAME=__token__
1010
- TWINE_NON_INTERACTIVE=1
1111
- TWINE_PASSWORD
12-
# env_file:
12+
- WAYSCRIPT_ORIGIN
13+
env_file:
1314
# if you want to test publishing, put your test pypi creds in .env.pypi-test
1415
# - ./.env.pypi-test
16+
- ./env.meierj.local
1517
stdin_open: true
1618
tty: true
1719

‎example.py‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,8 @@
1+
from wayscript.integrations.sql import postgres
2+
3+
_id = "c227ed10-1174-4722-a25a-1e5d3f770ad2"
4+
conn = postgres.get_client_for_workspace_integration(_id)
5+
6+
with conn.cursor() as cursor:
7+
cursor.execute("SELECT * FROM public.event;")
8+
print(len([x for x in cursor]))

‎src/wayscript/integrations/sql.py‎

Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,70 @@
1+
import json
2+
3+
from wayscript import utils
4+
from wayscript.errors import MissingCredentialsError
5+
6+
7+
PSYCOPG2_CONNECTION_KWARG_MAP = {
8+
"dbname": "database_name",
9+
"user": "database_user",
10+
"password": "database_password",
11+
"host": "database_host",
12+
"port": "database_port",
13+
}
14+
15+
16+
MSSQL_CONNECTION_KWARG_MAP = {
17+
"server": "database_host",
18+
"user": "database_user",
19+
"password": "database_password",
20+
"database": "database_name",
21+
"port": "database_port",
22+
23+
}
24+
25+
26+
MYSQL_CONNECTION_KWARG_MAP = {
27+
"host": "database_host",
28+
"user": "database_user",
29+
"password": "database_password",
30+
"database": "database_name",
31+
"port": "database_port",
32+
}
33+
34+
35+
def get_connection_kwargs(_id: str, credentials_mapping: dict) -> dict:
36+
"""
37+
Return connection kwargs
38+
39+
If you want to instantiate your own client, use this method.
40+
"""
41+
wayscript_client = utils.WayScriptClient()
42+
response = wayscript_client.get_workspace_integration_detail(_id)
43+
response.raise_for_status()
44+
workspace_integration_data = response.json()
45+
credentials_str = workspace_integration_data.get("credentials")
46+
credentials = {}
47+
48+
try:
49+
credentials = json.loads(credentials_str)
50+
except json.decoder.JSONDecodeError:
51+
credentials = {}
52+
53+
kwargs = {k: credentials.get(v) for k, v in credentials_mapping.items()}
54+
55+
if not credentials:
56+
raise MissingCredentialsError(f"Missing credentials for workspace_integration={_id}")
57+
58+
return kwargs
59+
60+
def get_psycopg2_connection_kwargs(_id: str) -> dict:
61+
"""Get connection kwargs for psycopg2"""
62+
return get_connection_kwargs(_id, credentials_mapping=PSYCOPG2_CONNECTION_KWARG_MAP)
63+
64+
def get_mssql_connection_kwargs(_id: str) -> dict:
65+
"""Get connection kwargs for SQL Server connection via mssql driver"""
66+
return get_connection_kwargs(_id, credentials_mapping=MSSQL_CONNECTION_KWARG_MAP)
67+
68+
def get_mysql_connection_kwargs(_id: str) -> dict:
69+
"""Return connection kwargs for MySQL connection via mysql driver"""
70+
return get_connection_kwargs(_id, credentials_mapping=MYSQL_CONNECTION_KWARG_MAP)

‎src/wayscript/integrations/sql/__init__.py‎

Whitespace-only changes.

‎src/wayscript/integrations/sql/postgres.py‎

Lines changed: 0 additions & 44 deletions
This file was deleted.

‎tests/integrations/sql/test_postgres.py‎

Lines changed: 0 additions & 31 deletions
This file was deleted.

‎tests/integrations/test_sql.py‎

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,61 @@
1+
import pytest
2+
import responses
3+
4+
from wayscript.integrations import sql
5+
6+
7+
PSYCOPG2_EXPECTED = dict(
8+
dbname="my_db",
9+
user="zach",
10+
password="very-secret-password",
11+
host="host.docker.internal",
12+
port=15432,
13+
)
14+
15+
MSSQL_EXPECTED = {
16+
"database": "my_db",
17+
"password": "very-secret-password",
18+
"port": 15432,
19+
"server": "host.docker.internal",
20+
"user": "zach",
21+
}
22+
23+
MYSQL_EXPECTED = {
24+
"database": "my_db",
25+
"host": "host.docker.internal",
26+
"password": "very-secret-password",
27+
"port": 15432,
28+
"user": "zach",
29+
}
30+
31+
32+
@responses.activate
33+
@pytest.mark.parametrize(
34+
"client_name,expected",
35+
[
36+
("psycopg2", PSYCOPG2_EXPECTED),
37+
("mssql", MSSQL_EXPECTED),
38+
("mysql", MYSQL_EXPECTED),
39+
],
40+
)
41+
def test_get_connection_kwargs(
42+
client_name,
43+
expected,
44+
patch_client_get_url,
45+
workspace_integrations_detail_response_sql,
46+
):
47+
"""Test getting postgres client kwargs"""
48+
49+
responses.add(
50+
responses.GET,
51+
patch_client_get_url,
52+
json=workspace_integrations_detail_response_sql,
53+
status=200,
54+
)
55+
56+
_id = workspace_integrations_detail_response_sql["id"]
57+
58+
function_name = f"get_{client_name}_connection_kwargs"
59+
function = getattr(sql, function_name)
60+
61+
assert expected == function(_id)

0 commit comments

Comments
 (0)