python数据库模块MySQLdb

[root@foundation55 python]# yum install MySQL-python -y
[root@foundation55 python]# yum install mysql -y

[root@foundation55 python]# ipython
Python 2.7.5 (default, Feb 11 2014, 07:46:25)
Type "copyright", "credits" or "license" for more information.

IPython 0.13.1 -- An enhanced Interactive Python.
?         -> Introduction and overview of IPython's features.
%quickref -> Quick reference.
help      -> Python's own help system.
object?   -> Details about 'object', use 'object??' for extra details.

In [1]: import MySQLdb    #导入 python 的 mysqldb 模块
 

In [2]: conn=MySQLdb.connect(user='root',passwd='password',host='127.0.0.1',charset='utf8')
 
###建立一个mysql 连接对象,在输出中文时必须加上 charset='utf8',不然会出现中文乱码

In [3]: cur=conn.cursor()
###conn 对象只是一个到 mysql 的连接状态,需要通过游标来发送 sql指令,我们需要建立一个游标,并创建一个 cur 对象保存下来

In [4]: cur.execute('CREATE DATABASE testbase')
Out[4]: 1L
In [5]: cur.execute('USE testbase')
Out[5]: 0L


In [6]: conn.commit()   #提交事物才能插入数据,在 rhel6 上使用 mysql 不需要这步,rhel7 上不提交insert 失败



###创建testuser表
In [11]: cur.execute('CREATE TABLE testuser(name VARCHAR(10) NOT NULL,passwd VARCHAR(10))')  
Out[11]: 0L

In [16]: cur.execute("INSERT INTO testuser VALUE('user1','user1')")    #插入数据
Out[16]: 1L
In [17]: cur.execute("INSERT INTO testuser VALUE('user2','user2')")      
Out[17]: 1L
In [18]: cur.execute("INSERT INTO testuser VALUE('user3','user3')")      
Out[18]: 1L
In [33]: cur.execute("INSERT INTO testuser VALUE('user7','user8')")   
Out[33]: 1L
In [35]: cur.execute("INSERT INTO testuser VALUE('user9','user10')")    
Out[35]: 1L
In [36]: cur.execute("INSERT INTO testuser VALUE('user11','user112')")
Out[36]: 1L


In [37]: cur.execute("SELECT * FROM testuser WHERE name LIKE 'user%'")    
Out[37]: 7L


####
In [38]: for data in cur.fetchall():
             print '%s\t%s' %data
   ....:     
user1    user1
user2    user2
user3    user3
user4    user5
user7    user8
user9    user10
user11    user112


In [39]: cur.execute("SELECT * FROM testuser")
Out[39]: 7L

#打印一条数据信息
In [40]: cur.fetchone()
Out[40]: (u'user1', u'user1')

In [41]: cur.fetchone()
Out[41]: (u'user2', u'user2')

In [42]: cur.fetchone()
Out[42]: (u'user3', u'user3')

In [43]: cur.fetchone()
Out[43]: (u'user4', u'user5')

In [44]: cur.fetchone()
Out[44]: (u'user7', u'user8')

In [45]: cur.fetchone()
Out[45]: (u'user9', u'user10')

In [46]: cur.fetchone()
Out[46]: (u'user11', u'user112')


###默认指针是一次递进的,想回去需要通过 scroll 函数调整指针,第一个参数指移动的位置,第二个参数是在什么地方移动

In [49]: cur.scroll(0,'absolute') #指针还原

In [50]: cur.execute("SELECT * FROM testuser")       #select 比较特殊,python 不能直接打印数据表
Out[50]: 7L

In [51]: cur.scroll(1,'absolute')

In [53]: cur.fetchone()
Out[53]: (u'user1', u'user1')

In [54]: cur.scroll(2,'absolute')

In [55]: cur.fetchone()
Out[55]: (u'user3', u'user3')

In [56]: cur.scroll(3,'absolute')

In [57]: cur.scroll(4,'absolute')

In [58]: cur.fetchone()
Out[58]: (u'user7', u'user8')

In [59]: cur.execute("SELECT * FROM testuser")
Out[59]: 7L

###一次取几个值,指针也是会以此递进
In [61]: cur.fetchmany(5)
Out[61]:
((u'user1', u'user1'),
 (u'user2', u'user2'),
 (u'user3', u'user3'),
 (u'user4', u'user5'),
 (u'user7', u'user8'))

In [62]: cur.fetchmany(7)
Out[62]: ((u'user9', u'user10'), (u'user11', u'user112'))

In [63]: cur.fetchmany(7)
Out[63]: ()

In [64]: cur.scroll(4,'absolute')

In [65]: cur.fetchmany(7)
Out[65]: ((u'user7', u'user8'), (u'user9', u'user10'), (u'user11', u'user112'))


#############################使用 executemany 函数一次插入多个值######################
In [78]: cur.execute("CREATE TABLE testuser1(name VARCHAR(10),passwd VARCHAR(10))")
Out[78]: 0L

In [89]: sql = "INSERT INTO testuser1(name,passwd) VALUE(%s,%s)" #为了方便定义字符串变量 %s 是字符串的格式化方法

In [90]: cur.executemany(sql,[('lll','nine'),('www','eight')])   #通过 execute 函数发送 sql 指令,后面的值是个元组,我们可以通过交互式获取元组的值
Out[90]: 2L

In [91]: cur.executemany(sql,[('lll','nine'),('www','eight'),('eee','seven')]) #使用 executemany 函数一次插入多个值,以列表方式
Out[91]: 3L

###删除数据
In [95]: cur.execute('DELETE FROM testuser1 WHERE name="lll"')   
Out[95]: 2L

###删除testuaer表
In [96]: cur.execute("DROP TABLE testuser")
Out[96]: 0L


###关闭游标
Out[97]: cur.close()

In [6]: conn.commit()   

###关闭连接
Out[98]: conn.close()




mysql打印输出:
#!/usr/bin/python
import MySQLdb
conn = MySQLdb.connect(host='localhost',user='root',passwd='password',db='testbase',charset='utf8')
cur = conn.cursor()
result = cur.fetchmany(cur.execute('select * from testuser'))

for rec in result:

     print rec[0], rec[1], rec[2]

cur.close()
conn.close()