python操作mysql总结

Windows系统，python环境搭建。

    下载并安装python2.7.11 https://www.python.org/downloads/

    下载并安装python的mysql包：

       http://www.codegood.com/downloads

       http://sourceforge.net/projects/mysql-python/?source=typ_redirect

    注意：

       如果你使用的是python的64为版本，需要下载对应的64为版本的mysql包。

　　 MySQL-python-1.2.3.win-amd64-py2.7.exe 

　　如果你使用的是python的32为版本，需要下载对应的32为版本的mysql包。　　

      MySQL-python-1.2.3.win32-py2.7.exe

　　 或

　　 http://sourceforge.net/projects/mysql-python/?source=typ_redirect

否则，在执行导入语句

import MySQLdb

出现如下错误

import _mysql

ImportError DLL load failed: %1 不是有效的 Win32 应用程序

安装mysql-python包时，若出现错误

提示“Python 2.7 is required, which was not found in the registry”

请将如下代码保存为register.py，

#
# script to register Python 2.0 or later for use with win32all
# and other extensions that require Python registry settings
#
# written by Joakim Loew for Secret Labs AB / PythonWare
#
# source:
# http://www.pythonware.com/products/works/articles/regpy20.htm
#
# modified by Valentine Gogichashvili as described in http://www.mail-archive.com/distutils-sig@python.org/msg10512.html
 
import sys
 
from _winreg import *
 
# tweak as necessary
version = sys.version[:3]
installpath = sys.prefix
 
regpath = "SOFTWARE\\Python\\Pythoncore\\%s\\" % (version)
installkey = "InstallPath"
pythonkey = "PythonPath"
pythonpath = "%s;%s\\Lib\\;%s\\DLLs\\" % (
    installpath, installpath, installpath
)
 
def RegisterPy():
    try:
        reg = OpenKey(HKEY_CURRENT_USER, regpath)
    except EnvironmentError as e:
        try:
            reg = CreateKey(HKEY_CURRENT_USER, regpath)
            SetValue(reg, installkey, REG_SZ, installpath)
            SetValue(reg, pythonkey, REG_SZ, pythonpath)
            CloseKey(reg)
        except:
            print "*** Unable to register!"
            return
        print "--- Python", version, "is now registered!"
        return
    if (QueryValue(reg, installkey) == installpath and
        QueryValue(reg, pythonkey) == pythonpath):
        CloseKey(reg)
        print "=== Python", version, "is already registered!"
        return
    CloseKey(reg)
    print "*** Unable to register!"
    print "*** You probably have another Python installation!"
 
if __name__ == "__main__":
    RegisterPy()

　　

执行完毕，显示"python 2.7 is already registered"，

表示成功，再次安装mysql-python即可。

 

安装完毕以后，让我们愉快的和mysql-python玩耍吧。

建一张测试用户表

 

-- ----------------------------
-- Table structure for `t_user`
-- ----------------------------
USE pydb;
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_user
-- ----------------------------

 

 

基本的sql操作 

import MySQLdb
   
conn = MySQLdb.Connect(
                       host="127.0.0.1",
                       port = 3306,
                       user = "user",
                       passwd="123456",
                       db="pydb",
                       charset='utf8')
print conn

cursor = conn.cursor()
print cursor


try:
    sql = "insert into t_user(name,age) values('wyf001', 28)"
    cursor.execute(sql)
    conn.commit()
except Exception as e:
    conn.rollback()

try:
    sql = "update t_user set age=29 where name = 'wyf001'"
    cursor.execute(sql)
    conn.commit()
except Exception as e:
    conn.rollback()

sql = "select id,name,age from t_user"
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
    print row
    
try:
    sql = "delete from t_user where name = 'wyf001'"
    cursor.execute(sql)
    conn.commit()
except Exception as e:
    conn.rollback()

执行结果：

参考：

http://www.cnblogs.com/min0208/archive/2012/05/24/2515584.html