Python--Python学习日记之MySQL增删改查、批量插入等等

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

---

首先连接Mysql

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

插件下载地址https://sourceforge.net/projects/mysql-python/

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

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

---

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