Repository: ploomber/jupysql (python)

Issue:
Loss of :a functionality from ipython-sql
I am just migrating from ipython-sql and find that the change in variable substitution appears to have reduced simplicity and usability of the sql extension.  The notes in #137 state:

> Please aware the variable substition as {a}, $a, and :a format has been deprecated.  Use {{a}} instead.

But {{a}} appears to be a simple string substitution, which is a very different functionality to that provided by :a, which ensures that the variable is safely quoted for insertion into SQL.  Using {{a}} instead does not seem to work.

For users using the sql extension to created SQL focussed notebooks with minimal python (essentially documenting queries) this now means that the simple variable substitution must be replaced with code to quote and escape strings.  For example the following code breaks:

```
id=42
name='arthur'
%sql create table test(id integer, name varchar(30));
%sql insert into test values (:id,:name)
```

Is there something I have missed in the documentation that handles this common use case.

Pull request:
adds `:variable` parameter substitution
## Describe your changes

also fixing flake8 lint issues since version 6.1.0 added new rules

## Issue number

Closes #671

## Checklist before requesting a review

- [ ] Performed a self-review of my code
- [ ] Formatted my code with [`pkgmt format`](https://ploomber-contributing.readthedocs.io/en/latest/contributing/pr.html#linting-formatting)
- [ ] Added [tests](https://ploomber-contributing.readthedocs.io/en/latest/contributing/pr.html#testing) (when necessary).
- [ ] Added [docstring](https://ploomber-contributing.readthedocs.io/en/latest/contributing/pr.html#documenting-changes-and-new-features) documentation and update the [changelog](https://ploomber-contributing.readthedocs.io/en/latest/contributing/pr.html#changelog) (when needed)



<!-- readthedocs-preview jupysql start -->
----
:books: Documentation preview :books:: https://jupysql--770.org.readthedocs.build/en/770/

<!-- readthedocs-preview jupysql end -->

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 15849bf..9192173 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -9,6 +9,8 @@
 * [Fix] Fixed vertical color breaks in histograms (#702)
 * [Fix] Showing feedback when switching connections (#727)
 * [Fix] Fix error that caused some connections not to be closed when calling `--close/-x`
+* [Fix] Fix error that caused literals like `':something'` to be interpreted as query parameters
+* [Feature] allows parametrizing queries with `:variable` with `%config SqlMagic.named_paramstyle = True`
 
 ## 0.8.0 (2023-07-18)
 
diff --git a/doc/api/configuration.md b/doc/api/configuration.md
index 8e57cc4..107e570 100644
--- a/doc/api/configuration.md
+++ b/doc/api/configuration.md
@@ -242,7 +242,31 @@ res = %sql SELECT * FROM languages LIMIT 2
 print(res)
 ```
 
-## Loading configuration settings
+## `named_paramstyle`
+
+Default: `False`
+
+If True, it enables named parameters `:variable`. Learn more in the [tutorial.](../user-guide/template.md)
+
+```{code-cell} ipython3
+%config SqlMagic.named_paramstyle=True
+```
+
+```{code-cell} ipython3
+rating = 12
+```
+
+```{code-cell} ipython3
+%%sql
+SELECT *
+FROM languages
+WHERE rating > :rating
+```
+
+## Loading configuration from a `pyproject.toml` file
+
+```{versionadded} 0.9
+```
 
 You can define configurations in a `pyproject.toml` file and automatically load the configurations when you run `%load_ext sql`. If the file is not found in the current or parent directories, default values will be used. A sample `pyproject.toml` could look like this:
 
diff --git a/doc/user-guide/template.md b/doc/user-guide/template.md
index 6f057fe..bdad4d7 100644
--- a/doc/user-guide/template.md
+++ b/doc/user-guide/template.md
@@ -5,7 +5,7 @@ jupytext:
     extension: .md
     format_name: myst
     format_version: 0.13
-    jupytext_version: 1.14.6
+    jupytext_version: 1.14.7
 kernelspec:
   display_name: Python 3 (ipykernel)
   language: python
@@ -28,7 +28,7 @@ The legacy formats of parametrization, namely `{variable}`, `:variable`, and `$v
 ```
 
 
-## Variable Expansion
+## Parametrization via `{{variable}}`
 
 JupySQL supports variable expansion in the form of `{{variable}}`. This allows the user to write a query with placeholders that can be replaced by variables dynamically.
 
@@ -42,6 +42,11 @@ Let's load some data and connect to the in-memory DuckDB instance:
 
 ```{code-cell} ipython3
 %load_ext sql
+%sql duckdb://
+%config SqlMagic.displaylimit = 3
+```
+
+```{code-cell} ipython3
 from pathlib import Path
 from urllib.request import urlretrieve
 
@@ -50,10 +55,30 @@ if not Path("penguins.csv").is_file():
         "https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv",
         "penguins.csv",
     )
-%sql duckdb://
 ```
 
-Now, let's define a simple query template with placeholders, and substitute the placeholders with a couple of variables using variable expansion.
+The simplest use case is to use a variable to determine which data to filter:
+
++++
+
+### Data filtering
+
+```{code-cell} ipython3
+sex = "MALE"
+```
+
+```{code-cell} ipython3
+%%sql
+SELECT *
+FROM penguins.csv
+WHERE  sex = '{{sex}}'
+```
+
+Note that we have to add quotes around `{{sex}}`, since the literal is replaced.
+
++++
+
+`{{variable}}` parameters are not limited to `WHERE` clauses, you can use them anywhere:
 
 ```{code-cell} ipython3
 dynamic_limit = 5
@@ -64,11 +89,15 @@ dynamic_column = "island, sex"
 %sql SELECT {{dynamic_column}} FROM penguins.csv LIMIT {{dynamic_limit}}
 ```
 
-Note that the variables substituted in the SQL statement are fetched from the local namespace.
+### SQL generation
 
-## Variable expansion using loops
+```{note}
+We use [jinja](https://jinja.palletsprojects.com/en/3.1.x/) to parametrize queries, to learn more about the syntax, check our their docs.
+```
+
+Since there are no restrictions on where you can use `{{variable}}` you can use it to dynamically generate SQL if you also use advanced control structures. 
 
-Now, let's look at parametrizing queries using a for loop. First, we'll create a set of unique `sex` values. This is required since the dataset contains samples for which `sex` couldn't be determined (`null`).
+Let's look at generating SQL queries using a `{% for %}` loop. First, we'll create a set of unique `sex` values. This is required since the dataset contains samples for which `sex` couldn't be determined (`null`).
 
 ```{code-cell} ipython3
 sex = ("MALE", "FEMALE")
@@ -96,9 +125,11 @@ final = %sqlcmd snippets avg_body_mass
 print(final)
 ```
 
-## Macros + variable expansion
+### SQL generation with macros
+
+If `{% for %}` lops are not enough, you can modularize your code generation even more with macros.
 
-`Macros` is a construct analogous to functions that promote re-usability. We'll first define a macro for converting a value from `millimetre` to `centimetre`. And then use this macro in the query using variable expansion.
+macros is a construct analogous to functions that promote re-usability. We'll first define a macro for converting a value from `millimetre` to `centimetre`. And then use this macro in the query using variable expansion.
 
 ```{code-cell} ipython3
 %%sql --save convert
@@ -120,13 +151,45 @@ final = %sqlcmd snippets convert
 print(final)
 ```
 
+### Using snippets
+
+You can combine the snippets feature with `{{variable}}`:
+
 ```{code-cell} ipython3
-%sqlcmd snippets -d convert
+species = "Adelie"
 ```
 
-## Create tables in loop
+```{code-cell} ipython3
+%%sql --save one_species --no-execute
+SELECT * FROM penguins.csv
+WHERE species = '{{species}}'
+```
+
+```{code-cell} ipython3
+%%sql
+SELECT *
+FROM one_species
+```
 
-We can also create multiple tables in a loop using parametrized queries. Let's segregate the dataset by `island`.
+```{important}
+When storing a snippet with `{{variable}}`, the values are replaced upon saving, so assigning a new value to `variable` won't have any effect.
+```
+
+```{code-cell} ipython3
+species = "Gentoo"
+```
+
+```{code-cell} ipython3
+%%sql
+SELECT *
+FROM one_species
+```
+
+### Combining Python and `{{variable}}`
+
+You can combine Python code with the `%sql` magic to execute parametrized queries.
+
+Let's see how we can create multiple tables, each one containing the penguins for a given `island`.
 
 ```{code-cell} ipython3
 for island in ("Torgersen", "Biscoe", "Dream"):
@@ -139,8 +202,94 @@ for island in ("Torgersen", "Biscoe", "Dream"):
 
 Let's verify data in one of the tables:
 
+```{code-cell} ipython3
+%sql SELECT * FROM Dream;
+```
+
 ```{code-cell} ipython3
 %sql SELECT * FROM Torgersen;
 ```
 
-[Click here](https://jupysql.ploomber.io/en/latest/intro.html?highlight=variable#variable-substitution) for more details.
+## Parametrization via `:variable`
+
+```{versionadded} 0.9
+```
+
+There is a second method to parametrize variables via `:variable`. This method has the following limitations
+
+- Only available for SQLAlchemy connections
+- Only works for data filtering parameters (`WHERE`, `IN`, `>=`, etc.)
+
+
+To enable it:
+
+```{code-cell} ipython3
+%config SqlMagic.named_paramstyle = True
+```
+
+```{code-cell} ipython3
+sex = "MALE"
+```
+
+```{code-cell} ipython3
+%%sql
+SELECT *
+FROM penguins.csv
+WHERE sex = :sex
+```
+
+Note that we don't have to quote `:sex`. When using `:variable`, if `variable` is a string, it'll automatically be quoted. 
+
+Here's another example where we use the parameters for an `IN` and a `>=` clauses:
+
+```{code-cell} ipython3
+one = "Adelie"
+another = "Chinstrap"
+min_body_mass_g = 4500
+```
+
+```{code-cell} ipython3
+%%sql
+SELECT *
+FROM penguins.csv
+WHERE species IN (:one, :another)
+AND body_mass_g >= :min_body_mass_g
+```
+
+Parametrizing other parts of the query like table names or column names won't work.
+
+```{code-cell} ipython3
+tablename = "penguins.csv"
+```
+
+```{code-cell} ipython3
+:tags: [raises-exception]
+
+%%sql
+SELECT *
+FROM :tablename
+```
+
+### Using snippets and `:variable`
+
+Unlike `{{variable}`, `:variable` parameters are evaluated at execution time, meaning you can `--save` a query and the output will change depending on the value of `variable` when the query is executed:
+
+```{code-cell} ipython3
+sex = "MALE"
+```
+
+```{code-cell} ipython3
+%%sql --save one_sex
+SELECT *
+FROM penguins.csv
+WHERE sex = :sex
+```
+
+```{code-cell} ipython3
+sex = "FEMALE"
+```
+
+```{code-cell} ipython3
+%%sql
+SELECT * FROM one_sex
+```
diff --git a/src/sql/connection/connection.py b/src/sql/connection/connection.py
index 261f0a0..373d90b 100644
--- a/src/sql/connection/connection.py
+++ b/src/sql/connection/connection.py
@@ -1,3 +1,5 @@
+import warnings
+import difflib
 import abc
 import os
 from difflib import get_close_matches
@@ -5,17 +7,19 @@ import atexit
 
 import sqlalchemy
 from sqlalchemy.engine import Engine
-from sqlalchemy.exc import NoSuchModuleError, OperationalError
+from sqlalchemy.exc import NoSuchModuleError, OperationalError, StatementError
 from IPython.core.error import UsageError
-import difflib
 import sqlglot
+from ploomber_core.exceptions import modify_exceptions
 
 
 from sql.store import store
 from sql.telemetry import telemetry
 from sql import exceptions, display
 from sql.error_message import detail
-from ploomber_core.exceptions import modify_exceptions
+from sql.parse import escape_string_literals_with_colon_prefix, find_named_parameters
+from sql.warnings import JupySQLQuotedNamedParametersWarning
+
 
 PLOOMBER_DOCS_LINK_STR = (
     "Documentation: https://jupysql.ploomber.io/en/latest/connecting.html"
@@ -332,7 +336,7 @@ class AbstractConnection(abc.ABC):
         pass
 
     @abc.abstractmethod
-    def raw_execute(self, query):
+    def raw_execute(self, query, parameters=None):
         """Run the query without any pre-processing"""
         pass
 
@@ -347,7 +351,9 @@ class AbstractConnection(abc.ABC):
     def close(self):
         """Close the connection"""
         for rs in self._result_sets:
-            rs._sqlaproxy.close()
+            # this might be None if it didn't run any query
+            if rs._sqlaproxy is not None:
+                rs._sqlaproxy.close()
 
         self.connection.close()
 
@@ -501,8 +507,68 @@ class SQLAlchemyConnection(AbstractConnection):
     def dialect(self):
         return self._dialect
 
-    def raw_execute(self, query):
-        return self.connection.execute(sqlalchemy.text(query))
+    def raw_execute(self, query, parameters=None):
+        """Run the query without any preprocessing
+
+        Parameters
+        ----------
+        query : str
+            SQL query
+
+        parameters : dict, default None
+            Parameters to use in the query. They should appear in the query with the
+            :name format (no quotes around them)
+        """
+        query, quoted_named_parameters = escape_string_literals_with_colon_prefix(query)
+
+        if quoted_named_parameters and parameters:
+            intersection = set(quoted_named_parameters) & set(parameters)
+
+            if intersection:
+                intersection_ = ", ".join(sorted(intersection))
+                warnings.warn(
+                    f"The following variables are defined: {intersection_}. However "
+                    "the parameters are quoted in the query, if you want to use "
+                    "them as named parameters, remove the quotes.",
+                    category=JupySQLQuotedNamedParametersWarning,
+                )
+
+        if parameters:
+            required_parameters = set(sqlalchemy.text(query).compile().params)
+            available_parameters = set(parameters)
+            missing_parameters = required_parameters - available_parameters
+
+            if missing_parameters:
+                raise exceptions.InvalidQueryParameters(
+                    "Cannot execute query because the following "
+                    "variables are undefined: {}".format(", ".join(missing_parameters))
+                )
+
+            if IS_SQLALCHEMY_ONE:
+                results = self.connection.execute(sqlalchemy.text(query), **parameters)
+            else:
+                results = self.connection.execute(
+                    sqlalchemy.text(query), parameters=parameters
+                )
+
+            return results
+        else:
+            try:
+                return self.connection.execute(sqlalchemy.text(query))
+            except StatementError as e:
+                # add a more helpful message if the users passes :variable but
+                # the feature isn't enabled
+                if parameters is None:
+                    named_params = find_named_parameters(query)
+
+                    if named_params:
+                        named_params_ = ", ".join(named_params)
+                        e.add_detail(
+                            f"Your query contains named parameters ({named_params_}) "
+                            "but the named parameters feature is disabled. Enable it "
+                            "with: %config SqlMagic.named_paramstyle=True"
+                        )
+                raise
 
     def _get_database_information(self):
         dialect = self.connection_sqlalchemy.dialect
@@ -592,7 +658,18 @@ class DBAPIConnection(AbstractConnection):
     def dialect(self):
         return self._dialect
 
-    def raw_execute(self, query):
+    def raw_execute(self, query, parameters=None):
+        """Run the query without any preprocessing
+
+        Parameters
+        ----------
+        query : str
+            SQL query
+
+        parameters : dict, default None
+            This parameter is added for consistency with SQLAlchemy connections but
+            it is not used
+        """
         cur = self.connection.cursor()
         cur.execute(query)
         return cur
diff --git a/src/sql/exceptions.py b/src/sql/exceptions.py
index 350d81c..2b7f139 100644
--- a/src/sql/exceptions.py
+++ b/src/sql/exceptions.py
@@ -47,3 +47,6 @@ TableNotFoundError = exception_factory("TableNotFoundError")
 
 # raise it when there is an error in parsing pyproject.toml file
 ConfigurationError = exception_factory("ConfigurationError")
+
+
+InvalidQueryParameters = exception_factory("InvalidQueryParameters")
diff --git a/src/sql/magic.py b/src/sql/magic.py
index 5834b5b..a300e12 100644
--- a/src/sql/magic.py
+++ b/src/sql/magic.py
@@ -15,7 +15,12 @@ from IPython.core.magic import (
     no_var_expand,
 )
 from IPython.core.magic_arguments import argument, magic_arguments, parse_argstring
-from sqlalchemy.exc import OperationalError, ProgrammingError, DatabaseError
+from sqlalchemy.exc import (
+    OperationalError,
+    ProgrammingError,
+    DatabaseError,
+    StatementError,
+)
 from traitlets.config.configurable import Configurable
 from traitlets import Bool, Int, TraitError, Unicode, Dict, observe, validate
 
@@ -152,6 +157,15 @@ class SqlMagic(Magics, Configurable):
     )
     autocommit = Bool(True, config=True, help="Set autocommit mode")
 
+    named_paramstyle = Bool(
+        False,
+        config=True,
+        help=(
+            "Allow named parameters in queries "
+            "(i.e., 'SELECT * FROM foo WHERE bar = :bar')"
+        ),
+    )
+
     @telemetry.log_call("init")
     def __init__(self, shell):
         self._store = store
@@ -360,17 +374,18 @@ class SqlMagic(Magics, Configurable):
     @telemetry.log_call("execute", payload=True)
     @modify_exceptions
     def _execute(self, payload, line, cell, local_ns, is_interactive_mode=False):
-        def interactive_execute_wrapper(**kwargs):
-            for key, value in kwargs.items():
-                local_ns[key] = value
-            return self._execute(line, cell, local_ns, is_interactive_mode=True)
-
         """
         This function implements the cell logic; we create this private
         method so we can control how the function is called. Otherwise,
         decorating ``SqlMagic.execute`` will break when adding the ``@log_call``
         decorator with ``payload=True``
         """
+
+        def interactive_execute_wrapper(**kwargs):
+            for key, value in kwargs.items():
+                local_ns[key] = value
+            return self._execute(line, cell, local_ns, is_interactive_mode=True)
+
         # line is the text after the magic, cell is the cell's body
 
         # Examples
@@ -513,7 +528,12 @@ class SqlMagic(Magics, Configurable):
             return
 
         try:
-            result = run_statements(conn, command.sql, self)
+            result = run_statements(
+                conn,
+                command.sql,
+                self,
+                parameters=user_ns if self.named_paramstyle else None,
+            )
 
             if (
                 result is not None
@@ -548,7 +568,13 @@ class SqlMagic(Magics, Configurable):
                 return result
 
         # JA: added DatabaseError for MySQL
-        except (ProgrammingError, OperationalError, DatabaseError) as e:
+        except (
+            ProgrammingError,
+            OperationalError,
+            DatabaseError,
+            # raised when they query has :parameters but no parameters are given
+            StatementError,
+        ) as e:
             # Sqlite apparently return all errors as OperationalError :/
             self._error_handling(e, command.sql)
         except Exception as e:
diff --git a/src/sql/parse.py b/src/sql/parse.py
index b0f15de..96fc29b 100644
--- a/src/sql/parse.py
+++ b/src/sql/parse.py
@@ -1,3 +1,4 @@
+import re
 import itertools
 import shlex
 from os.path import expandvars
@@ -105,3 +106,42 @@ def without_sql_comment(parser, line):
 def magic_args(magic_execute, line):
     line = without_sql_comment(parser=magic_execute.parser, line=line)
     return parse_argstring(magic_execute, line)
+
+
+def escape_string_literals_with_colon_prefix(query):
+    """
+    Given a query, replaces all occurrences of ':variable' with '\:variable' and
+    ":variable" with "\:variable" so that the query can be passed to sqlalchemy.text
+    without the literals being interpreted as bind parameters. It doesn't replace
+    the occurrences of :variable (without quotes)
+    """  # noqa
+
+    # Define the regular expression pattern for valid Python identifiers
+    identifier_pattern = r"\b[a-zA-Z_][a-zA-Z0-9_]*\b"
+
+    double_quoted_variable_pattern = r'(?<!\\)":(' + identifier_pattern + r')(?<!\\)"'
+
+    # Define the regular expression pattern for matching ':variable' format
+    single_quoted_variable_pattern = r"(?<!\\)':(" + identifier_pattern + r")(?<!\\)\'"
+
+    # Replace ":variable" and ':variable' with "\:variable"
+    query_quoted = re.sub(double_quoted_variable_pattern, r'"\\:\1"', query)
+    query_quoted = re.sub(single_quoted_variable_pattern, r"'\\:\1'", query_quoted)
+
+    double_found = re.findall(double_quoted_variable_pattern, query)
+    single_found = re.findall(single_quoted_variable_pattern, query)
+
+    return query_quoted, double_found + single_found
+
+
+def find_named_parameters(input_string):
+    # Define the regular expression pattern for valid Python identifiers
+    identifier_pattern = r"\b[a-zA-Z_][a-zA-Z0-9_]*\b"
+
+    # Define the regular expression pattern for matching :variable format
+    variable_pattern = r'(?<!["\'])\:(' + identifier_pattern + ")"
+
+    # Use findall to extract all matches of :variable from the input string
+    matches = re.findall(variable_pattern, input_string)
+
+    return matches
diff --git a/src/sql/run/run.py b/src/sql/run/run.py
index 4e15fbc..52d90f7 100644
--- a/src/sql/run/run.py
+++ b/src/sql/run/run.py
@@ -17,7 +17,7 @@ _COMMIT_BLACKLIST_DIALECTS = (
 )
 
 
-def run_statements(conn, sql, config):
+def run_statements(conn, sql, config, parameters=None):
     """
     Run a SQL query (supports running multiple SQL statements) with the given
     connection. This is the function that's called when executing SQL magic.
@@ -63,7 +63,7 @@ def run_statements(conn, sql, config):
             if is_select and conn.dialect == "duckdb":
                 manual_commit_call_required = False
 
-            result = conn.raw_execute(statement)
+            result = conn.raw_execute(statement, parameters=parameters)
 
             if manual_commit_call_required:
                 _commit_if_needed(conn=conn, config=config)
diff --git a/src/sql/warnings.py b/src/sql/warnings.py
index 30e1d3f..10839e6 100644
--- a/src/sql/warnings.py
+++ b/src/sql/warnings.py
@@ -1,2 +1,6 @@
 class JupySQLDataFramePerformanceWarning(UserWarning):
     pass
+
+
+class JupySQLQuotedNamedParametersWarning(UserWarning):
+    pass


Test patch:
diff --git a/src/sql/_testing.py b/src/sql/_testing.py
index b87eb64..5762065 100644
--- a/src/sql/_testing.py
+++ b/src/sql/_testing.py
@@ -34,10 +34,7 @@ class TestingShell(InteractiveShell):
 
     def run_cell(self, *args, **kwargs):
         result = super().run_cell(*args, **kwargs)
-
-        if result.error_in_exec is not None:
-            raise result.error_in_exec
-
+        result.raise_error()
         return result
 
 
diff --git a/src/tests/conftest.py b/src/tests/conftest.py
index a899364..7357b18 100644
--- a/src/tests/conftest.py
+++ b/src/tests/conftest.py
@@ -89,49 +89,58 @@ def ip_empty():
     ConnectionManager.close_all()
 
 
+def insert_sample_data(ip):
+    ip.run_cell(
+        """%%sql
+CREATE TABLE test (n INT, name TEXT);
+INSERT INTO test VALUES (1, 'foo');
+INSERT INTO test VALUES (2, 'bar');
+CREATE TABLE [table with spaces] (first INT, second TEXT);
+CREATE TABLE author (first_name, last_name, year_of_death);
+INSERT INTO author VALUES ('William', 'Shakespeare', 1616);
+INSERT INTO author VALUES ('Bertold', 'Brecht', 1956);
+CREATE TABLE empty_table (column INT, another INT);
+CREATE TABLE website (person, link, birthyear INT);
+INSERT INTO website VALUES ('Bertold Brecht',
+    'https://en.wikipedia.org/wiki/Bertolt_Brecht', 1954 );
+INSERT INTO website VALUES ('William Shakespeare',
+    'https://en.wikipedia.org/wiki/William_Shakespeare', 1564);
+INSERT INTO website VALUES ('Steve Steve', 'google_link', 2023);
+CREATE TABLE number_table (x INT, y INT);
+INSERT INTO number_table VALUES (4, (-2));
+INSERT INTO number_table VALUES ((-5), 0);
+INSERT INTO number_table VALUES (2, 4);
+INSERT INTO number_table VALUES (0, 2);
+INSERT INTO number_table VALUES ((-5), (-1));
+INSERT INTO number_table VALUES ((-2), (-3));
+INSERT INTO number_table VALUES ((-2), (-3));
+INSERT INTO number_table VALUES ((-4), 2);
+INSERT INTO number_table VALUES (2, (-5));
+INSERT INTO number_table VALUES (4, 3);
+"""
+    )
+
+
 @pytest.fixture
 def ip(ip_empty):
     """Provides an IPython session in which tables have been created"""
+    ip_empty.run_cell("%sql sqlite://")
+    insert_sample_data(ip_empty)
 
-    # runsql creates an inmemory sqlitedatabase
-    runsql(
-        ip_empty,
-        [
-            "CREATE TABLE test (n INT, name TEXT)",
-            "INSERT INTO test VALUES (1, 'foo')",
-            "INSERT INTO test VALUES (2, 'bar')",
-            "CREATE TABLE [table with spaces] (first INT, second TEXT)",
-            "CREATE TABLE author (first_name, last_name, year_of_death)",
-            "INSERT INTO author VALUES ('William', 'Shakespeare', 1616)",
-            "INSERT INTO author VALUES ('Bertold', 'Brecht', 1956)",
-            "CREATE TABLE empty_table (column INT, another INT)",
-            "CREATE TABLE website (person, link, birthyear INT)",
-            """INSERT INTO website VALUES ('Bertold Brecht',
-            'https://en.wikipedia.org/wiki/Bertolt_Brecht', 1954 )""",
-            """INSERT INTO website VALUES ('William Shakespeare',
-            'https://en.wikipedia.org/wiki/William_Shakespeare', 1564)""",
-            "INSERT INTO website VALUES ('Steve Steve', 'google_link', 2023)",
-            "CREATE TABLE number_table (x INT, y INT)",
-            "INSERT INTO number_table VALUES (4, (-2))",
-            "INSERT INTO number_table VALUES ((-5), 0)",
-            "INSERT INTO number_table VALUES (2, 4)",
-            "INSERT INTO number_table VALUES (0, 2)",
-            "INSERT INTO number_table VALUES ((-5), (-1))",
-            "INSERT INTO number_table VALUES ((-2), (-3))",
-            "INSERT INTO number_table VALUES ((-2), (-3))",
-            "INSERT INTO number_table VALUES ((-4), 2)",
-            "INSERT INTO number_table VALUES (2, (-5))",
-            "INSERT INTO number_table VALUES (4, 3)",
-        ],
-    )
     yield ip_empty
 
     ConnectionManager.close_all()
 
-    runsql(ip_empty, "DROP TABLE IF EXISTS test")
-    runsql(ip_empty, "DROP TABLE IF EXISTS author")
-    runsql(ip_empty, "DROP TABLE IF EXISTS website")
-    runsql(ip_empty, "DROP TABLE IF EXISTS number_table")
+
+@pytest.fixture
+def ip_dbapi(ip_empty):
+    ip_empty.run_cell("import sqlite3; conn = sqlite3.connect(':memory:');")
+    ip_empty.run_cell("%sql conn")
+    insert_sample_data(ip_empty)
+
+    yield ip_empty
+
+    ConnectionManager.close_all()
 
 
 @pytest.fixture
diff --git a/src/tests/test_magic.py b/src/tests/test_magic.py
index 4819817..ab4bd37 100644
--- a/src/tests/test_magic.py
+++ b/src/tests/test_magic.py
@@ -17,6 +17,7 @@ from sql.connection import ConnectionManager
 from sql.magic import SqlMagic
 from sql.run.resultset import ResultSet
 from sql import magic
+from sql.warnings import JupySQLQuotedNamedParametersWarning
 
 from conftest import runsql
 from sql.connection import PLOOMBER_DOCS_LINK_STR
@@ -708,7 +709,7 @@ def test_autopolars(ip):
     ip.run_line_magic("config", "SqlMagic.autopolars = True")
     dframe = runsql(ip, "SELECT * FROM test;")
 
-    assert type(dframe) == pl.DataFrame
+    assert isinstance(dframe, pl.DataFrame)
     assert not dframe.is_empty()
     assert len(dframe.shape) == 2
     assert dframe["name"][0] == "foo"
@@ -747,27 +748,27 @@ def test_autopolars_infer_schema_length(ip):
 
 def test_mutex_autopolars_autopandas(ip):
     dframe = runsql(ip, "SELECT * FROM test;")
-    assert type(dframe) == ResultSet
+    assert isinstance(dframe, ResultSet)
 
     ip.run_line_magic("config", "SqlMagic.autopolars = True")
     dframe = runsql(ip, "SELECT * FROM test;")
-    assert type(dframe) == pl.DataFrame
+    assert isinstance(dframe, pl.DataFrame)
 
     import pandas as pd
 
     ip.run_line_magic("config", "SqlMagic.autopandas = True")
     dframe = runsql(ip, "SELECT * FROM test;")
-    assert type(dframe) == pd.DataFrame
+    assert isinstance(dframe, pd.DataFrame)
 
     # Test that re-enabling autopolars works
     ip.run_line_magic("config", "SqlMagic.autopolars = True")
     dframe = runsql(ip, "SELECT * FROM test;")
-    assert type(dframe) == pl.DataFrame
+    assert isinstance(dframe, pl.DataFrame)
 
     # Disabling autopolars at this point should result in the default behavior
     ip.run_line_magic("config", "SqlMagic.autopolars = False")
     dframe = runsql(ip, "SELECT * FROM test;")
-    assert type(dframe) == ResultSet
+    assert isinstance(dframe, ResultSet)
 
 
 def test_csv(ip):
@@ -1522,3 +1523,142 @@ displaycon = false
 
     # revert back to a default setting
     setattr(sql, "displaycon", True)
+
+
+@pytest.mark.parametrize(
+    "fixture_name",
+    [
+        "ip",
+        "ip_dbapi",
+    ],
+)
+def test_interpolation_ignore_literals(fixture_name, request):
+    ip = request.getfixturevalue(fixture_name)
+
+    ip.run_cell("%config SqlMagic.named_paramstyle = True")
+
+    # this isn't a parameter because it's quoted (':last_name')
+    result = ip.run_cell(
+        "%sql select * from author where last_name = ':last_name'"
+    ).result
+    assert result.dict() == {}
+
+
+def test_sqlalchemy_interpolation(ip):
+    ip.run_cell("%config SqlMagic.named_paramstyle = True")
+
+    ip.run_cell("last_name = 'Shakespeare'")
+
+    # define another variable to ensure the test doesn't break if there are more
+    # variables in the namespace
+    ip.run_cell("first_name = 'William'")
+
+    result = ip.run_cell(
+        "%sql select * from author where last_name = :last_name"
+    ).result
+
+    assert result.dict() == {
+        "first_name": ("William",),
+        "last_name": ("Shakespeare",),
+        "year_of_death": (1616,),
+    }
+
+
+def test_sqlalchemy_interpolation_missing_parameter(ip):
+    ip.run_cell("%config SqlMagic.named_paramstyle = True")
+
+    with pytest.raises(UsageError) as excinfo:
+        ip.run_cell("%sql select * from author where last_name = :last_name")
+
+    assert (
+        "Cannot execute query because the following variables are undefined: last_name"
+        in str(excinfo.value)
+    )
+
+
+@pytest.mark.parametrize(
+    "fixture_name",
+    [
+        "ip",
+        "ip_dbapi",
+    ],
+)
+def test_sqlalchemy_insert_literals_with_colon_character(fixture_name, request):
+    ip = request.getfixturevalue(fixture_name)
+
+    ip.run_cell(
+        """%%sql
+CREATE TABLE names (
+    name VARCHAR(50) NOT NULL
+);
+
+INSERT INTO names (name)
+VALUES
+    ('John'),
+    (':Mary'),
+    ('Alex'),
+    (':Lily'),
+    ('Michael'),
+    ('Robert'),
+    (':Sarah'),
+    ('Jennifer'),
+    (':Tom'),
+    ('Jessica');
+"""
+    )
+
+    result = ip.run_cell("%sql SELECT * FROM names WHERE name = ':Mary'").result
+
+    assert result.dict() == {"name": (":Mary",)}
+
+
+def test_error_suggests_turning_feature_on_if_it_detects_named_params(ip):
+    ip.run_cell("%config SqlMagic.named_paramstyle = False")
+
+    with pytest.raises(UsageError) as excinfo:
+        ip.run_cell("%sql SELECT * FROM penguins.csv where species = :species")
+
+    suggestion = (
+        "Your query contains named parameters (species) but the named "
+        "parameters feature is disabled. Enable it with: "
+        "%config SqlMagic.named_paramstyle=True"
+    )
+    assert suggestion in str(excinfo.value)
+
+
+@pytest.mark.parametrize(
+    "cell, expected_warning",
+    [
+        (
+            "%sql SELECT * FROM author where last_name = ':last_name'",
+            "The following variables are defined: last_name.",
+        ),
+        (
+            "%sql SELECT * FROM author where last_name = ':last_name' "
+            "and first_name = :first_name",
+            "The following variables are defined: last_name.",
+        ),
+        (
+            "%sql SELECT * FROM author where last_name = ':last_name' "
+            "and first_name = ':first_name'",
+            "The following variables are defined: first_name, last_name.",
+        ),
+    ],
+    ids=[
+        "one-quoted",
+        "one-quoted-one-unquoted",
+        "two-quoted",
+    ],
+)
+def test_warning_if_variable_defined_but_named_param_is_quoted(
+    ip, cell, expected_warning
+):
+    ip.run_cell("%config SqlMagic.named_paramstyle = True")
+    ip.run_cell("last_name = 'Shakespeare'")
+    ip.run_cell("first_name = 'William'")
+
+    with pytest.warns(
+        JupySQLQuotedNamedParametersWarning,
+        match=expected_warning,
+    ):
+        ip.run_cell(cell)
diff --git a/src/tests/test_parse.py b/src/tests/test_parse.py
index d1d594d..1c451de 100644
--- a/src/tests/test_parse.py
+++ b/src/tests/test_parse.py
@@ -9,6 +9,8 @@ from sql.parse import (
     parse,
     without_sql_comment,
     magic_args,
+    escape_string_literals_with_colon_prefix,
+    find_named_parameters,
 )
 
 try:
@@ -278,3 +280,65 @@ def test_magic_args(ip, line, expected):
     args = magic_args(sql_line, line)
 
     assert args.__dict__ == complete_with_defaults(expected)
+
+
+@pytest.mark.parametrize(
+    "query, expected_escaped, expected_found",
+    [
+        ("SELECT * FROM table where x > :x", "SELECT * FROM table where x > :x", []),
+        (
+            "SELECT * FROM table where x > ':x'",
+            "SELECT * FROM table where x > '\\:x'",
+            ["x"],
+        ),
+        (
+            'SELECT * FROM table where x > ":y"',
+            'SELECT * FROM table where x > "\\:y"',
+            ["y"],
+        ),
+        (
+            "SELECT * FROM table where x > '':something''",
+            "SELECT * FROM table where x > ''\\:something''",
+            ["something"],
+        ),
+        (
+            'SELECT * FROM table where x > "":var""',
+            'SELECT * FROM table where x > ""\\:var""',
+            ["var"],
+        ),
+    ],
+    ids=[
+        "no-escape",
+        "single-quote",
+        "double-quote",
+        "double-single-quote",
+        "double-double-quote",
+    ],
+)
+def test_escape_string_literals_with_colon_prefix(
+    query, expected_escaped, expected_found
+):
+    escaped, found = escape_string_literals_with_colon_prefix(query)
+    assert escaped == expected_escaped
+    assert found == expected_found
+
+
+@pytest.mark.parametrize(
+    "query, expected",
+    [
+        (
+            "SELECT * FROM penguins WHERE species = :species AND mass = ':mass'",
+            ["species"],
+        ),
+        (
+            'SELECT * FROM penguins WHERE species = :species AND mass = ":mass"',
+            ["species"],
+        ),
+        (
+            "SELECT * FROM penguins WHERE species = :species AND mass = :mass",
+            ["species", "mass"],
+        ),
+    ],
+)
+def test_find_named_parameters(query, expected):
+    assert find_named_parameters(query) == expected
diff --git a/src/tests/test_testing.py b/src/tests/test_testing.py
new file mode 100644
index 0000000..ee8031d
--- /dev/null
+++ b/src/tests/test_testing.py
@@ -0,0 +1,18 @@
+import pytest
+
+from sql._testing import TestingShell
+
+
+@pytest.fixture(scope="module")
+def ip():
+    return TestingShell()
+
+
+def test_testingshell_raises_code_errors(ip):
+    with pytest.raises(ZeroDivisionError):
+        ip.run_cell("1 / 0")
+
+
+def test_testingshell_raises_syntax_errors(ip):
+    with pytest.raises(SyntaxError):
+        ip.run_cell("SELECT * FROM penguins.csv where species = :species")