CODEWITHSUNDEEP
pythonsqlite
Voley Betz
Voley Betz

Reputation: 11

Like query in Python - SQLite

I have a database with 2 columns, with the name of the players written in this format:

Player1 Player2
Yang Y. Ying G.
Kim G. Uin Y.

I should find all the rows containing the name of the players but only with the surname, like Yang, without Y.

So I thought to use the LIKE query, like this:

for game in final_table:
  name = "Yang"
  cur1.execute("SELECT * FROM matches WHERE Player1 like ?", [name])
  match = cur1.fetchall()

print(match)

It gives me nothing in return. Instead if I write the entire name like this:

for game in final_table:
  name = "Yang Y."
  cur1.execute("SELECT * FROM matches WHERE Player1 like ?", [name])
  match = cur1.fetchall()

print(match)

It works properly. What am I doing wrong?

Upvotes: 0

Views: 807

Answers (2)

romanows
romanows

Reputation: 458

Take a look at the documentation for LIKE. The way you've written your query in both cases attempts to find exact matches against name. There will be no exact matches because all of the data in your SQLite database has spaces and additional text after that last name string.

Instead, you might want to write:

cur1.execute("SELECT * FROM matches WHERE Player1 like ? ", [f"{name} %"])

Notice the use of the "%" wildcard that'll match whatever initials follow the surname.

Upvotes: 1

Diego Torres Milano
Diego Torres Milano

Reputation: 69198

SQLite provides two wildcards for constructing patterns. They are percent sign % and underscore _ :

The percent sign % wildcard matches any sequence of zero or more characters. The underscore _ wildcard matches any single character.

for game in final_table:
  name = "Yang %"
  cur1.execute("SELECT * FROM matches WHERE Player1 like ?", [name])
  match = cur1.fetchall()

print(match)

Upvotes: 1

Related Questions