SQL: INSERT - SELECT - WHERE LIKE

 SQL: INSERT - SELECT - WHERE LIKE

After inserting a row make sure to issue a .commit() command to get the row officially in the database. The  .commit() is done on the variable returned from the connector call, not the cursor object.

Use .executemany() to concurrently insert 2 or more rows. The rows must be set up as tuples and the tuples are then in a list. It is OK to mix single and double quotes to mark the values in each row.

Either .execute() or .executemany() contain the actual SQL commands that must be in single or double quotes.

import mysql.connector

mydatab = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

mycursor = mydatab.cursor()

sql = 'INSERT INTO players (name, age) VALUES (%s, %s)'

val = [

  ("Peter", "25"),

  ('Sam', '31'),

  ('Josie', "28")

  ]

# It is Ok to mix and match single and double quote in the same call

mycursor.executemany(sql, val)

mydatab.commit()

-------------------------------

SELECT * for all rows

SELECT column names separated by commas to only return those columns, but still gets all rows.

Either way, you still need a .fetchall() and a for loop to view all matches.

mysqlcursor.execute("SELECT name, address FROM players")

mysqlresult = mysqlcursor.fetchall()

for x in mysqlresult:
  print(x)

--------------------------------

WHERE is used for searching.

WHERE column_name=value

or

WHERE column_name LIKE '%value%' 

with % as wildcard.

To prevent SQL injections, use a %s as a placeholder for the end-user input value to be passed in via a variable. This adds an extra step to capture the end-user input as a string.

sql = "SELECT * FROM players WHERE age = %s"
age = ("31")

with age containing the user input at a prompt.

----------------------------------

Here is a really clever example that permits an end-user to provide an ad hoc search query with an arbitrary keyword wildcarded. However, it is not clear if this construct is vulnerable to SQL injection.

import mysql.connector

mydatab = mysql.connector.connect(

  host="localhost",

   user="your username",

  password="fill in",

  database="mydatabase"

)

# search query phrase

user_input = "Jo"

mycursor = mydatab.cursor()

name_stem = '\'%' + user_input + '%\''

sql = "SELECT * FROM players WHERE name LIKE " + name_stem

print(sql)

mycursor.execute(sql)

mysqlresult = mycursor.fetchall()

for x in mysqlresult:

    print(x)

  

This will find all players with Jo somewhere in their name.

To help combat SQL injection, the following version of the same code is preferred.

import mysql.connector

mydatab = mysql.connector.connect(

  host="localhost",

  user="your username",

  password="fill in",

  database="mydatabase"

)

user_input = "Jo" 

# captured from a GUI or command line prompt.

mycursor = mydatab.cursor()

name_stem = ("%" + str(user_input) + "%")

 # need pair of % to wildcard

print(name_stem)

print(type(name_stem))

sql = "SELECT * FROM customers WHERE name LIKE %s"

# parameter substitution

print(sql)

nm = (name_stem,) 

print(nm)

print(type(nm))

# for some strange reason, it needs to a tuple with an empty trailing comma!!??

mycursor.execute(sql, nm) # nm goes in for %s

mysqlresult = mycursor.fetchall()

for x in mysqlresult:

    print(x) # matches John Josie Joseph Jo