Python操作Mysql

来源：http://www.runoob.com/python3/python3-mysql.html

安装PyMySql

pip3 install PyMySQL

连接Testdb数据库：

#!/usr/bin/python3
 
import pymysql
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# 使用 execute()  方法执行 SQL 查询 
cursor.execute("SELECT VERSION()")
 
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
 
print ("Database version : %s " % data)
 
# 关闭数据库连接
db.close()

插入数据：

#!/usr/bin/python3
 
import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # 执行sql语句
   cursor.execute(sql)
   # 执行sql语句
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()
 
# 关闭数据库连接
db.close()

 查询数据：

#定义函数，在mysql中查询某个监控项前一天的值
Function QueryMySQL($table,$item)
    {
    Write-Host $item
    $sqlcmd = "select value from $table where item = ""$item"" and date(updatetime) = date_sub(curdate(),interval 1 day);"
    $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($sqlcmd,$connection)
    $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd)
    $dataSet = New-Object System.Data.DataSet
    $recordCount = $dataAdapter.Fill($dataSet)
    $table = $dataSet.Tables[0]

    return $table.Rows.value
    }


$item =  "DHCP_bjdf"
$talbe = 'windows'
QueryMySQL $talbe $item