点击(此处)折叠或打开
- #
-
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()
-
点击(此处)折叠或打开
-
#!/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
-