[FIXED] KeyError:'1,2' running Postgresql regexp_match() in Jupyter Notebook

Issue

I am using Jupyter notebook to run postgresql. Python, postgres, pips and Jupyter are all up to date. I have been able to load sql with

%load_ext sql

and connected to the postgres database with

%sqlpostgresql://postgres:postgres@localhost/analysis

Table was created and loaded a csv loaded successfully with

%%sql
CREATE TABLE crime_reports (
 crime_id bigserial PRIMARY KEY,
 date_1 timestamp with time zone,
 date_2 timestamp with time zone,
 street varchar(250),
 city varchar(100),
 crime_type varchar(100),
 description text,
 case_number varchar(50),
 original_text text NOT NULL
);
COPY crime_reports (original_text)
FROM 'C:\YourDirectory\crime_reports.csv'
WITH (FORMAT CSV, HEADER OFF, QUOTE '"');

However, this code

%%sql
SELECT crime_id,
 regexp_match(original_text, '\d{1,2}\/\d{1,2}\/\d{2}')
FROM crime_reports;

runs successfully in pgAdmin, returning dates but in the Jupyter notebook I get this error instead:

KeyError                                  Traceback (most recent call last)
~\AppData\Local\Temp/ipykernel_10976/608277809.py in <module>
----> 1 get_ipython().run_cell_magic('sql', '', "SELECT crime_id,\n regexp_match(original_text, '\\d{1,2}\\/\\d{1,2}\\/\\d{2}')\nFROM crime_reports;\n")

~\AppData\Local\Programs\Python\Python310\lib\site-packages\IPython\core\interactiveshell.py in run_cell_magic(self, magic_name, line, cell)
   2417             with self.builtin_trap:
   2418                 args = (magic_arg_s, cell)
-> 2419                 result = fn(*args, **kwargs)
   2420             return result
   2421 

~\AppData\Local\Programs\Python\Python310\lib\site-packages\decorator.py in fun(*args, **kw)
    230             if not kwsyntax:
    231                 args, kw = fix(args, kw, sig)
--> 232             return caller(func, *(extras + args), **kw)
    233     fun.__name__ = func.__name__
    234     fun.__doc__ = func.__doc__

~\AppData\Local\Programs\Python\Python310\lib\site-packages\IPython\core\magic.py in <lambda>(f, *a, **k)
    185     # but it's overkill for just that one bit of state.
    186     def magic_deco(arg):
--> 187         call = lambda f, *a, **k: f(*a, **k)
    188 
    189         if callable(arg):

~\AppData\Local\Programs\Python\Python310\lib\site-packages\decorator.py in fun(*args, **kw)
    230             if not kwsyntax:
    231                 args, kw = fix(args, kw, sig)
--> 232             return caller(func, *(extras + args), **kw)
    233     fun.__name__ = func.__name__
    234     fun.__doc__ = func.__doc__

~\AppData\Local\Programs\Python\Python310\lib\site-packages\IPython\core\magic.py in <lambda>(f, *a, **k)
    185     # but it's overkill for just that one bit of state.
    186     def magic_deco(arg):
--> 187         call = lambda f, *a, **k: f(*a, **k)
    188 
    189         if callable(arg):

~\AppData\Local\Programs\Python\Python310\lib\site-packages\sql\magic.py in execute(self, line, cell, local_ns)
    149         cell_params = {}
    150         for variable in cell_variables:
--> 151             cell_params[variable] = local_ns[variable]
    152         cell = cell.format(**cell_params)
    153 

KeyError: '1,2'

I have tried trusting the Notebook and still getting same error.

Solution

Try to replace this:

regexp_match(original_text, '\d{1,2}\/\d{1,2}\/\d{2}')

with this:

regexp_match(original_text, '\d{{1,2}}\/\d{{1,2}}\/\d{{2}}')

Probably it's python formatting and you need to escape your {}s.

Answered By - Yevgeniy Kosmak