【Python】Python 2 和 Python 3 操作 MySQL 数据库实现创建表、删除表、增删改查操作

于 2022-06-30 07:44:14 发布
阅读量266 收藏 1
点赞数 1
分类专栏： 【Python】 文章标签： 数据库 mysql python
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_54252387/article/details/125528395
版权

1、MySQL数据库和表的编码格式

（1）创建数据库并指定字符集

mysql> create database testpythondb character set utf8;
Query OK, 1 row affected, 1 warning (0.60 sec)

（2）查看数据库的编码格式

mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.10 sec)

（3）创建表并指定字符集

mysql> create table tb_books (
    -> id varchar(10) not null,
    -> name varchar(20) not null,
    -> author varchar(20) not null,
    -> price double not null) default charset = utf8;
Query OK, 0 rows affected, 1 warning (2.16 sec)

mysql> show tables;
+------------------------+
| Tables_in_testpythondb |
+------------------------+
| tb_books               |
+------------------------+
1 row in set (0.17 sec)

mysql> desc tb_books;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | varchar(10) | NO   |     | NULL    |       |
| name   | varchar(20) | NO   |     | NULL    |       |
| author | varchar(20) | NO   |     | NULL    |       |
| price  | double      | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.10 sec)

（4）查看数据表的编码格式

mysql> show create table tb_books;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                          |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_books | CREATE TABLE `tb_books` (
  `id` varchar(10) NOT NULL,
  `name` varchar(20) NOT NULL,
  `author` varchar(20) NOT NULL,
  `price` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

（5）修改数据库的编码格式

mysql> alter database testpythondb character set gbk;
Query OK, 1 row affected (0.17 sec)

mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | gbk   |
+------------------------+-------+
1 row in set (0.06 sec)

（6）修改数据表的编码格式

mysql> alter table tb_books character set gbk;
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb_books;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                  |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_books | CREATE TABLE `tb_books` (
  `id` varchar(10) CHARACTER SET utf8 NOT NULL,
  `name` varchar(20) CHARACTER SET utf8 NOT NULL,
  `author` varchar(20) CHARACTER SET utf8 NOT NULL,
  `price` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

（7）修改字段的编码格式

mysql> create database testpythondb character set utf8;
mysql> alter table tb_books change name bookname varchar(25) character set gbk not null;
Query OK, 0 rows affected, 1 warning (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc tb_books;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | varchar(10) | NO   |     | NULL    |       |
| bookname | varchar(25) | NO   |     | NULL    |       |
| author   | varchar(20) | NO   |     | NULL    |       |
| price    | double      | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show create table tb_books;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                            |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_books | CREATE TABLE `tb_books` (
  `id` varchar(10) CHARACTER SET utf8 NOT NULL,
  `bookname` varchar(25) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `author` varchar(20) CHARACTER SET utf8 NOT NULL,
  `price` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

（8）添加表外键

mysql> create database testpythondb character set utf8;
mysql> create table tb_bookstore (
    -> storeid varchar(10) not null,
    -> storename varchar(20) not null,
    -> address varchar(50) not null);
Query OK, 0 rows affected (1.02 sec)

mysql> show tables;
+------------------------+
| Tables_in_testpythondb |
+------------------------+
| tb_books               |
| tb_bookstore           |
+------------------------+
2 rows in set (0.00 sec)

mysql> show create table tb_bookstore;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                             |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_bookstore | CREATE TABLE `tb_bookstore` (
  `storeid` varchar(10) NOT NULL,
  `storename` varchar(20) NOT NULL,
  `address` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table tb_bookstore add constraint idx_id primary key (storeid);
Query OK, 0 rows affected (1.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_bookstore;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| storeid   | varchar(10) | NO   | PRI | NULL    |       |
| storename | varchar(20) | NO   |     | NULL    |       |
| address   | varchar(50) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table tb_books add storeid varchar(10) not null;
Query OK, 0 rows affected (0.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_books;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | varchar(10) | NO   |     | NULL    |       |
| bookname | varchar(25) | NO   |     | NULL    |       |
| author   | varchar(20) | NO   |     | NULL    |       |
| price    | double      | NO   |     | NULL    |       |
| storeid  | varchar(10) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table tb_books add constraint fk_book_store foreign key(storeid) references tb_bookstore(storeid);
Query OK, 0 rows affected (1.89 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_books;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | varchar(10) | NO   |     | NULL    |       |
| bookname | varchar(25) | NO   |     | NULL    |       |
| author   | varchar(20) | NO   |     | NULL    |       |
| price    | double      | NO   |     | NULL    |       |
| storeid  | varchar(10) | NO   | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> show create table tb_books;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                             |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_books | CREATE TABLE `tb_books` (
  `id` varchar(10) CHARACTER SET utf8 NOT NULL,
  `bookname` varchar(25) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `author` varchar(20) CHARACTER SET utf8 NOT NULL,
  `price` double NOT NULL,
  `storeid` varchar(10) NOT NULL,
  KEY `fk_book_store` (`storeid`),
  CONSTRAINT `fk_book_store` FOREIGN KEY (`storeid`) REFERENCES `tb_bookstore` (`storeid`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

（9）删除表外键

mysql> alter table tb_books drop foreign key fk_book_store;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb_books;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_books | CREATE TABLE `tb_books` (
  `id` varchar(10) CHARACTER SET utf8 NOT NULL,
  `bookname` varchar(25) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `author` varchar(20) CHARACTER SET utf8 NOT NULL,
  `price` double NOT NULL,
  `storeid` varchar(10) NOT NULL,
  KEY `fk_book_store` (`storeid`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc tb_books;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | varchar(10) | NO   |     | NULL    |       |
| bookname | varchar(25) | NO   |     | NULL    |       |
| author   | varchar(20) | NO   |     | NULL    |       |
| price    | double      | NO   |     | NULL    |       |
| storeid  | varchar(10) | NO   | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table tb_books drop key fk_book_store;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_books;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | varchar(10) | NO   |     | NULL    |       |
| bookname | varchar(25) | NO   |     | NULL    |       |
| author   | varchar(20) | NO   |     | NULL    |       |
| price    | double      | NO   |     | NULL    |       |
| storeid  | varchar(10) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> show create table tb_books;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                              |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_books | CREATE TABLE `tb_books` (
  `id` varchar(10) CHARACTER SET utf8 NOT NULL,
  `bookname` varchar(25) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `author` varchar(20) CHARACTER SET utf8 NOT NULL,
  `price` double NOT NULL,
  `storeid` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

（10）删除数据库

mysql> create database testpythondb character set utf8;
mysql> drop database testpythondb;
Query OK, 2 rows affected (0.62 sec)

2、PyCharm默认编码格式设置
3、Python 2 操作 MySQL 数据库

安装MySQL-python-1.2.5.win32-py2.7.exe，下载地址：https://pypi.org/project/MySQL-python/#files
 在 Python 2.7 环境下，pip install mysql 报错，故只能安装MySQL-python程序。
连接数据库

# coding=utf-8

import MySQLdb

connection = MySQLdb.connect(user='root', passwd='123456', db='testpythondb')
cursor = connection.cursor()
cursor.execute("SELECT VERSION()")
result = cursor.fetchone()

print "数据库版本：{}".format(result)

connection.close()

输出：

数据库版本：(‘5.7.35’,)

Python 2.7 只能连接 MySQL 5.x 版本。
本机 MySQL 5.7.35 和 MySQL 8.0.21 版本切换方式：
修改注册表：Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MySQL\ImagePath，编辑该文件，“G:\Program Files\mysql-5.7.35-win32\bin\mysqld” MySQL为MySQL 5.7.35版本，“E:\Program Files\mysql-8.0.21-winx64\bin\mysqld” MySQL为MySQL 8.0.21 版本。

删除表、创建表

# coding=utf-8

import MySQLdb

connection = MySQLdb.connect(user='root', passwd='123456', db='testpythondb')
cursor = connection.cursor()

cursor.execute("DROP TABLE IF EXISTS tb_books")

sql = "create table tb_books (          \
        id varchar(10) not null,        \
        name varchar(20) not null,      \
        author varchar(20) not null,    \
        price double not null) default charset = utf8"

cursor.execute(sql)

connection.close()

mysql> show tables;
+------------------------+
| Tables_in_testpythondb |
+------------------------+
| tb_books               |
+------------------------+
1 row in set (0.00 sec)

INSERT操作

# coding=utf-8

# import sys
import MySQLdb

# reload(sys)
# sys.setdefaultencoding("utf-8")

connection = MySQLdb.connect(user='root', passwd='123456', db='testpythondb', charset='utf8')
cursor = connection.cursor()

sql1 = "insert into tb_books (id, name, author, price) values ('1', '呼啸山庄', '艾米莉·勃朗特', 80.5)"
sql2 = "insert into tb_books (id, name, author, price) values('%s', '%s', '%s', '%g')" % ("2", "老人与海", "欧内斯特·米勒尔·海明威", 88.2)

try:
    cursor.execute(sql1)
    cursor.execute(sql2)
    connection.commit()
except:
    connection.rollback()

connection.close()

mysql> select * from tb_books;
+----+--------------+------------------------------------+-------+
| id | name         | author                             | price |
+----+--------------+------------------------------------+-------+
| 1  | 呼啸山庄     | 艾米莉·勃朗特                      |  80.5 |
| 2  | 老人与海     | 欧内斯特·米勒尔·海明威             |  88.2 |
+----+--------------+------------------------------------+-------+
2 rows in set (0.00 sec)

UPDATE操作

# coding=utf-8

# import sys
import MySQLdb

# reload(sys)
# sys.setdefaultencoding("utf-8")

connection = MySQLdb.connect(user='root', passwd='123456', db='testpythondb', charset='utf8')
cursor = connection.cursor()

sql = "update tb_books set price = 82.6 where id='1'"

try:
    cursor.execute(sql)
    connection.commit()
except:
    connection.rollback()

connection.close()

mysql> select * from tb_books;
+----+--------------+------------------------------------+-------+
| id | name         | author                             | price |
+----+--------------+------------------------------------+-------+
| 1  | 呼啸山庄     | 艾米莉·勃朗特                      |  82.6 |
| 2  | 老人与海     | 欧内斯特·米勒尔·海明威             |  88.2 |
+----+--------------+------------------------------------+-------+
2 rows in set (0.00 sec)

SELECT操作

# coding=utf-8

import sys
import MySQLdb

reload(sys)
sys.setdefaultencoding("utf-8")

connection = MySQLdb.connect(user='root', passwd='123456', db='testpythondb', charset='utf8')
cursor = connection.cursor()

sql = "select * from tb_books where price > 80"

try:
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        id = row[0]
        name = row[1]
        author = row[2]
        price = row[3]
        print "id:{0}, name:{1}, author:{2}, price:{3}".format(id, name, author, price)
except:
    connection.rollback()

connection.close()

输出：

id:1, name:呼啸山庄, author:艾米莉·勃朗特, price:82.6
id:2, name:老人与海, author:欧内斯特·米勒尔·海明威, price:88.2

DELETE操作

# coding=utf-8

# import sys
import MySQLdb

# reload(sys)
# sys.setdefaultencoding("utf-8")

connection = MySQLdb.connect(user='root', passwd='123456', db='testpythondb', charset='utf8')
cursor = connection.cursor()

sql = "delete from tb_books where price > 85"

try:
    cursor.execute(sql)
    connection.commit()
except:
    connection.rollback()

connection.close()

mysql> select * from tb_books;
+----+--------------+----------------------+-------+
| id | name         | author               | price |
+----+--------------+----------------------+-------+
| 1  | 呼啸山庄     | 艾米莉·勃朗特        |  82.6 |
+----+--------------+----------------------+-------+
1 row in set (0.00 sec)

4、Python 3 操作 MySQL 数据库

安装 pymysql 库

> pip install pymysql

连接数据库

# coding=utf-8

import pymysql

connection = pymysql.connect(user='root', passwd='123456', db='testpythondb')
cursor = connection.cursor()
cursor.execute("SELECT VERSION()")
result = cursor.fetchone()

print("数据库版本：", result)

connection.close()

输出：

数据库版本： (‘8.0.21’,)

删除表、创建表

# coding=utf-8

import pymysql

connection = pymysql.connect(user='root', passwd='123456', db='testpythondb')
cursor = connection.cursor()

cursor.execute("DROP TABLE IF EXISTS tb_books")

sql = "create table tb_books (          \
        id varchar(10) not null,        \
        name varchar(20) not null,      \
        author varchar(20) not null,    \
        price double not null) default charset = utf8"

cursor.execute(sql)

connection.close()

mysql> show tables;
+------------------------+
| Tables_in_testpythondb |
+------------------------+
| tb_books               |
+------------------------+
1 row in set (0.00 sec)

mysql> show create table tb_books;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                          |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_books | CREATE TABLE `tb_books` (
  `id` varchar(10) NOT NULL,
  `name` varchar(20) NOT NULL,
  `author` varchar(20) NOT NULL,
  `price` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

INSERT操作

# coding=utf-8

import pymysql

connection = pymysql.connect(user='root', passwd='123456', db='testpythondb')
cursor = connection.cursor()

sql1 = "insert into tb_books (id, name, author, price) values ('1', '呼啸山庄', '艾米莉·勃朗特', 80.5)"
sql2 = "insert into tb_books (id, name, author, price) values('%s', '%s', '%s', '%g')" % ("2", "老人与海", "欧内斯特·米勒尔·海明威", 88.2)

try:
    cursor.execute(sql1)
    cursor.execute(sql2)
    connection.commit()
except:
    connection.rollback()

connection.close()

mysql> select * from tb_books;
+----+--------------+------------------------------------+-------+
| id | name         | author                             | price |
+----+--------------+------------------------------------+-------+
| 1  | 呼啸山庄     | 艾米莉·勃朗特                      |  80.5 |
| 2  | 老人与海     | 欧内斯特·米勒尔·海明威             |  88.2 |
+----+--------------+------------------------------------+-------+
2 rows in set (0.00 sec)

UPDATE操作

# coding=utf-8

import pymysql

connection = pymysql.connect(user='root', passwd='123456', db='testpythondb')
cursor = connection.cursor()

sql = "update tb_books set price = 82.6 where id='1'"

try:
    cursor.execute(sql)
    connection.commit()
except:
    connection.rollback()

connection.close()

mysql> select * from tb_books;
+----+--------------+------------------------------------+-------+
| id | name         | author                             | price |
+----+--------------+------------------------------------+-------+
| 1  | 呼啸山庄     | 艾米莉·勃朗特                      |  82.6 |
| 2  | 老人与海     | 欧内斯特·米勒尔·海明威             |  88.2 |
+----+--------------+------------------------------------+-------+
2 rows in set (0.00 sec)

SELECT操作

# coding=utf-8

import pymysql

connection = pymysql.connect(user='root', passwd='123456', db='testpythondb')
cursor = connection.cursor()

sql = "select * from tb_books where price > 80"

try:
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        id = row[0]
        name = row[1]
        author = row[2]
        price = row[3]
        print("id:{0}, name:{1}, author:{2}, price:{3}".format(id, name, author, price))
except:
    connection.rollback()

connection.close()

输出：

id:1, name:呼啸山庄, author:艾米莉·勃朗特, price:82.6
id:2, name:老人与海, author:欧内斯特·米勒尔·海明威, price:88.2

DELETE操作

# coding=utf-8

import pymysql

connection = pymysql.connect(user='root', passwd='123456', db='testpythondb')
cursor = connection.cursor()

sql = "delete from tb_books where price > 85"

try:
    cursor.execute(sql)
    connection.commit()
except:
    connection.rollback()

connection.close()

mysql> select * from tb_books;
+----+--------------+----------------------+-------+
| id | name         | author               | price |
+----+--------------+----------------------+-------+
| 1  | 呼啸山庄     | 艾米莉·勃朗特        |  82.6 |
+----+--------------+----------------------+-------+
1 row in set (0.00 sec)`
九层之台起于累土
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
python连接数据库（3）插入更新和删除
GoodLuckAC的博客
05-30 7713
首先说一下事务与回滚： 事务就是执行单元的集合 回滚就是回到执行之前的状态 事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。 ① 原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。 ② 一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相
python3对mysql的增删改查操作
kelanj的博客
09-20 1775
思路： 1.python中下载库PyMySql 2.打开MySQL，创建数据库py_test 3.创建.py文件，代码：连接数据库，创建游标对象，创建表，对表进行增删改查，关闭游标对象，关闭数据库 代码如下： import pymysql #数据库连接 db=pymysql.connect(host="localhost",port=3306,user="root",password...
python3 对mysql数据库的常见操作(查询，插入，更新，删除)简单案例
热门推荐
大蛇王的博客
06-25 2万+
环境：python3.6 库：pip install pymysql 链接mysql数据库的常用方法↓↓↓： import pymysql #python3中不支持mysqldb conn=pymysql.connect(host='数据库地址', user='name', passwd='*...
Python3中连接Mysql数据库进行查询、增加、删除数据
qq_37273544的博客
12-24 814
用python连接mysql数据库进行常用操作，将常用操作封装成了一个类方便调用 """ 连接数据库，查询、增加、删除数据 """ import MySQLdb class MysqlSearch(object): """docstring for ClassName""" def __init__(self): # super(ClassName, self).__init_
python操作mysql——插入、查询、删除
技术酱油党
12-27 944
python操作mysql——插入、查询、删除目录python操作mysql插入查询删除目录代码块语法遵循标准markdown代码，例如：#!/usr/bin/env python #coding=utf-8#MySQLdb查询 import MySQLdb#conn = MySQLdb.connect(host='localhost',port=3306,user='root',passwd='4
Python实现连接MySql数据库及增删改查操作详解
09-09
Python 实现连接 MySQL 数据库及增删改查操作详解 在 Python 开发中，与 MySQL 数据库进行交互是一项常见的任务。PyMySQL 是一个用于 Python 3.x 的库，它允许我们连接 MySQL 数据库并执行 SQL 操作。这篇文章将...
python3.6连接mysql数据库及增删改查操作详解
09-08
在本文中，我们将详细探讨如何使用 Python 3.6 连接到 MySQL 数据库并进行基本的 CRUD（创建、读取、更新、删除）操作。 首先，要连接到 MySQL 数据库，你需要安装 `pymysql` 库。`pymysql` 是一个 Python 的 MySQL...
Python操作mysql数据库实现增删查改功能的方法
09-09
本篇文章将详细讲解如何使用Python实现对MySQL数据库的增删查改（CRUD）操作。 首先，我们需要导入Python中的MySQL数据库连接库`MySQLdb`。`MySQLdb`是Python的一个MySQL接口，它允许我们通过Python代码连接到MySQL...
Python操作MySQL数据库实例详解【安装、连接、增删改查等】
09-08
本篇文章将详细阐述如何使用Python与MySQL进行交互，包括安装MySQL库、建立连接、数据库和表的操作以及数据的增删改查。 首先，安装Python连接MySQL的库——MySQL Connector/Python。你可以通过Python的包管理器pip...
python中操作mysql数据库CRUD（增、删、改、查）
wangprince2017
11-15 1864
一、插入操作 #coding:utf8 import pymysql.cursors #连接数据库 conn=pymysql.Connect( user="root", password="root", port=3306, host="127.0.0.1", db="wxl123", charset="utf8" ) #设置字符编码以及自动提交 cursor=conn.cursor() cursor.execute('set names u...
Python MySQL删除表
PY洋洋
02-03 3243
删除表 仅在存在时删除
Python 操作数据库（2）
hiekay的博客
01-07 226
回顾一下：（1）连接数据库;（2）建立指针；（3）通过指针插入记录；（4）提交将插入结果保存到数据库。在交互模式中，先温故，再知新。 >>> #导入模块 >>> import MySQLdb >>> #连接数据库 >>> conn = MySQLdb.connect(host="localhost",user=&quot
python2连接数据库进行简单的增删改查
g1oodboy的博客
12-05 466
python2进行数据库连接并且进行简单的增删改查，如果添加新的内容可以自行添加
Python学习笔记（6）
qq_44934798的博客
09-05 205
参考资料：https://www.runoob.com/python3/python-mysql-connector.html Python MySQL - mysql-connector 驱动 MySQL 是最流行的关系型数据库管理系统 创建数据库连接 可以使用以下代码来连接数据库： demo_mysql_test.py: import mysql.connector mydb = mysq...
python操作mysql数据库（增，删，改，查）
快递小可的博客
11-12 1万+
python操作mysql数据库
Python连接mysql进行数据库的删表，建表操作（后续更新数据同步）
qq_40944039的博客
06-09 333
Python小白成长记01 Python连接mysql进行数据库的删表，建表，插入操作 人生的第一篇博客呀，希望各位不吝赐教，共同学习，共同交流，一起进步，在脱发的道路上越走越远????； 话不多说，先看看自己写的蹩脚的代码，希望各位不要嘲笑???? 代码示例 代码的缩进各位自己调一下，这个不知道为什么显示没有缩进。 import pymysql import numpy as np import pandas as pd import pyhdb from sqlalchemy import cre
python2操作mysql数据库_例程
文选的blog
02-27 381
首先MySQLdb库是一个第三方库，需要使用pip安装相关库 pip install mysql-python 安装成功之后进入例程 例程的主要功能是 连接mysql数据库 执行相关mysql语句 关闭连接 #!/usr/bin/python # -*- coding: UTF-8 -*- import MySQLdb ...
Python2操作MySQL数据库封装通用方法
Miss520jenny的博客
01-26 324
Python2操作MySQL数据库示例如下： coding=utf-8 import pymysql class Database(): def init(self, host, port, db, user, passwd, charset=‘utf8’): self.host = host self.port = port self.db = db self.user = user self.password = passwd self.charset = charset def open(self):
python操作mysql创建表并增删改查的class
最新发布
09-17
答：Python操作MySQL创建表并增删改查的类可以使用SQLAlchemy库来实现。SQLAlchemy可以帮助你使用Python编写MySQL数据库操作的类，包括创建表、增加、删除和修改数据等操作。 ### 回答2： 下面是一个使用Python...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

九层之台起于累土

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值