Python操作mysql增删改查

打开数据库连接

import pymysql

conn = pymysql.connect(
    host = "127.0.0.1", 
    port = 3306,
    user = "root", 
    passwd = "123456", 
    db = "testdb", 
    charset = "utf8")  

# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
print(cursor)
print(type(cursor))

创建数据库

import pymysql

try:
    conn = pymysql.connect(
    host = "127.0.0.1", 
    port = 3306,
    user = "root", 
    passwd = "123456" 
    )
    cur = conn.cursor()
    cur.execute('CREATE DATABASE IF NOT EXISTS pythonDBnew1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')
    cur.close()
    conn.close()
    print("创建数据库pythonDB成功! ")
except pymysql.Error as e:
     print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

建表

import pymysql

try:
    conn = pymysql.connect(
        host="127.0.0.1",
        port=3306,
        user="root",
        passwd="123456"
    )
    conn.select_db('pythondbnew1')  # 选择pythonDB数据库
    cur = conn.cursor()  # 获取游标

    # 如果所建表已存在，删除重建
    cur.execute("drop table if exists User;")
    # 执行建表sql语句
    cur.execute('''CREATE TABLE `User`(
	`id` int(11) DEFAULT NULL,
	`name` varchar(255) DEFAULT NULL,
	`password` varchar(255) DEFAULT NULL,
	 `birthday` date DEFAULT NULL
                   )ENGINE=innodb DEFAULT CHARSET=utf8;''')
    cur.close()
    conn.close()
    print(u"创建数据表成功")
except pymysql.Error as e:
    print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

增

import pymysql

conn = pymysql.connect(
host = "127.0.0.1", 
port = 3306,
user = "root", 
passwd = "123456" ,
db = "pythondbnew1", 
charset = "utf8"
)
cursor = conn.cursor()
# 插入一条数据
insert = cursor.execute("insert into user values(1,'Tom','123','1990-01-01')")
print("添加语句受影响的行数：", insert)
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()

# 另一种插入数据方法，通过格式字符串传入值，此方式可以防止sql注入
sql = "insert into user values(%s, %s, %s, %s)"
insert = cursor.execute(sql, (3,'lucy','efg','1993-02-01'))
print("添加语句受影响的行数：", insert)
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print(u"sql语句执行成功！")

批量插入数据

import pymysql
import random
import time

def now():
    return time.strftime("%Y-%m-%d")

conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "123456" ,
db = "pythondbnew1",
charset = "utf8"
)
cursor = conn.cursor()

for i in range(10,20):
    sql = "insert into user values(%s, %s, %s, %s)"
    cursor.execute(sql, (random.randint(1,10000),'lucy'+str(random.randint(1,10000)),'efg'+str(random.randint(1,10000)),now()))

# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print(u"sql语句执行成功！")

import pymysql

conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "123456" ,
db = "pythondbnew1",
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()

#批量插入条数据
sql = "insert into user values(%s, %s, %s, %s)"
insert = cursor.executemany(sql, [
    (5,'tom','tom','1989-03-17'),
    (6,'amy','test','1898-12-01'),
    (7,'lily','linux','1994-06-23')])
print("批量插入返回受影响的行数：", insert)

# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！")

查

import pymysql

conn = pymysql.connect(
host = "127.0.0.1", 
port = 3306,
user = "root", 
passwd = "123456" ,
db = "pythondbnew1", 
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
cursor.execute("select * from user")

# 获取游标处两条数据
resTuple = cursor.fetchmany(2)
print("结果集类型：", type(resTuple))
for i in resTuple:
  print(i)
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！")

import pymysql

conn = pymysql.connect(
host = "127.0.0.1", 
port = 3306,
user = "root", 
passwd = "123456" ,
db = "pythondbnew1", 
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
cursor.execute("select * from user")
resSet = cursor.fetchall()
print("共%s条数据。" %len(resSet))
print(resSet)
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！")

改

import pymysql

conn = pymysql.connect(
host = "127.0.0.1", 
port = 3306,
user = "root", 
passwd = "123456" ,
db = "pythondbnew1", 
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()

# 更新一条数据
update = cursor.execute("update user set password = 'Tom_test' where name='Tom'")
print("修改语句受影响的行数：", update)
#查询一条数据
cursor.execute("select * from user where name='Tom';")
print(cursor.fetchone())

# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！")

import pymysql

conn = pymysql.connect(
host = "127.0.0.1", 
port = 3306,
user = "root", 
passwd = "123456" ,
db = "pythondbnew1", 
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()

#查询一条数据
query = cursor.execute("select * from user")
print("表中所有数据：")
for i in cursor.fetchall():
  print(i)
# 批量更新数据
cursor.executemany("update user set password = %s where name=%s", [('tomx2x', 'tom'), ('Tomx2x', 'amy')])
# 查看更新后的结果
query = cursor.execute("select * from user")
print("表中所有数据：")
for i in cursor.fetchall():
  print(i)
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！")

删

import pymysql

conn = pymysql.connect(
host = "127.0.0.1", 
port = 3306,
user = "root", 
passwd = "123456" ,
db = "pythondbnew1", 
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
cursor.execute("select * from user")
print("表中所有数据：")
for i in cursor.fetchall():
  print(i)
# 删除数据
delete = cursor.execute("delete from user where name='tom'")
print("删除语句影响的行数：", delete)

print("删除一条数据后，表中数据：")
cursor.execute("select * from user")
for i in cursor.fetchall():
  print(i)

# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！")

import pymysql

conn = pymysql.connect(
host = "127.0.0.1", 
port = 3306,
user = "root", 
passwd = "123456" ,
db = "pythondbnew1", 
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
cursor.execute("select * from user")
print("表中所有数据：")
for i in cursor.fetchall():
  print(i)
# 批量删除数据
delete = cursor.executemany("delete from user where name=%s", [('amy',), ('lily',)])
print("删除语句影响的行数：", delete)

print("删除一条数据后，表中数据：")
cursor.execute("select * from user")
for i in cursor.fetchall():
  print(i)

# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！")

import pymysql

conn = pymysql.connect(
host = "127.0.0.1", 
port = 3306,
user = "root", 
passwd = "123456" ,
db = "pythondbnew1", 
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
cursor.execute("select * from user")
datas = cursor.fetchall()
print("修改前的数据：\n", datas[0])
# 更新数据表中第一条数据
cursor.execute("update user set birthday='2100-08-12' where name='tom'")
cursor.execute("select * from user")
datas = cursor.fetchall()
print("修改后的数据：\n", datas[0])

# 回滚事务
conn.rollback()
cursor.execute("select * from user")
datas = cursor.fetchall()
print("事务回滚后的数据：\n", datas[0])

# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！")