import MySQLdb
import re

# SQL 查询语句
# sql = "Select * From MenuInfo"
def mySQL_Query (sql):
    # 打开数据库连接
    #db = MySQLdb.connect("localhost", "root", "12qwaszxQW!@", "DTAIDB")
    db = MySQLdb.connect("localhost", "root", "root", "DTAIDB")
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
    except:
        # results=[]
        print("Error: unable to fecth data")
    # 关闭数据库连接
    db.close()
    return results


# SQL插入\更新和删除语句
# sql = "Select * From MenuInfo"
def mySQL_Execute (sql):
    # 打开数据库连接
    #db = MySQLdb.connect("localhost", "root", "12qwaszxQW!@", "DTAIDB")
    db = MySQLdb.connect("localhost", "root", "root", "DTAIDB")
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    i = 0
    err=""
    try:
        # 执行sql语句
        i = cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except Exception as e:
        err = e
        # 发生错误时回滚
        db.rollback()
    # 关闭数据库连接
    db.close()
    return i,err


def cleanText(txt):
    cleantxt = re.sub(r'[!@#$%^&*()\']', '', txt)
    return cleantxt