python管理mysql

#
import MySQLdb
#创建数据库的连接
conn = MySQLdb.connect(host='localhost',user='root',passwd='123456')
#打印列名
cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)

cursor = conn.cursor()
cursor.execute('show databases')
cursor.execute('create database min')
#要执行的sql语句
use = 'use min'
cursor.execute(use)
#建表语句
create_tab = "create table song(id int(11) not null primary key auto_increment,name char(5) not null,sex char(2) not null,age int(3) not null)"
cursor.execute(create_tab)
li = [
    ('cb','w',11),
    ('mb','m',9),
]
sql = 'insert into song values(%s,%s,%s)'
cur.executemany(sql,li)
param = [['zb','m',17],['bb','m',18]]
param1 = (('tb','w',17,('ab','m',19))
#执行多条数据，利用元组或列表
cursor.executemany(sql,param)
cursor.executemany(sql,param1)
#提交操作
conn.commit()
#从数据库表中读取列名和对应的值以字典形式。
cur.fetchall()
#从数据库表中读取一条
cur.fetchone()
#绝对定位
cur.scroll(0,mode='absolute')
#相对定位
cur.scroll(-1,mode='relative')
#关闭MySQLdb.cursors.DictCursor实例
cur.close()
#关闭数据库链接
conn.close()

config.py

#!/usr/bin/env python
#-*- coding:utf-8 -*-
'''
Created on 2018??5??25??

@author: SongHyun
'''
conn_dict = dict(host='127.0.0.1',user='root',db='min')
#如果以元组形式传参，内容必须按照顺序添加host、user、password、db、port等，参照MySQLdb.connects
conn_tuple = ('127.0.0.1','root','123456','min')

sql_command.py

#!/usr/bin/env python
#-*- coding:utf-8 -*-
'''
Created on 2018年5月24日

@author: SongHyun
'''

import MySQLdb
import config

class Sql_Command(object):
    def __init__(self):
#定义私有变量
        self.__conn_dict = config.conn_dict
        self.__conn_tuple = config.conn_tuple

    def Get_All(self,sql,params):
        conn = MySQLdb.connect(**self.__conn_dict)
#       conn1 = MySQLdb.connect(*self.__conn_tuple)

        cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)

#局部变量，不能被调用
        echo = cur.execute(sql,params)
        data = cur.fetchall()

        cur.close()
        conn.close()
        return data

    def Get_One(self,sql,params):
        conn = MySQLdb.connect(**self.__conn_dict)
#       conn1 = MySQLdb.connect(*self.__conn_tuple)
        cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)

#self.echo.为类的变量可以被调用
        self.echo = cur.execute(sql,params)
        data = cur.fetchone()

        cur.close()
        conn.close()
        return data


command = Sql_Command()
sql = "select * from song where id > %s"
params = (1,)

one_data = command.Get_One(sql,params)
all_data = command.Get_All(sql,params)
print command.echo
print one_data
print all_data