首页 > 数据库 >Python-pymysql操作MySQL数据库

Python-pymysql操作MySQL数据库

时间:2022-10-10 17:16:04浏览次数:63  
标签:Python pymysql cursor ## user MySQL print conn

一、pymysql

py -m pip install pymysql;

二、pymysql数据库操作

1.简单示例

#coding=utf-8
import pymysql
# 打开数据库连接
conn = pymysql.connect(
    host = "127.0.0.1",
    port = 3306,
    user = "lgb",
    passwd = "Lgb@1234",
    db = "test",
    charset = "utf8")
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
print(cursor)
print(type(cursor))

2.创建数据库

#coding=utf-8
import pymysql
try:
    conn = pymysql.connect(
    host = "127.0.0.1",
    port = 3306,
    user = "lgb",
    passwd = "Lgb@1234"
    )
    cur = conn.cursor()
    cur.execute("CREATE DATABASE IF NOT EXISTS grdb 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]))

#COLLATE utf8_general_ci:大小写不敏感

3.创建表

#coding=utf-8
import pymysql
try:
    conn = pymysql.connect(
    host = "127.0.0.1",
    port = 3306,
    user = "lgb",
    passwd = "Lgb@1234")

    conn.select_db('grdb')  ## 选择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]))

4.插入表数据

#coding=utf-8
import pymysql
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "lgb",
passwd = "Lgb@1234",
db = "grdb",
charset = "utf8")
## 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
## 方式一:直接执行insert语句,插入一条数据
insert = cursor.execute("insert into user values(1,'Tom','123','1990-01-01')")
print(u"添加语句受影响的行数:",insert)
## 方式二:通过格式字符串传入值,此方式可以防止SQL注入
sql = "insert into user values(%s,%s,%s,%s)"
insert = cursor.execute(sql,(3,'lucy','efg','1993-02-01'))
print(u"添加语句受影响的行数:",insert)
## 关闭游标
cursor.close()
## 提交事务
conn.commit()
## 关闭数据库连接
conn.close()
print(u"sql语句执行成功!")

5.查询表数据语句

(1)逐条获取fetchone()

#coding=utf-8
import pymysql
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "lgb",
passwd = "Lgb@1234",
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语句执行成功!")

#cursor.fetchone  一条一条获取数据,每条数据是元组

(2)获取n条数据fetchmany(n)

#coding=utf-8
import pymysql
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "lgb",
passwd = "Lgb@1234",
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语句执行成功!")

#cursor.fetchmany  获取前两行数据,每条数据是元组

(3)获取所有数据fetchall()

#coding=utf-8
import pymysql
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "lgb",
passwd = "Lgb@1234",
db = "grdb",
charset = "utf8")
## 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
cursor.execute("select * from user")
## 获取所有的数据
resTuple = cursor.fetchall()
print("结果集类型:",type(resTuple))
for i in resTuple:
    print(i)
## 关闭游标
cursor.close()
## 提交事务
conn.commit()
## 关闭数据库连接
conn.close()
print("sql语句执行成功!")

#cursor.fetchall  获取数据,每条数据是元组

6.更新数据

#coding=utf-8
import pymysql
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "lgb",
passwd = "Lgb@1234",
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'), ('amy2x', 'amy')])
## 查看更新后的结果
query = cursor.execute("select * from user")
print("表中所有数据:")
for i in cursor.fetchall():
  print(i)
## 关闭游标
cursor.close()
## 提交事务
conn.commit()
## 关闭数据库连接
conn.close()
print("sql语句执行成功!")

 

7.删除数据

#coding=utf-8
import pymysql
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "lgb",
passwd = "Lgb@1234",
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) ## 提交事务 conn.commit() ## 关闭游标 cursor.close()

标签:Python,pymysql,cursor,##,user,MySQL,print,conn
From: https://www.cnblogs.com/yeyuzhuanjia/p/16776346.html

相关文章

  • Python基础11
    今日内容概要函数参数名称空间与作用域名字的查找顺序今日内容详细函数参数位置参数及关键字参数位置形参 函数定义阶段括号内从左往右依次填写的变量名 deffu......
  • python连接tdengine数据库
    1、首先在pyCharm中install(我的做法)或者: pipinstallC:\TDengine\connector\python\windows\python3(参考 https://blog.csdn.net/Tomonkey/article/details/10880791......
  • python的高级函数
    #1.列表生成器num=[xforxinrange(1,10)]print(num)old=[xforxinrange(1,100)ifx%2==1]print(old)print([x*xforxinrange(10)])a=["a","b","c"]......
  • Mysql:为什么使用索引以及索引的优缺点
    索引概述索引优缺点优点(1)是索引的关键,优点(3)和优点(4)其实是创建索引后顺带的福利。缺点(1)和(2)是小缺点,真正的大缺点是(3)。好比你在原本已经订好目录的书上再插入新的1页,意......
  • Python 获取几天前的时间
    两种计算方法importtimeimportdatetime#先获得时间数组格式的日期threeDayAgo=(datetime.datetime.now()-datetime.timedelta(days=3))#转换为时间戳timeS......
  • Python学习路程——Day11
    Python学习路程——Day11函数参数在使用函数参数时,一般情况下所需要遵循的规范: 越短的、越简单的、越靠前 越长的、越复杂的、越靠后同一个形参在调用的时候不能多......
  • python编程从入门到实践--第4章 操作列表
    一。遍历整个列表注意缩进,与不要遗漏冒号。magicians=['alice','david','carolina']formagicianinmagicians:#print(magician)print(f"{magician......
  • MySQL练习题2
    6,取得平均工资最高的部门的部门名称。selectd.dname,avg(e.sal)asavgsalfromempejoindeptdone.deptno=d.deptnogroupbyd.dnameorderbyavgsaldescli......
  • python命令行输入\n失效
    测试在命令行输入aaaa\ncc时,没有把\n变为换行python把aaaa\ncc变为了aaaa\\ncca=input('请输入:').strip()print(a)输出请输入:aaaa\nccaaaa\ncc......
  • 进入python的世界_day10_python基础——函数之参数、名称空间
    一、位置参数位置形参​ 函数定义阶段(函数定义第一行)括号内从左往右,依次填写变量名位置实参​ 函数调用阶段括号内从左往右,依次填写传入的数据值"""1.位置形参......