python mysql批量insert数据_Python--Python学习日记之MySQL增删改查、批量插入等等

最近闲置在家无聊玩起了Python，发现用Java平时写的一些小程序如果使用Python的话代码会更加精简，Python使用起来和Java几乎没有区别，今天就先记录下连接Mysql的一些简单操作，获取连接、插入、批量插入、修改、删除等

首先连接Mysql

连接MySQL需要安装插件，下载下来直接默认安装就可以了

测试是否安装成功，如下图就是安装成功了

# coding:UTF-8

import MySQLdb

print MySQLdb

连接数据库代码

# coding:UTF-8

import MySQLdb

conn = MySQLdb.Connect(

host = '127.0.0.1',

port = 3306,

user = 'root',

passwd = 'root',

db = 'springdemo', #数据库名称

charset = 'utf8'

)

cursor = conn.cursor()

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 = 'springdemo',

charset = 'utf8'

)

cursor = conn.cursor()

sql = "select * from spring_user"

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 = 'springdemo',

charset = 'utf8'

)

cursor = conn.cursor()

sql_insert = "insert into spring_user(id,nickname) values(10,'name10')"

sql_update = "update spring_user set nickname='update' where id=9"

sql_delete = "delete from spring_user where d<3"

sql = "select * from spring_user"

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 = 'springdemo',

charset = 'utf8'

)

cursor = conn.cursor()

sql_insert = "insert into spring_user(id,nickname) values(%s,%s)"

list=[]

data11=(11,"name11")

data12=(12,"name12")

data13=(13,"name13")

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()

本人小小菜鸟，难免会有错误，希望大神们看到能够及时指出。৳৸ᵃᵑᵏ Ꮍ৹੫ᵎ