Python: 连接MySQL数据库

# more mysql.py 
#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "root", "", "wordpress", charset='utf8' )

# 使用cursor()方法获取操作游标
cursor = db.cursor()

# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()

print "Database version : %s " % data

# 关闭数据库连接
db.close()

测试数据库：

# more my1.py 
#!/usr/bin/python
# -*- coding: UTF-8 -*-

import pymysql
import logging
import string
import os
import codecs
import time

import sys
reload(sys)
sys.setdefaultencoding("utf8")

# 打开数据库连接
def connect_wxremit_db():
    return pymysql.connect(host='localhost',
                           port=3306,
                           user='root',
                           password='',
                           database='wordpress',
                           charset='utf8')
# 
#$sql = "SET NAMES utf8";
#$result=$db->query($sql);
#
#$sql = "select ID,post_title,post_status from wp_posts;";
#$result=$db->query($sql);

def query_posts():
    sql_str = ("SELECT ID,post_title,post_status FROM wp_posts;")

    con = connect_wxremit_db()
    cur = con.cursor()
    cur.execute(sql_str)
    rows = cur.fetchall()
    cur.execute('SET NAMES UTF8')

    cur.close()
    con.close()

#    assert len(rows) == 1, 'Fatal error: country_code does not exists!'
#    return rows[0][0]
    return rows

def update_posts(sql):
    sql_str = (sql)

    con = connect_wxremit_db()
    cur = con.cursor()
    cur.execute(sql_str)
    rows = cur.fetchall()
    con.commit()
    cur.close()
    con.close()

    return rows

def insert_file_rec(self, file_name, file_md5):
        con = mysql_api.connect_wxremit_db()
        cur = con.cursor()
        try:
            sql_str = ("INSERT INTO t_forward_file (Ffile_name, Ffile_md5)", 
                       + " VALUES ('%s', '%s')" % (file_name, file_md5))
            cur.execute(sql_str)
            con.commit()
        except:
            con.rollback()
            logging.exception('Insert operation error')
            raise
        finally:
            cur.close()
            con.close()


def update_refund_trans(self, remit_id):
        con = mysql_api.connect_wxremit_db()
        cur = con.cursor()
        try:
            sql_str = ("SELECT Fremit_id"
                       + " FROM t_wxrefund_trans"
                       + " WHERE Fremit_id='%s'" % remit_id
                       + " FOR UPDATE")

            cur.execute(sql_str)
            assert cur.rowcount == 1, 'Fatal error: The wx-refund record be deleted!'

            sql_str = ("UPDATE t_wxrefund_trans"
                        + " SET Fcheck_amount_flag=1"
                        + ", Fmodify_time=now()"
                        + " WHERE Fremit_id='%s'" % remit_id)
            cur.execute(sql_str)

            assert cur.rowcount == 1, 'The number of affected rows not equal to 1'
            con.commit()
        except:
            con.rollback()
            logging.exception('Update operation error')
            raise
        finally:
            cur.close()
            con.close()

print (query_posts()[0][1])

# 打开文件
fo = open("/root/py/hisdb.log", "r")
print "文件名为: ", fo.name

line = fo.read(30000)
#print "读取的字符串: %s" % (line)

# 关闭文件
fo.close()

msg = pymysql.escape_string(line)

# 格式化成2016-03-20 11:45:39形式
dt = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) 

msg =  "<font color=red>更新时间：" + dt +  "</font>\n </br>[bash]" + msg  +  "[/bash]"
sql =  "update wp_posts set post_content='" + msg + "' where ID=1;"
#sql =  "update wp_posts set post_content='windows' where ID=1;"

update_posts(sql)