python 串連mysql多層結構執行個體

標籤：python mysql

一、python 串連mysql多層結構：

       目錄檔案介紹：

           sqlexec             sql執行操作，例：增刪改查

           table                 資料庫表模組 一個表一個模組

           index.py            主檔案

           conf.py              資料庫連接資訊

       目錄結構如下：

-rw-r--r-- 1 root root     167 Jun 22 20:36 conf.py
-rw-r--r-- 1 root root     244 Jun 22 20:36 conf.pyc
-rw-r--r-- 1 root root     594 Jun 22 20:35 index.py
drwxr-xr-x 2 root root    4096 Jun 22 20:36 sqlexec
drwxr-xr-x 2 root root    4096 Jun 22 20:39 table

./sqlexec:
total 12
-rw-r--r-- 1 root root    0 Jun 22 18:48 __init__.py
-rw-r--r-- 1 root root  138 Jun 22 18:48 __init__.pyc
-rw-r--r-- 1 root root  911 Jun 22 20:21 sql_exec.py
-rw-r--r-- 1 root root 1690 Jun 22 20:21 sql_exec.pyc

./table:
total 12
-rw-r--r-- 1 root root    0 Jun 22 18:48 __init__.py
-rw-r--r-- 1 root root  130 Jun 22 18:48 __init__.pyc
-rw-r--r-- 1 root root 1246 Jun 22 19:57 users.py
-rw-r--r-- 1 root root 1782 Jun 22 19:57 users.pyc

二、具體指令碼內容：

1、index.py

#!/usr/bin/python27
#coding:utf-8

import os,sys
from table.users import users

def main():
    username = raw_input(‘username:‘)
    password = raw_input(‘password:‘)
    check = users()
    result = check.checkvalidate(username,password)
    if not result:
        print(‘使用者名稱密碼錯誤‘)
    else:
        print(‘歡迎登入後台管理系統‘)
        user_list = check.get_dict_user_info(0,‘‘)
        for key in user_list:
            for item in key.items():
                print(item)


if __name__ == ‘__main__‘:
    os.system(‘clear‘)
    main()

2、 conf.py

#!/usr/bin/python27
#coding=utf-8

conf_dict = {‘host‘:‘127.0.0.1‘,
             ‘user‘:‘root‘,
             ‘passwd‘:‘root‘,
             ‘db‘:‘yunwei‘
            }

3、table目錄下users.py

#!/usr/bin/python27
#coding:utf-8

import os,sys
sys.path.append("..")
from sqlexec.sql_exec import mysql_exec

class users(object):
    def __init__(self):
        self.__result = mysql_exec()

    def get_dict_user_info(self,flag,user):
        if flag == 1:
            sql = "select * from users where user_name=%s"
            res =  self.__result.get_dict(sql,user)
        elif flag == 0:
            sql = "select * from users"
            res = self.__result.get_dict(sql,‘‘)
        else:
            res = "the flag is error"
        return res


    def get_one_user_info(self,flag,user):
        if flag == 1:
            sql = "select * from users where user_name=%s"
            res =  self.__result.get_one(sql,user)
        elif flag == 0:
            sql = "select * from users"
            res = self.__result.get_one(sql,‘‘)
        else:
            res = "the flag is error"
        return res

    def checkvalidate(self,user,passwd):
        sql = "select user_name,password from users where user_name=%s and  password=md5(%s)"
        params = (user,passwd,)
        res = self.__result.get_one(sql,params)
        return res

4、sqlexec目錄下sql_exec.py

#!/usr/bin/python27
#coding:utf-8

import os,sys
import MySQLdb
sys.path.append(‘..‘)
import conf

class mysql_exec(object):
    def __init__(self):
        self.__conn = conf.conf_dict

    def __connect(self):
        try:
            conn = MySQLdb.connect(**self.__conn)
        except Exception,e:
            print(e)
            sys.exit(‘connect failed‘)

        cur = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
        return cur
        cur.close()
        conn.close()
        
    def get_dict(self,sql,params):
        res = self.__connect()
        if params != ‘‘:
            res.execute(sql,params)
        else:
            res.execute(sql)
        data = res.fetchall()
        return data

    def get_one(self,sql,params):
        res = self.__connect()
        if params != ‘‘:
            res.execute(sql,params)
        else:
            res.execute(sql)
        data = res.fetchone()
        return data

本文出自 “秋天的童話” 部落格，請務必保留此出處http://wushank.blog.51cto.com/3489095/1664211

python 串連mysql多層結構執行個體