python操作mysql增删查改

# coding=utf-8

'''
python操作mysql,需安装MySQLdb驱动
安装MySQLdb，请访问 http://sourceforge.net/projects/mysql-python ，
(Linux平台可以访问：https://pypi.python.org/pypi/MySQL-python)从这里可选择适合您的平台的安装包，
分为预编译的二进制文件和源代码安装包。
'''

'''
命令行操作

1、登陆
本地：mysql -u root -p
远程：mysql -h0.0.0.0 -uxxx -pxxx

2、查看数据库
show databases;

3、链接数据库
use db_name;

4 查看数据表
show tables;

5 当前选择的数据库：
select database();

6 查询mysql版本：
select version();

7 查看表结构：describe <表名>;

8 当前所有连接的详细情况：
show full processlist;(没有full，只显示前100个连接)

'''


import MySQLdb

def get_connection():
    conn = MySQLdb.connect("127.0.0.1", "root", "root@123", "test")
    return conn


def select_data():
    conn = get_connection()
    cursor = conn.cursor()
    sql = "SELECT * FROM users"
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
    except:
        print "Error"
        result = False
    finally:
        conn.close()
        return result


def insert_data():
    conn = get_connection()
    cursor = conn.cursor()
    sql = "INSERT INTO users (username, password) VALUES ('kevin', 'k')"
    try:
        cursor.execute(sql)
        conn.commit()
        return True
    except:
        conn.rollback()
        return False
    finally:
        conn.close()


def update_data():
    conn = get_connection()
    cursor = conn.cursor()
    sql = "UPDATE users SET password='t' WHERE username='kevin'"
    try:
        cursor.execute(sql)
        conn.commit()
        result = True
    except:
        conn.rollback()
        result = False
    finally:
        conn.close()
        return result


def delete_data():
    conn = get_connection()
    cursor = conn.cursor()
    sql = "DELETE FROM users WHERE username='kevin'"
    try:
        cursor.execute(sql)
        conn.commit()
        result = True
    except:
        conn.rollback()
        result = False
    finally:
        conn.close()
        return result



if __name__ == "__main__":
    dev = 3
    if dev == 0:
        select_data =  select_data()
        print select_data
    elif dev == 1:
        insert_data = insert_data()
        print insert_data
    elif dev == 2:
        update_data = update_data()
        print update_data
    elif dev == 3:
        delete_data = delete_data()
        print delete_data