python3的MySQLdb使用方法

使用模块

>>> import MySQLdb

>>> conn = MySQLdb.connect(host="localhost",user="root",passwd="12345678",db="pythontest",charset="utf8")

>>> cur = conn.cursor()

 

插入数据

>>> cur.execute("create table users(id int(2) not null primary key auto_increment,username varchar(40),password text,email text)default charset=utf8;")

>>> cur.execute("insert into users(username,password,email) values('qiwsir','123123','qiwsir@gmail.com')")

>>> conn.commit()

>>> cur.execute("insert into users (username,password,email) values (%s,%s,%s)",("python","123456","python@gmail.com"))

>>> conn.commit()

>>> cur.executemany("insert into users (username,password,email) values (%s,%s,%s)",(("google","111222","g@gmail.com"),("facebook","222333","f@face.book"),("github","333444","git@hub.com"),("docker","444555","doc@ker.com")))

>>> conn.commit()

 

逐条输出

>>> cur.execute("select * from users")

>>> lines = cur.fetchall()

>>> for line in lines:

...  print line

...

(1L, u'qiwsir', u'123123', u'qiwsir@gmail.com')

(2L, u'python', u'123456', u'python@gmail.com')

(3L, u'google', u'111222', u'g@gmail.com')

(4L, u'facebook', u'222333', u'f@face.book')

(5L, u'github', u'333444', u'git@hub.com')

(6L, u'docker', u'444555', u'doc@ker.com')

 

全部输出

>>> cur.execute("select * from users")

>>> lines = cur.fetchall()

>>> print lines;

((1L, u'qiwsir', u'123123', u'qiwsir@gmail.com'), (2L, u'python', u'123456', u'python@gmail.com'), (3L, u'google', u'111222', u'g@gmail.com'), (4L, u'facebook', u'222333', u'f@face.book'), (5L, u'github', u'333444', u'git@hub.com'), (6L, u'docker', u'444555', u'doc@ker.com'))

>>> cur.execute("select * from users where id=1")

 

输出一条

>>> cur.execute("select * from users")

>>> line_first = cur.fetchone()

>>> print line_first

(1L, u'qiwsir', u'123123', u'qiwsir@gmail.com')

深度研究：游标 >>> 当输出第7次时，游标对应的数据为空，所以输出一个空值

>>> cur.execute("select * from users")

>>> print cur.fetchone()

(1L, u'qiwsir', u'123123', u'qiwsir@gmail.com')

>>> print cur.fetchone()

(2L, u'python', u'123456', u'python@gmail.com')

>>> print cur.fetchone()

(3L, u'google', u'111222', u'g@gmail.com')

>>> print cur.fetchone()

(4L, u'facebook', u'222333', u'f@face.book')

>>> print cur.fetchone()

(5L, u'github', u'333444', u'git@hub.com')

>>> print cur.fetchone()

(6L, u'docker', u'444555', u'doc@ker.com')

>>> print cur.fetchone()

None

 

相对位置 （接上，游标从第7条开始，游标不变）

>>> cur.scroll(-1)

>>> print cur.fetchone()

(6L, u'docker', u'444555', u'doc@ker.com')

>>> cur.scroll(-2)

>>> print cur.fetchone()

(5L, u'github', u'333444', u'git@hub.com')

>>> cur.scroll(-5) 

>>> print cur.fetchone()

(1L, u'qiwsir', u'123123', u'qiwsir@gmail.com')

有疑问，如果执行前面两条，则执行第3条时返回第一条；如果不执行前面两条，则执行第3条时返回第二条：

>>> print cur.fetchone()

None  #游标在第7条

>>> cur.scroll(-4) #返回第4条

>>> print cur.fetchone()

(3L, u'google', u'111222', u'g@gmail.com')

或：

>>> print cur.fetchone()

None   #游标在第7条

>>> cur.scroll(-5)#返回第5条

>>> print cur.fetchone()

(2L, u'python', u'123456', u'python@gmail.com')

 

绝对位置

>>> cur.scroll(2,"absolute")

>>> print cur.fetchone()

(3L, u'google', u'111222', u'g@gmail.com')

 

当前位置后n条

>>> cur.execute("select * from users")

>>> print cur.fetchone()

(1L, u'qiwsir', u'123123', u'qiwsir@gmail.com')

>>> print cur.fetchone()

(2L, u'python', u'123456', u'python@gmail.com')

>>> print cur.fetchone()

(3L, u'google', u'111222', u'g@gmail.com')

>>> cur.fetchmany(3)

((4L, u'facebook', u'222333', u'f@face.book'), (5L, u'github', u'333444', u'git@hub.com'), (6L, u'docker', u'444555', u'doc@ker.com'))

 

截取某一列  >>> awk

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

>>> cur.execute("select * from users")

>>> cur.fetchall()

({'username': u'qiwsir', 'password': u'123123', 'id': 1L, 'email': u'qiwsir@gmail.com'}, {'username': u'python', 'password': u'123456', 'id': 2L, 'email': u'python@gmail.com'}, {'username': u'google', 'password': u'111222', 'id': 3L, 'email': u'g@gmail.com'}, {'username': u'facebook', 'password': u'222333', 'id': 4L, 'email': u'f@face.book'}, {'username': u'github', 'password': u'333444', 'id': 5L, 'email': u'git@hub.com'}, {'username': u'docker', 'password': u'444555', 'id': 6L, 'email': u'doc@ker.com'})

>>> cur.scroll(0,"absolute")

>>> for line in cur.fetchall():

...  print line["username"]

...

qiwsir

python

google

facebook

github

docker

 

关闭

>>> cur.close()

>>> conn.close()

 

 

补充：

名称	描述
close()	关闭游标。之后游标不可用
execute(query[,args])	执行一条SQL语句，可以带参数
executemany(query, pseq)	对序列pseq中的每个参数执行sql语句
fetchone()	返回一条查询结果
fetchall()	返回所有查询结果
fetchmany([size])	返回size条结果
nextset()	移动到下一个结果
scroll(value,mode='relative')	移动游标到指定行，如果mode='relative',则表示从当前所在行移动value条,如果mode='absolute',则表示从结果集的第一行移动value条.