# 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)