sql - Using python psychopg2 to get a count on all variables in the database that contain a specific string -
i have list, words = [word1, word2, word3, ...] want use sql return number of times each word appears in column of sql file. can't figure out how pass variable sql query. appreciated! code far looks like:
import psycopg2 sql word in words conn = sql.connect(**params) c = conn.cursor() #create query , parameters usernames , ids query = """ select count(column a) file column similar '% **variable word** %' limit 1000; """ try: c.execute(query) except: conn.commit() print("error in query") result = c.fetchall()
also, count return total number of times word appears or number of lines of column in appears? (will count of in "the team won game" return 1 or two?)
the replaceable parameter flag used psycopg2 "%s", , use plain "%" in query replaceable parameters need double (i.e., "%%"). code should like:
query = """select count(column_a) file column_a similar '%%%s%%' limit 1000;""" try: c.execute(query, word)
this should return number of lines in word appears, not total number of occurrences of word in lines.
your example has space in column name used; i've substituted underscore, if column name contains space, name should double-quoted in query.
Comments
Post a Comment