Repository: ploomber/jupysql
Pull request #111 (2023-02-02 16:48:54)

Add: CI Integration database tests
closes https://github.com/ploomber/jupysql/issues/92
## Changes

- Add: Workflow [ci-integration-db.yaml](https://github.com/ploomber/jupysql/pull/111/files#diff-a1dd9315a458573f17e15acc3f1c763961960d0999962e64831d8560d1a7b100) to initialize databases
- Add `src/tests/integration` to run database integration testing 

## Things to do in next phase

- Use fixtures as a parameter to test under different database environments

<!-- readthedocs-preview jupysql start -->
----
:books: Documentation preview :books:: https://jupysql--111.org.readthedocs.build/en/111/

<!-- readthedocs-preview jupysql end -->


Patch:
diff --git a/.github/pull_request_template.md b/.github/pull_request_template.md
new file mode 100644
index 0000000..31cf702
--- /dev/null
+++ b/.github/pull_request_template.md
@@ -0,0 +1,11 @@
+## Describe your changes
+
+## Issue ticket number and link
+Closes #x
+
+## Checklist before requesting a review
+
+- [ ] I have performed a self-review of my code
+- [ ] I have added thorough [tests](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#testing) (when necessary).
+- [ ] I have added the right documentation in the [docstring](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#documenting-changes-and-new-features) and [changelog](https://github.com/ploomber/contributing/blob/main/CONTRIBUTING.md#changelog) (when needed)
+
diff --git a/.github/workflows/ci-integration-db.yaml b/.github/workflows/ci-integration-db.yaml
new file mode 100644
index 0000000..ec34e45
--- /dev/null
+++ b/.github/workflows/ci-integration-db.yaml
@@ -0,0 +1,55 @@
+name: CI - DB Integration
+
+on: [push, pull_request]
+
+jobs:
+  database-integration-test:
+    strategy:
+      matrix:
+        python-version: ['3.11']
+        os: [ubuntu-latest]
+    runs-on: ${{ matrix.os }}
+    services:
+    # postgreSQL Service
+      postgres:
+        image: postgres
+        env:
+          POSTGRES_DB: db
+          POSTGRES_USER: ploomber_app
+          POSTGRES_PASSWORD: ploomber_app_password
+        options: >-
+          --health-cmd pg_isready
+          --health-interval 10s
+          --health-timeout 5s
+          --health-retries 5
+        ports:
+          - 5432:5432
+      # mySQL Service
+      mysql:
+        image: mysql
+        env:
+          MYSQL_DATABASE: db
+          MYSQL_USER: ploomber_app
+          MYSQL_PASSWORD: ploomber_app_password
+          MYSQL_ROOT_PASSWORD: ploomber_app_root_password
+        ports:
+          - 33306:3306
+        options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=3
+    steps:
+      - uses: actions/checkout@v2
+
+      - name: Set up Python ${{ matrix.python-version }}
+        uses: actions/setup-python@v2
+        with:
+          python-version: ${{ matrix.python-version }}
+
+      - name: Install dependencies
+        run: |
+
+          # Install required package for integration testing
+          pip install pandas pyarrow psycopg2-binary pymysql
+          pip install ".[dev]"
+      - name: Integration Test
+        run:
+          # Run the integration test by pytest marker
+          pytest src/tests/integration
\ No newline at end of file
diff --git a/.github/workflows/ci.yaml b/.github/workflows/ci.yaml
index 9cb80c5..5adcadb 100644
--- a/.github/workflows/ci.yaml
+++ b/.github/workflows/ci.yaml
@@ -36,7 +36,7 @@ jobs:
 
       - name: Test with pytest
         run: |
-          pytest --durations-min=5
+          pytest --durations-min=5 --ignore=src/tests/integration
 
 
   # run: pkgmt check
diff --git a/CHANGELOG.md b/CHANGELOG.md
index e44fe7d..600b474 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,7 +1,10 @@
 # CHANGELOG
 
-## 0.5.4dev
+## 0.5.5dev
 
+## 0.5.4 (2023-02-06)
+
+* [Feature] Adds `%jupysql`/`%%jupysql` as alias for `%sql`/`%%sql`
 * [Fix] Adds community link to `ValueError` and `TypeError`
 
 ## 0.5.3 (2023-01-31)
diff --git a/doc/connecting.md b/doc/connecting.md
index 6f78128..8bfad43 100644
--- a/doc/connecting.md
+++ b/doc/connecting.md
@@ -57,7 +57,7 @@ a flag with (-a|--connection_arguments)the connection string as a JSON string. S
 
 Check out our guide for connecting to a database:
 
-- [PosgreSQL](howto/postgres-connect)
+- [PostgreSQL](howto/postgres-connect)
 
 +++
 
diff --git a/doc/howto.md b/doc/howto.md
index a09e28a..4df9840 100644
--- a/doc/howto.md
+++ b/doc/howto.md
@@ -11,6 +11,19 @@ kernelspec:
   name: python3
 ---
 
+```{code-cell} ipython3
+:tags: [remove-cell]
+
+# clean up all .db files (this cell will not be displayed in the docs)
+from pathlib import Path
+from glob import glob
+
+for file in (Path(f) for f in glob("*.db")):
+    if file.exists():
+        print(f"Deleting: {file}")
+        file.unlink()
+```
+
 # How-To
 
 ## Query CSV files with SQL
@@ -230,3 +243,36 @@ Close by passing the alias:
 ```{code-cell} ipython3
 %sql -l
 ```
+
+## Connect to existing `engine`
+
+Pass the name of the engine:
+
+```{code-cell} ipython3
+some_engine = create_engine("sqlite:///some.db")
+```
+
+```{code-cell} ipython3
+%sql some_engine
+```
+
++++ {"tags": []}
+
+## Use `%sql`/`%%sql` in Databricks
+
+Databricks uses the same name (`%sql`/`%%sql`) for its SQL magics; however, JupySQL exposes a `%jupysql`/`%%jupysql` alias so you can use both:
+
+```{code-cell} ipython3
+%jupysql duckdb://
+```
+
+```{code-cell} ipython3
+%jupysql SELECT * FROM "penguins.csv" LIMIT 3
+```
+
+```{code-cell} ipython3
+%%jupysql
+SELECT *
+FROM "penguins.csv"
+LIMIT 3
+```
diff --git a/src/sql/__init__.py b/src/sql/__init__.py
index b116e3e..55d6915 100644
--- a/src/sql/__init__.py
+++ b/src/sql/__init__.py
@@ -1,6 +1,6 @@
 from .magic import RenderMagic, SqlMagic, load_ipython_extension
 
-__version__ = "0.5.4dev"
+__version__ = "0.5.5dev"
 
 
 __all__ = [
diff --git a/src/sql/magic.py b/src/sql/magic.py
index 93ad922..8b26463 100644
--- a/src/sql/magic.py
+++ b/src/sql/magic.py
@@ -126,6 +126,8 @@ class SqlMagic(Magics, Configurable):
     @needs_local_scope
     @line_magic("sql")
     @cell_magic("sql")
+    @line_magic("jupysql")
+    @cell_magic("jupysql")
     @magic_arguments()
     @argument("line", default="", nargs="*", type=str, help="sql")
     @argument(


Test patch:
diff --git a/src/tests/integration/fixtures/database.py b/src/tests/integration/fixtures/database.py
new file mode 100644
index 0000000..31473f8
--- /dev/null
+++ b/src/tests/integration/fixtures/database.py
@@ -0,0 +1,96 @@
+import pandas as pd
+import pytest
+from sqlalchemy import create_engine
+
+
+databaseConfig = {
+    # Key: targetDB
+    "postgreSQL": {
+        "drivername": "postgresql",
+        "username": "ploomber_app",
+        "password": "ploomber_app_password",
+        "database": "db",
+        "host": "localhost",
+        "port": "5432",
+        "alias": "postgreSQLTest",
+    },
+    "mySQL": {
+        "drivername": "mysql+pymysql",
+        "username": "ploomber_app",
+        "password": "ploomber_app_password",
+        "database": "db",
+        "host": "localhost",
+        "port": "33306",
+        "alias": "mySQLTest",
+    },
+}
+
+
+# SQLAlchmey URL: https://docs.sqlalchemy.org/en/20/core/engines.html#database-urls
+def get_database_url(database):
+    return "{}://{}:{}@{}:{}/{}".format(
+        databaseConfig[database]["drivername"],
+        databaseConfig[database]["username"],
+        databaseConfig[database]["password"],
+        databaseConfig[database]["host"],
+        databaseConfig[database]["port"],
+        databaseConfig[database]["database"],
+    )
+
+
+def load_taxi_data(engine):
+    table_name = "taxi"
+    df = pd.DataFrame(
+        {"taxi_driver_name": ["Eric Ken", "John Smith", "Kevin Kelly"] * 15}
+    )
+    df.to_sql(name=table_name, con=engine, chunksize=100_000, if_exists="replace")
+
+
+@pytest.fixture(scope="session")
+def setup_postgreSQL():
+    engine = create_engine(get_database_url("postgreSQL"))
+    # Load taxi_data
+    load_taxi_data(engine)
+    yield engine
+    engine.dispose()
+
+
+@pytest.fixture
+def ip_with_postgreSQL(ip, setup_postgreSQL):
+    # Disconnect build-in sqlite connection
+    ip.run_cell("%sql --close sqlite://")
+    # Select database engine
+    ip.run_cell(
+        "%sql "
+        + get_database_url("postgreSQL")
+        + " --alias "
+        + databaseConfig["postgreSQL"]["alias"]
+    )
+    yield ip
+    # Disconnect database
+    ip.run_cell("%sql -x " + databaseConfig["postgreSQL"]["alias"])
+
+
+@pytest.fixture(scope="session")
+def setup_mySQL():
+    engine = create_engine(get_database_url("mySQL"))
+    # Load taxi_data
+    load_taxi_data(engine)
+    yield engine
+    engine.dispose()
+
+
+@pytest.fixture
+def ip_with_mySQL(ip, setup_mySQL):
+    # Disconnect build-in sqlite connection
+    ip.run_cell("%sql --close sqlite://")
+    # Select database engine
+    ip.run_cell(
+        "%sql "
+        + get_database_url("mySQL")
+        + " --alias "
+        + databaseConfig["mySQL"]["alias"]
+    )
+    yield ip
+    # Disconnect database
+    ip.run_cell("%sql -x " + databaseConfig["mySQL"]["alias"])
diff --git a/src/tests/integration/test_generic_db_opeations.py b/src/tests/integration/test_generic_db_opeations.py
new file mode 100644
index 0000000..ce51344
--- /dev/null
+++ b/src/tests/integration/test_generic_db_opeations.py
@@ -0,0 +1,62 @@
+import pytest
+# flake8: noqa
+from fixtures.database import *
+
+
+# Query
+@pytest.mark.parametrize(
+    "ip_with_dynamic_db, excepted", [("ip_with_postgreSQL", 3), ("ip_with_mySQL", 3)]
+)
+def test_query_count(ip_with_dynamic_db, excepted, request):
+    ip_with_dynamic_db = request.getfixturevalue(ip_with_dynamic_db)
+    out = ip_with_dynamic_db.run_line_magic("sql", "SELECT * FROM taxi LIMIT 3")
+    print("count out: ", len(out))
+    assert len(out) == excepted
+
+
+# Create
+@pytest.mark.parametrize(
+    "ip_with_dynamic_db, excepted", [("ip_with_postgreSQL", 15), ("ip_with_mySQL", 15)]
+)
+def test_create_table_with_indexed_df(ip_with_dynamic_db, excepted, request):
+    ip_with_dynamic_db = request.getfixturevalue(ip_with_dynamic_db)
+    ip_with_dynamic_db.run_cell("results = %sql SELECT * FROM taxi LIMIT 15")
+    ip_with_dynamic_db.run_cell("new_table_from_df = results.DataFrame()")
+    ip_with_dynamic_db.run_cell("%sql --persist sqlite:// new_table_from_df")
+    out = ip_with_dynamic_db.run_cell("%sql SELECT * FROM new_table_from_df")
+
+    assert len(out.result) == excepted
+
+
+# Connection
+def get_connection_count(ip_with_dynamic_db):
+    out = ip_with_dynamic_db.run_line_magic("sql", "-l")
+    print("Current connections:", out)
+    connections_count = len(out)
+    return connections_count
+
+
+# Test - Number of active connection
+@pytest.mark.parametrize(
+    "ip_with_dynamic_db, excepted", [("ip_with_postgreSQL", 1), ("ip_with_mySQL", 1)]
+)
+def test_active_connection_number(ip_with_dynamic_db, excepted, request):
+    ip_with_dynamic_db = request.getfixturevalue(ip_with_dynamic_db)
+    assert get_connection_count(ip_with_dynamic_db) == excepted
+
+
+@pytest.mark.parametrize(
+    "ip_with_dynamic_db, config_key",
+    [("ip_with_postgreSQL", "postgreSQL"), ("ip_with_mySQL", "mySQL")],
+)
+def test_close_and_connect(ip_with_dynamic_db, config_key, request):
+    ip_with_dynamic_db = request.getfixturevalue(ip_with_dynamic_db)
+    conn_alias = databaseConfig[config_key]["alias"]
+    # Disconnect
+    ip_with_dynamic_db.run_cell("%sql -x " + conn_alias)
+    assert get_connection_count(ip_with_dynamic_db) == 0
+    # Connect
+    ip_with_dynamic_db.run_cell(
+        "%sql " + get_database_url(config_key) + " --alias " + conn_alias
+    )
+    assert get_connection_count(ip_with_dynamic_db) == 1
diff --git a/src/tests/test_magic.py b/src/tests/test_magic.py
index 6a8e27f..f86059a 100644
--- a/src/tests/test_magic.py
+++ b/src/tests/test_magic.py
@@ -9,6 +9,7 @@ import pytest
 from sqlalchemy import create_engine
 
 from sql.connection import Connection
+from sql.magic import SqlMagic
 from conftest import runsql
 
 
@@ -460,3 +461,10 @@ def test_autolimit(ip):
     ip.run_line_magic("config", "SqlMagic.autolimit = 1")
     result = runsql(ip, "SELECT * FROM test;")
     assert len(result) == 1
+
+
+def test_jupysql_alias():
+    assert SqlMagic.magics == {
+        "line": {"jupysql": "execute", "sql": "execute"},
+        "cell": {"jupysql": "execute", "sql": "execute"},
+    }