# cPy - General procudure to connect to MariaDB
# provided by Anthony Vogelaar, Perth Scotland
# rev 0.10  2024-08-05  initial release

import  os;     os.system('clear')
import  pymysql as db                       # (venv) pip install PyMySql


# conn_str = dict(host='192.168.118.181', user='me', password='pwd', database='my_db')


# table description
def test1():
    conn = db.connect(host='192.168.118.181', user='me', password='pwd', database='my_db')
    # conn = db.connect(**conn_str)
    cur  = conn.cursor()
    cur.execute('SELECT * FROM tbl1')
    n_rows = cur.rowcount
    descr  = cur.description
    conn.close()

    print('curser.rowcount   :', n_rows)
    print('curser.description:', descr)


# table Query
def test2():
    conn = db.connect(**conn_str)
    cur  = conn.cursor()
    cur.execute('SELECT * FROM tbl1')
    data = cur.fetchall()
    conn.close()

    for row in data:
        print(row)


# table Insert
def test3():
    conn = db.connect(**conn_str)
    cur  = conn.cursor()
    try:
        cur.execute("INSERT INTO tbl1 VALUES (3, 'Klaas', 'Soest')")
        cur.execute("INSERT INTO tbl1 VALUES (4, 'Peter', 'Zeist')")
        conn.commit()
    except:
        conn.rollback()
        print('conn.rollback')
    conn.close()


# table Update
def test4():
    conn = db.connect(**conn_str)
    cur  = conn.cursor()
    try:
        cur.execute("UPDATE tbl1 SET plaats = 'Hilversum' WHERE id = 4")
        conn.commit()
    except:
        conn.rollback()
        print('conn.rollback')
    conn.close()


# table Delete
def test5():
    conn = db.connect(**conn_str)
    cur  = conn.cursor()
    try:
        cur.execute("DELETE FROM tbl1 WHERE id >= 3")
        conn.commit()
    except:
        conn.rollback()
        print('conn.rollback')
    conn.close()


# table Insert parameters
def test6():
    conn = db.connect(**conn_str)
    cur  = conn.cursor()
    sql = 'INSERT INTO tbl1 VALUES (%s, %s, %s)'
    try:
        cur.execute(sql, (3, 'Aart', 'Huizen'))
        cur.execute(sql, (4, 'Charles', 'Laren'))
        conn.commit()
    except:
        conn.rollback()
        print('conn.rollback')
    conn.close()


# table Insert prepared (n/a)
def test7():
    conn = db.connect(**conn_str)
    cur  = conn.cursor()
    sql = 'INSERT INTO tbl1 VALUES (%s, %s, %s)'
    try:
        cur.executemany(sql, [(5, 'Steven', 'Weesp'), (6, 'James', 'Blaricum')])
        conn.commit()
    except:
        conn.rollback()
        print('conn.rollback')
    conn.close()


test1()
# test2()     # Query / Show table
