官网地址: http://mysql-python.sourceforge.net/ MySQLdb User's Guide http://mysql-python.sourceforge.net/MySQLdb.html
使用:
导入python-MySQLdb 模块
import MySQLdb #导入python-MySQLdb 模块 conn = MySQLdb.connect( #创建connection host='192.168.10.128', user='root', passwd='123456', port=3306, charset='utf8') cursor = conn.cursor() #获取cursor游标
print conn
print cursor
sql = "show databases"
cursor.execute(sql)
conn.commit() #
cursor.close() #关闭cursor
conn.close() #关闭连接
输出结果:
>>> <_mysql.connection open to '192.168.10.128' at 2ea5da8> <MySQLdb.cursors.Cursor object at 0x0000000002F64198> >>>
建立连接:MySQLdb.connect(参数)
参数名 | 类型 | 说明 |
host | 字符串 | MySQL服务器地址(如果是本地,用localhost) |
port | 数字 | MySQL服务器端口(一般为3306) |
user | 字符串 | MySQL用户 |
password | 字符串 | MySQL用户密码 |
db | 字符串 | 数据库名 (=conn.select_db('数据库名')) |
charset | 字符串 | 字符集类型 |
其他 | 字符串 | |
数据库操作:
1,使用cursor.execute()执行SQL语句
几乎支持所有能在MySQL上用的SQL语句,只要把SQL语句赋给cursor.execute()的参数即可。
选择数据库:
conn.select_db('test')
创建数据库表:
create_table_area="CREATE TABLE `area` (\
`area_id` bigint(20) NOT NULL AUTO_INCREMENT,\
`name` varchar(255) NOT NULL,\
PRIMARY KEY (`area_id`)\
)"
cursor.execute(create_table_area)
插入单条数据:
sql = "insert into area (area_id,name) values(1,'Tim')"
cursor.execute(sql)
插入多条数据:
insert_area="insert into area (area_id,name) values (%s,%s)"
area_value=((1,'CN'),(2,'AU'),(3,'HK'),(4,'US'))
cursor.executemany(insert_area,area_value)
2,使用cursor.fetch*()获取并处理数据(首先要获取数据)
sql = "select * from area"
cursor.execute(sql)
cursor.rowcout #获取到本地的数据行数
cursor.fetchone() #第一条数据
cursor.fetchmany(3) #剩下的3条数据
cursor.fetchall() #剩下的所有数据(当以上的fetch*()都不执行时,执行cursor.fetchall()会得到表中的所有数据)
import MySQLdb try: conn = MySQLdb.connect( host='192.168.10.128', user='root', passwd='123456', port=3306, charset='utf8') cursor = conn.cursor() conn.select_db('test') sql = "select * from area" cursor.execute(sql) print cursor.rowcount ts = cursor.fetchone() print ts ts = cursor.fetchmany(3) print ts ts = cursor.fetchall() print ts except Exception as e: conn.rollback() print e conn.commit() cursor.close() conn.close()
输出结果:(1L,2L,u'Tim',u'SS'这些值的后面前面的L和u只表示数据类型长整型字符型)
5 #rowcount (1L, u'Tim') #fetchone() ((2L, u'Tim'), (3L, u'SS'), (4L, u'SS')) #fetchmany(3) ((5L, u'SS'),) #fetchall()
3,用cursor.fetchall()取得数据库表名和字段名
import MySQLdb try: conn = MySQLdb.connect( host='192.168.10.128', user='root', passwd='123456', port=3306, charset='utf8') cursor = conn.cursor() conn.select_db('test') sql = "show tables" cursor.execute(sql) ts = cursor.fetchall() print ts sql = "select * from area" cursor.execute(sql) tm = cursor.fetchall() print tm except Exception as e: conn.rollback() print e conn.commit() cursor.close() conn.close()
输出结果:结果是一个tuple
((u'area',), (u'student',)) ((1L, u'Tim'), (2L, u'Tim'), (3L, u'SS'), (4L, u'SS'), (5L, u'SS'))
4,用把取得的值用Dict来存储.获得cursor的方式稍微不同:cursorDict = conn.cursor(MySQLdb.cursors.DictCursor)。
DictCursor
的这个功能是继承于CursorDictRowsMixIn
,这个MixIn提供了3个额外的方法: fetchoneDict、fetchmanyDict、fetchallDict
import MySQLdb from MySQLdb.cursors import CursorDictRowsMixIn try: conn = MySQLdb.connect( host='192.168.10.128', user='root', passwd='123456', port=3306, charset='utf8') cursor = conn.cursor() cursorDict = conn.cursor(MySQLdb.cursors.DictCursor) conn.select_db('test') cursorDict.execute("select * from area") tm = cursorDict.fetchall() print tm cursorDict.execute("select * from area") tm = cursorDict.fetchallDict() print tm cursor.execute("select * from area") tm = cursor.fetchall() print tm except Exception as e: conn.rollback() print e conn.commit() cursor.close() conn.close()
输出结果:结果虽然也是个tuple,但是里面还有Dict
({'area_id': 1L, 'name': u'Tim'}, {'area_id': 2L, 'name': u'Tim'}, {'area_id': 3L, 'name': u'SS'}, {'area_id': 4L, 'name': u'SS'}, {'area_id': 5L, 'name': u'SS'}) ({'area_id': 1L, 'name': u'Tim'}, {'area_id': 2L, 'name': u'Tim'}, {'area_id': 3L, 'name': u'SS'}, {'area_id': 4L, 'name': u'SS'}, {'area_id': 5L, 'name': u'SS'}) ((1L, u'Tim'), (2L, u'Tim'), (3L, u'SS'), (4L, u'SS'), (5L, u'SS'))