title: SQL Clips Example
tags: python sql example

This needs
```
python -m pip install mysql-connector-python
```

## clip_db_setup
```py
#!/usr/bin/env python
import sys
import os
import mysql.connector
from icecream import ic; ic.configureOutput(includeContext=True)
from mysql.connector.errors import DatabaseError
HOST=os.getenv("CLIPHOST",os.getenv("HOST","default_host"))
USER=os.getenv("CLIPUSER","mrbobbins")
PASS=os.getenv("CLIPPASS","mrbobbins")
DB=os.getenv("CLIPDB","mrbobbins")

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

sql1 = """CREATE TABLE IF NOT EXISTS clip (
    id INTEGER AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(256) UNIQUE, 
    value TEXT, 
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX clipindex (name), 
    fulltext INDEX clipcontent (value));"""
sql2 = """CREATE TABLE IF NOT EXISTS history (
    id INTEGER AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(256), 
    value TEXT, 
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX historyindex (name));"""
cursor = mydb.cursor()
cursor.execute(sql1)
cursor.execute(sql2)
mydb.commit()

"""insert into clip (name,value) values ("boing","random fluffball") on duplicate key update name=VALUES(name), value=VALUES(value);
insert into history (name,value) values ("boing","random fluffball") on duplicate key update name=VALUES(name), value=VALUES(value);
"""
```

## cput
```py
#!/usr/bin/env python
import sys
import os
import mysql.connector
from icecream import ic; ic.configureOutput(includeContext=True)
from mysql.connector.errors import DatabaseError
HOST=os.getenv("CLIPHOST",os.getenv("HOST","default_host"))
USER=os.getenv("CLIPUSER","mrbobbins")
PASS=os.getenv("CLIPPASS","mrbobbins")
DB=os.getenv("CLIPDB","mrbobbins")

args = sys.argv[1:]
if len(args) == 0:
  print(f"{sys.argv[0]} <name> [content filenames]")
  print(f"filename - reads from stdin")
  exit(1)
name, *args = args
if len(args) == 0:
  args = ["-"]

stdin = None
contents = []
for arg in args:
  if arg == "-":
    if stdin is None:
      stdin = sys.stdin.read()
    contents.append(stdin)
  else:
    try:
      with open(arg) as f:
        contents.append(f.read())
    except Exception:
      print(f"#Fail {arg}")
      exit(1)

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

sql1 = """insert into clip (name,value) VALUES ( %s, %s ) ON DUPLICATE KEY UPDATE name=VALUES(name), value=VALUES(value);"""
sql2 = """insert into history (name,value) VALUES ( %s, %s ) ON DUPLICATE KEY UPDATE name=VALUES(name), value=VALUES(value); """
sql = """insert into clip (name,value) values ( %s, %s ) on duplicate key update name=VALUES(name), value=VALUES(value);
insert into history (name,value) values ( %s, %s ) on duplicate key update name=VALUES(name), value=VALUES(value);
"""
val = (name,content)
cursor = mydb.cursor()
cursor.execute(sql1,val)
cursor.execute(sql2,val)
print(f"Added {name}")
mydb.commit()

"""insert into clip (name,value) values ("boing","random fluffball") on duplicate key update name=VALUES(name), value=VALUES(value);
insert into history (name,value) values ("boing","random fluffball") on duplicate key update name=VALUES(name), value=VALUES(value);
"""
```

## cget
```py
#!/usr/bin/env python
import sys
import os
import mysql.connector
from icecream import ic; ic.configureOutput(includeContext=True)
from mysql.connector.errors import DatabaseError
HOST=os.getenv("CLIPHOST",os.getenv("HOST","default_host"))
USER=os.getenv("CLIPUSER","mrbobbins")
PASS=os.getenv("CLIPPASS","mrbobbins")
DB=os.getenv("CLIPDB","mrbobbins")

args = sys.argv[1:]
if len(args) == 0:
  print(f"{sys.argv[0]} <name> [<name>...]")
  exit(1)

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

sql = "select value from clip where name = %s;"

cursor = mydb.cursor()
error = False
for arg in args:
  cursor.execute(sql,(arg,))
  a = cursor.fetchall()
  if len(a) > 0:
    b = a[0][0]
    if b[-1] != "\n":
      print(b)
    else:
      print(b,end="")
  else:
    error = True

exit(1 if error else 0)
```

## clist
```py
#!/usr/bin/env python
import sys
import os
import mysql.connector
import re
from icecream import ic; ic.configureOutput(includeContext=True)
from mysql.connector.errors import DatabaseError
from colors import color
if not sys.stdout.isatty():
  color = lambda x,*xs,**kw: x

HOST=os.getenv("CLIPHOST",os.getenv("HOST","default_host"))
USER=os.getenv("CLIPUSER","mrbobbins")
PASS=os.getenv("CLIPPASS","mrbobbins")
DB=os.getenv("CLIPDB","mrbobbins")

args = sys.argv[1:]
if len(args) == 0:
  args = [""]
  
try:
  mydb = mysql.connector.connect(host=HOST,user=USER,password=PASS,database=DB)
except DatabaseError as e:
  print(f"DatabaseError(connect): {e}")
  exit(1)

sql = "select name, time from clip;"

cursor = mydb.cursor()
error = False
found = {}
for arg in args:
  ignorecase = False
  if m := re.match(r"(/|@)(.*)\1(i)?",arg):
    ignorecase = m.group(2) == "i" 
  if ignorecase:
    r = re.compile(arg,re.I)
  else:
    r = re.compile(arg)

  cursor.execute(sql)
  a = cursor.fetchall()
  for x in a:
    y = x[0]
    t = x[1]
    if r.search(y):
      found[y] = t
l = 0
for k in sorted(found.keys()):
  l = max(l,len(k))
for k in sorted(found.keys()):
  t = found[k]
  m = l-len(k)
  p = " "*m
  kc = color(k,fg="#ffff77")+p
  tc = color(t,fg="#aaaacc")
  print(f"{kc} -- {tc}")
```