接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语句执行成功!")