CODEWITHSUNDEEP
pythonpostgresql
user3628240
user3628240

Reputation: 927

PSQL - How to filter out if a column value equals a specific string

I created a database with column called option where the value is a varchar of either 'Call', 'Put', or None. When I set up the database I made the default value None if there was no Call or Put.

I'm trying to filter out the rows that contain 'Call' or 'Put' for option. However, when I write the query

SELECT * FROM holdings h WHERE h.option != 'Call' AND h.option != 'Put';

I get this error:
    curr.execute('SELECT option FROM holdings WHERE option != 'Call';')
                                                                  ^
SyntaxError: invalid syntax

When I try to use NOT LIKE '%Call%' I get this error:

curr.execute('SELECT option FROM holdings AS h WHERE option NOT LIKE '%Call%';')
NameError: name 'Call' is not defined

Any help would be appreciated

Thanks!

Code looks like this:

import psycopg2
import csv
conn = psycopg2.connect('dbname = db user = asmith')
curr = conn.cursor()
curr.execute('SELECT option FROM holdings h WHERE option NOT LIKE '%Call%';')
tups = curr.fetchall()
for tup in tups:
    print tup

Edit: When I run these two lines: 

curr.execute('SELECT * FROM holdings h WHERE option = \'Call\' OR option = \'Put\';') 
curr.execute('SELECT * FROM holdings h WHERE option != \'Call\' AND option != \'Put\';')

The first query gives me all the rows with a Call or Put, but when I run the second query I get nothing, even though it should theoretically give me all of the rows with option that are None. Does anyone know why that is?

Upvotes: 1

Views: 8957

Answers (2)

Razzka
Razzka

Reputation: 641

Looks like Syntax error here:

curr.execute('SELECT option FROM holdings h WHERE option NOT LIKE '%Call%';')

String 1 is 'SELECT option FROM holdings h WHERE option NOT LIKE ', breaks with ' (before %Call%), then some variable named Call (which Your compiler cannot find) and then String 2 ';'

You have to escape ' with backslash like this:

curr.execute('SELECT option FROM holdings AS h WHERE option NOT LIKE \'%Call%\';')

Upvotes: 3

Hasan Alizada
Hasan Alizada

Reputation: 591

There is logic error in statement. You should write this instead:

SELECT * FROM holdings h WHERE h.option != 'Call' OR h.option != 'Put';

or this:

SELECT * FROM holdings h WHERE h.option NOT IN ( 'Call' , 'Put');

Thanks.

Upvotes: 0

Related Questions