Python实现MySQL操作的方法小结【安装,连接,增删改查等】

>import MySQLdb

>conn =MySQLdb.connect(host = '127.0.0.1',user= 'root',passwd='123456',db='test',port=3306,charset='utf8')
>

>cur = conn.cursor()
>

>cur.execute("insert into msg (title,name,content) values ('python','zz','test mysql insert')")
1L #返回受影响的记录条数.
>conn.commit() #需要提交事务插入才会生效.
>

>sql = "insert into msg (title,name,content) values (%s,%s,%s)" #定义一个sql语句
>cur.executemany(sql,[('title01','name01','content01'),('title02','name02','content02')])
2L
>conn.commit()
>

>sql = "insert into msg (title,name,content) values"
>for i in range (100):
  > sql += "('id" + str(i) + "'," + "'name" + str(i) + "'," + "'content" + str(i) + "'),"
>sql = sql[:-1] #利用切片将最后的"," 删除.
>cur.execute(sql)
103L
>conn.commit()
>

>cur.execute("delete from msg where title = 'title02' ")
1L
>conn.commit()
>

>cur.execute("update msg set title='changedTitle' where title='title01'")
1L
>conn.commit()
>

>cur.execute("select * from msg")
3L #返回记录条数
>

>cur.fetchone()
(1L, 'title01', 'name01', 'content01')

>cur.fetchmany(size=5)
((2L, 'python', 'ChenYL', 'insert content'), (3L, 'py03', 'name03', 'content03'), (4L, 'py04', 'name04', 'content04'), (5L, 'id0', 'name0', 'content0'), (7L, 'id2', 'name2', 'content2'))
>

>cur.fetchall()
105L
>

>cur.scroll(1,mode="absolute")
>

>cur.close()
>conn.close