MySQL-python

python下连接Mysql的一些简单操作，获取连接、插入、批量插入、修改、删除等
安装
mysql-python 只支持2.x,不支持python 3.x

windows10下
python 3.x，用 pip install MySQLClient
测试
import MySQLdb
print(MySQLdb)
macOS下
brew install mysql-connector-c
pip install mysql-python
测试
import pymysql
print(pymysql)
Mac下直接将MySQLdb替换成pymysql即可，其他参数无需修改
 

#coding=utf-8
import MySQLdb

conn = MySQLdb.Connect( host = '127.0.0.1', port = 3306, user = 'root', passwd = 'root', db = 'test', charset = 'utf8' )
cursor = conn.cursor()
sql = "select * from web"
cursor.execute(sql)
rs = cursor.fetchall()
for row in rs: #固定格式输出
    print('网站名称      ','网站初始地址' )  # 获取结果第一个参数
    print (row[1],row[2]) #获取结果第一个参数
print (conn)
print (cursor)
cursor.close()
conn.close()

查询数据库

#coding=utf-8
import MySQLdb

conn = MySQLdb.Connect( host = '127.0.0.1', port = 3306, user = 'root', passwd = 'root', db = 'test', charset = 'utf8' )
cursor = conn.cursor()
sql = "select * from web"
cursor.execute(sql)
#输出结果集有多少条数据
print (cursor.rowcount)
#  查询顶部第一个数据
rs = cursor.fetchone()
print (rs)
# 查询向下查询n个数据 #
rs = cursor.fetchmany(1)
# print rs # 向下查询全部数据
rs = cursor.fetchall()
for row in rs: #固定格式输出
    print ("id=%s,name=%s" , row)
    #获取结果第一个参数
    print (row[0])
cursor.close()
conn.close()

插入、修改、删除操作

#coding=utf-8
import MySQLdb

conn = MySQLdb.Connect( host = '127.0.0.1', port = 3306, user = 'root', passwd = 'root', db = 'test', charset = 'utf8' )
cursor = conn.cursor()
sql_insert = "insert into web(web_name,web_url) values('name10','url10')"
sql_update = "update web set web_name='xxx' where id=4"
sql_delete = "delete from web where id<3"
sql = "select * from web"
try:
    cursor.execute(sql_insert)
    print (cursor.rowcount)
    cursor.execute(sql_update)
    print (cursor.rowcount)
    cursor.execute(sql_delete)
    print (cursor.rowcount)
    conn.commit()
except Exception as e:
    print (e)
    conn.rollback()
cursor.close()
conn.close()

批量插入

#coding=utf-8
import MySQLdb

conn = MySQLdb.Connect( host = '127.0.0.1', port = 3306, user = 'root', passwd = 'root', db = 'test', charset = 'utf8' )
cursor = conn.cursor()
sql_insert = "insert into web(web_name,web_url) values(%s,%s)"
list=[]
data11=('name11','url11')
data12=('name12','url12')
data13=('name13','url13')
list.append(data11)
list.append(data12)
list.append(data13)
try:
    cursor.executemany(sql_insert,list)
    print (cursor.rowcount)
    conn.commit()
except Exception as e:
    print (e)
    conn.rollback()
cursor.close()
conn.close()

转自http://www.imooc.com/article/19509
为适应python3做少量修改