Dup Ver Goto 📝

MySql (or MariaDB) in Python 1

To
83 lines, 213 words, 1812 chars Page 'Mysql_01' does not exist.

Mysql or more likely the compatible fork mariadb.

Connect and Query 1

import mysql.connector
from mysql.connector.errors import DatabaseError

try:
  mydb = mysql.connector.connect(host=HOST,user=USER,password=PASS,database=DB)
except DatabaseError as e:
  eprint(f"DatabaseError(connect): {e}",)
  exit(1)

sql = """SELECT...
  FROM table...
"""

cursor = mydb.cursor()
cursor.execute("SELECT * FROM table WHERE boing = %s AND snarf = %s ",(param,param2))
a = cursor.fetchall()
if len(a) > 0:
    # found something
else:
    # found nothing

Parameters

a = "hello"
b = "world"
cur.execute("INSERT INTO mytable ( a, b ) VALUES ( %s, %s )",(a,b))

Worked Example

Find videos of a given duration.

import sys
import mysql.connector
from mysql.connector.errors import DatabaseError
import json
import argparse
from jda_time import hms

def main():
  global mydb

  try:
    mydb = mysql.connector.connect(host="localhost",user="me",password="turnip",database="movies")
  except DatabaseError as e:
    print(f"DatabaseError(connect): {e}",file=sys.stderr)
    exit(1)

  parser = argparse.ArgumentParser(
    prog='within',
    description='find movies with given duration')
  parser.add_argument('rest',nargs="*")
  ns = parser.parse_args()
  rest = ns.rest
  try:
    if len(rest) == 1:
      shortest = hms(rest[0])
      longest = 24*3600
    elif len(rest) == 2:
      shortest, longest = [ hms(x) for x in rest ]
    else:
      raise ValueError()
  except ValueError as e:
    print(f"Invalid arguments")

  cur = mydb.cursor()
  cur.execute("""
    SELECT path, duration_hms FROM movies
    WHERE duration >= %s AND duration <= %s
    ORDER BY duration
    """,(shortest,longest))
  for row in cur.fetchall():
    print(row[0],row[1])

if __name__ == "__main__":
  main()