Wildcards not working in postgres engine

[WakeMeAtThree]

Hello, I have a postgres database that I connected to in jupyter, and when I query using LIKE '%1' like this:

SELECT topic_title
FROM engagement
WHERE url LIKE '%/1' AND is_pm = 'No';

I get: <sql_magic.exceptions.EmptyResult at 0x8dc35b0>

Now here's the thing. I know that this query will get be several results because I queried it successfully in pgadmin. But I would love to have it working it in jupyter. I tried restarting the kernel and running the cells again, and I tried it on a difference database, and LIKE % wildcards just don't work.

Any idea how to resolve this?

[WakeMeAtThree]

Okay, so I tried it again using the ipython-sql jupyter magic (just to try verify if it might be an issue from my end), and the query works successfully. I'm trying to figure out how to resolve this, but I unfortunately I don't have the skills to go deep and fix any possible bug.

Also, I tested this query on an sqlite db using a different engine connection, and wildcards work.

It's just this postgres engine that doesn't seem to run wildcards successfully.

[crawles]

Having trouble reproducing this. Can you try below and see if it's working? Which API are you using? SQLAlchemy? or psycopg2?

%load_ext sql_magic
import pandas.io.sql as psql
import psycopg2
connect_credentials = {'database': 'postgres',
                       'host': 'localhost',
                       'password': '',
                       'user': 'postgres'}

# connect to postgres connection object
conn = psycopg2.connect(**connect_credentials)
conn.autocommit = True
schema_name = 'template'
psql.execute('SET search_path TO {}'.format(schema_name), conn)
%config SQL.conn_name='conn'

%%read_sql -n
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table (
  column1 INT,
  column2 text
);

INSERT INTO my_table 
VALUES  (1,'hello');

SELECT *
FROM my_table
WHERE column2 like '%ello';

returns:

column1 column2 1 hello

[WakeMeAtThree]

Hi @crawles, I'm using sqlalchemy as recommended by the main sql_magic repo page here. This is my current set up:

#Libraries used
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
from pylab import *

#Initialize sqlalchemy engine
postgres_engine = create_engine('postgresql://user:pass@host:port/database')

#Jupyter magics used
%load_ext sql_magic
%config SQL.conn_name = 'postgres_engine'
%matplotlib inline

Querying anything works fine, except for using WHERE LIKE 'W%', which returns an <sql_magic.exceptions.EmptyResult at 0x8dc35b0>. I will be trying your approach now and will let you know what happens.

[WakeMeAtThree]

@crawles , your approach works well, thank you good sir. Do you think the main repo page should mention this as a default approach rather than sqlalchemy? Were you able to reproduce the problem with sqlalchemy? Also, I just had to include port in the credentials dictionary, as the default is set to 5432.

connect_credentials = {'database': 'postgres',
                       'host': 'localhost',
                       'password': '',
                       'port': '',
                       'user': 'postgres'}

[crawles]

@WakeMeAtThree glad to hear it! Will try to reproduce it.

[WakeMeAtThree]

@crawles I don't have any issues, but I'd like to help out if needed. Were you able to reproduce the problem? What do I need to do (or know) to help fix it?

[maurice1408]

I hit the same issue, I think you will find your wildcard will work with SQLAlchemy if you enter %% rather than just %

so WHERE p.package_type like 'LOAD%%' rather than WHERE p.package_type like 'LOAD%'

[WakeMeAtThree]

Nice observation, @maurice1408! I just recreated this in my original db while using sqlalchemy, and 'Wal%%' does seem to work. But I prefer to use psycopg2 , I don't want to pick up a bad habit of writing double %% in queries. Thanks for mentioning this.

[AlexFiliakov]

@crawles thanks for the psycopg2 approach, the double-% was driving me crazy.

A couple minor points for anybody using this approach:

• don't need to SET search_path if you're working across multiple schema
• add 'port' to the dictionary if yours is not the default 5432
• prefer 'dbname' instead of 'database' according to this: http://initd.org/psycopg/docs/module.html

  The basic connection parameters are:

  • dbname – the database name (database is a deprecated alias)