python

操作MYSQL数据库，使用 MySQLdb 库

1、setuptools

下载:

wget http://pypi.python.org/packages/source/s/setuptools/setuptools-0.6c8.tar.gz --no-check-certificate

安装:

tar zxvf setuptools-0.6c8.tar.gz && cd setuptools-0.6c8 && python setup.py build && python setup.py install

 

2、python-dev

如果没有安装 python 源代码，会报找不到 Python.h 的头文件错误，安装方法：

yum install python-devel

 

 

3、mysql_config

apt-get install libmysqlclient-dev

 

4、MySQLdb

下载：

http://nchc.dl.sourceforge.net/project/mysql-python/mysql-python/1.2.3/MySQL-python-1.2.3.tar.gz

安装：

tar zxvf MySQL-python-1.2.3.tar.gz && cd MySQL-python-1.2.3 && python setup.py build && python setup.py install

 

 

 测试：

import time
import MySQLdb

try:
    conn=MySQLdb.connect(host='211.152.53.103',user='root',passwd='123456',port=3306)
    cur=conn.cursor()
    cur.execute('drop database if exists python')
    cur.execute('create database if not exists python')
    conn.select_db('python')
    cur.execute('create table test(id int,info varchar(20))')


    for i in range(100):
        value=[i,'test:'+str(i)]
        cur.execute('insert into test values(%s,%s)',value)
        conn.commit()
        time.sleep(0)

    sql ="select * from test";
    cur.execute(sql)
    data = cur.fetchall()
    if data:
        for item in data:
            print item[0],item[1]
    cur.close()
    conn.close()

except MySQLdb.Error,e:
     print "Mysql Error %d: %s" % (e.args[0], e.args[1])

 

import MySQLdb
import json

conn=MySQLdb.connect(host='54.225.106.132',user='root',passwd='tot91smart.net',port=3306)
cur=conn.cursor()
conn.select_db('bbb')

mymap = {}
cur.execute("select id,gold from c_property")
data0 = cur.fetchall()
if data0:
    for item0 in data0:
    #    print item0[0],"&",item0[1]
        mymap[item0[0]] = item0[1]

#for k,v in mymap.items():
#    print k,":",v

value = []
sql ="select id,gold,propertyList from p_user"
cur.execute(sql)
data = cur.fetchall()
if data:
    for item in data:
    #    print item[0],item[1],item[2]
        if item[2]:
            propertyJson = json.loads(item[2])
            for array in propertyJson:
                if array[0] == 0:
                    continue;
                else:
                    print "propertyid: ",array[0]," & num: ",array[1]," & price: ",mymap[array[0]]," & totol_gold: ",mymap[array[0]] *array[1]
                    add_gold = mymap[array[0]] *array[1]
                    userid = item[0]
                    mysql = "update p_user set gold = gold + " + str(add_gold) + ",propertylist = '' where id = " + str(userid)
                    value.append(mysql)

for item in value:
    cur.execute(item)
    conn.commit()
                
cur.close()
conn.close()

 

 

如果出现错误：SyntaxError: Non-ASCII character ‘\xe7′ in file，则可在文件第一行加上下面四个任意其中一个即可解决：

#coding:utf-8
# coding=utf-8
#-*- coding: utf-8 -*-
# vim: set fileencoding=utf-8 :