mysql基本用法(二)安装pymysql及游标使用(2)

接mysql基本用法(二)安装pymysql及游标使用(1)

三、代码如下：

#coding=utf-8
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 = "gloryroad" ,
db = "pythondbnew", 
charset = "utf8"
)

使用cursor()方法获取数据库的操作游标

cursor = conn.cursor()

for i in range(10,20):
    # 另一种插入数据方法，通过格式字符串传入值，此方式可以防止sql注入
    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语句执行成功！")

四、代码如下：

#coding=utf-8
import pymysql

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

使用cursor()方法获取数据库的操作游标

cursor = conn.cursor()
cursor.execute("select * from user")
while 1:
  res = cursor.fetchone()
  if res is None:
    # 表示已经取完结果集
    break
  print(res)
  #将读取到的时间格式化
  print(res[-1].strftime("%Y-%m-%d"))

关闭游标

cursor.close()

提交事务

conn.commit()

关闭数据库连接

conn.close()
print("sql语句执行成功！")

备注信息
fetchmany(2) # 你想取几条自己都可以指定 比如里面的2就是你指定的。

#coding=utf-8
import pymysql

conn = pymysql.connect(
host = "127.0.0.1", 
port = 3306,
user = "root", 
passwd = "gloryroad" ,
db = "grdb", 
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语句执行成功！")

更改一条语句代码如下：加了一条update

#coding=utf-8
import pymysql

conn = pymysql.connect(
host = "127.0.0.1", 
port = 3306,
user = "root", 
passwd = "gloryroad" ,
db = "grdb", 
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语句执行成功！")

批量更新代码： 没有tom 改成lucy 老师的是之前插入的

#coding=utf-8
import pymysql

conn = pymysql.connect(
host = "127.0.0.1", 
port = 3306,
user = "root", 
passwd = "gloryroad" ,
db = "grdb", 
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 where name='tom' or name='amy'")
print("表中所有数据：")
for i in cursor.fetchall():
  print(i)

关闭游标

cursor.close()

提交事务

conn.commit()

关闭数据库连接

conn.close()
print("sql语句执行成功！")

删除

#coding=utf-8
import pymysql

conn = pymysql.connect(
host = "127.0.0.1", 
port = 3306,
user = "root", 
passwd = "gloryroad" ,
db = "grdb", 
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语句执行成功！")

批量删除：

#coding=utf-8
import pymysql

conn = pymysql.connect(
host = "127.0.0.1", 
port = 3306,
user = "root", 
passwd = "gloryroad" ,
db = "grdb", 
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语句执行成功！”)
cursor.close()

批量删除：
批量删除数据：

#coding=utf-8
import pymysql

conn = pymysql.connect(
host = "127.0.0.1", 
port = 3306,
user = "root", 
passwd = "gloryroad" ,
db = "pythondbnew", 
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语句执行成功！")

回滚事务，这块比较重要！！！

#coding=utf-8
import pymysql

conn = pymysql.connect(
host = "127.0.0.1", 
port = 3306,
user = "root", 
passwd = "gloryroad" ,
db = "pythondbnew", 
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语句执行成功！")