Dong-gyun Kim
Dong-gyun Kim

Reputation: 431

postgresql matching with substring

Using python pstcopg2

I want to make a data frame like

df = sql.read_sql("""SELECT objects.sampleid, MAX(objects.projid) AS projid, 
                            MAX(samples.t01) AS hdrfilename, MAX(samples.t06) AS ctdfilename, 
                            MAX(objects.objdate) || ' ' || MAX(objects.objtime) AS objdatetime, 
                            MAX(objects.latitude) AS latitude, MAX(objects.longitude) AS longitude, 
                            MAX(objects.orig_id) AS orig_id 
        FROM samples, objects 
        WHERE objects.projid in (%s) and objects.orig_id LIKE '%samples.orig_id%'
        GROUP BY objects.sampleid"""%(projid_list), mydb)

But an error message occurred

    GROUP BY objects.sampleid"""%(projid_list), mydb) 
TypeError: not enough arguments for format string 

I guess the main problem came from this line,

objects.orig_id LIKE '%samples.orig_id%'

If I apply the code with

objects.orig_id = samples.orig_id

It works, but that's not exactly what I want. I want if the objects.orig_id contains samples.orig_id, I get the data.

How to correct the above code??

Upvotes: 0

Views: 228

Answers (1)

Mayank Porwal
Mayank Porwal

Reputation: 34086

You can escape the % by %%.

Something like this should work:

LIKE '%%samples.orig_id%%'

Upvotes: 2

Related Questions