Python 中如何连接 MySQL 数据库

Python实现连接MySQL数据库的常见方法总结_python_脚本之家 (jb51.net)

mysql -u root -p

show databases;

create database stock; 

介绍两种方法来连接 MySQL 数据库，并将查询结果转化为 Pandas dataframe 对象：第一种方法使用 pymysql 库来连接 MySQL 数据库；第二种方法则使用 SQLAlchemy 的 create_engine 函数创建 MySQL 数据库连接引擎。同时，针对这两种方法，我们还将对代码进行封装和优化，提高程序的可读性和健壮性

使用 pymysql

方法一：使用 pymysql 库连接 MySQL 数据库

创建连接对象——db= pymysql()
创建cursor游标对象——cur = db.cursor()
数据操作——cur.execute(sql)
提交连接事务——db.commit()
关闭cursor游标对象——cur.close()
关闭连接对象——db.close( )

数据库连接对象常用方法：

cursor()

commit()

rollback()

close()

安装pymysqlclient时遇到编译错误，可能跟我这个老旧的windows版本有关系？

Collecting mysqlclient
  Downloading mysqlclient-2.1.1.tar.gz (88 kB)
     -------------------------------------- 88.1/88.1 kB 556.4 kB/s eta 0:00:00
  Preparing metadata (setup.py): started
  Preparing metadata (setup.py): finished with status 'done'
Building wheels for collected packages: mysqlclient
  Building wheel for mysqlclient (setup.py): started
  Building wheel for mysqlclient (setup.py): finished with status 'error'
  error: subprocess-exited-with-error
  
  × python setup.py bdist_wheel did not run successfully.
  │ exit code: 1
  ╰─> [23 lines of output]
      running bdist_wheel
      running build
      running build_py
      creating build
      creating build\lib.win32-3.7
      creating build\lib.win32-3.7\MySQLdb
      copying MySQLdb\__init__.py -> build\lib.win32-3.7\MySQLdb
      copying MySQLdb\_exceptions.py -> build\lib.win32-3.7\MySQLdb
      copying MySQLdb\connections.py -> build\lib.win32-3.7\MySQLdb
      copying MySQLdb\converters.py -> build\lib.win32-3.7\MySQLdb
      copying MySQLdb\cursors.py -> build\lib.win32-3.7\MySQLdb
      copying MySQLdb\release.py -> build\lib.win32-3.7\MySQLdb
      copying MySQLdb\times.py -> build\lib.win32-3.7\MySQLdb
      creating build\lib.win32-3.7\MySQLdb\constants
      copying MySQLdb\constants\__init__.py -> build\lib.win32-3.7\MySQLdb\constants
      copying MySQLdb\constants\CLIENT.py -> build\lib.win32-3.7\MySQLdb\constants
      copying MySQLdb\constants\CR.py -> build\lib.win32-3.7\MySQLdb\constants
      copying MySQLdb\constants\ER.py -> build\lib.win32-3.7\MySQLdb\constants
      copying MySQLdb\constants\FIELD_TYPE.py -> build\lib.win32-3.7\MySQLdb\constants
      copying MySQLdb\constants\FLAG.py -> build\lib.win32-3.7\MySQLdb\constants
      running build_ext
      building 'MySQLdb._mysql' extension
      error: Microsoft Visual C++ 14.0 is required. Get it with "Build Tools for Visual Studio": https://visualstudio.microsoft.com/downloads/
      [end of output]
  
  note: This error originates from a subprocess, and is likely not a problem with pip.
  ERROR: Failed building wheel for mysqlclient
  Running setup.py clean for mysqlclient
Failed to build mysqlclient
ERROR: Could not build wheels for mysqlclient, which is required to install pyproject.toml-based projects
Process returned with code 1

Building mysqlclient on Windows is very hard. But there are some binary wheels you can install easily.

If binary wheels do not exist for your version of Python, it may be possible to build from source, but if this does not work, do not come asking for support. To build from source, download the MariaDB C Connector and install it. It must be installed in the default location (usually "C:\Program Files\MariaDB\MariaDB Connector C" or "C:\Program Files (x86)\MariaDB\MariaDB Connector C" for 32-bit). If you build the connector yourself or install it in a different location, set the environment variable MYSQLCLIENT_CONNECTOR before installing. Once you have the connector installed and an appropriate version of Visual Studio for your version of Python:

来自pypi的官方文档：

windows平台上build mysqlclient非常困难，但是可以使用二进制wheel文件可以很容易安装。如果没有对应Python版本的wheels文件我们需要从源代码开始编译。如果编译失败不要来寻求帮助和支持。显然我的windows版本是一个老旧的32位系统，没有现成的wheels安装文件，从源代码开始编译和安装遇到问题。

使用 SQLAlchemy

方法二：使用 SQLAlchemy 的 create_engine 函数连接 MySQL 数据库

 engine = create_engine('mysql://user:pwd@host/database?charset=utf8') 
Traceback (most recent call last):
  File "<pyshell>", line 1, in <module>
  File "<string>", line 2, in create_engine
...
    return __import__("MySQLdb")
ModuleNotFoundError: No module named 'MySQLdb'

却一个Mysqldb，网上找了很多资料，因为我这个旧的windows32位系统的原因，安装MySQLdb失败。

pandas.core.frame.DataFrame.to_sql使用SQLAlchemy库

方法三：

pip install pymysql

import pymysql

conn = pymysql.connect(host='127.0.0.1', user='root', passwd='password', db='stock')

查询数据库表大小

1、SHOW TABLE STATUS LIKE 'table_name';

2、MySQL提供了一个名为INFORMATION_SCHEMA的数据库，其中包含了系统中所有数据库、表和列的元数据信息。可以通过执行以下SQL语句来查询表的大小：

SELECT table_name, table_rows, data_length, index_length
FROM information_schema.tables
WHERE table_schema = 'database_name' AND table_name = 'table_name';

3、创建一个存储过程，用于查询表的大小：

DELIMITER //

CREATE PROCEDURE get_table_size(IN database_name VARCHAR(255), IN table_name VARCHAR(255))

BEGIN

SELECT table_name, table_rows, data_length, index_length

FROM information_schema.tables

WHERE table_schema = database_name AND table_name = table_name;

END //

DELIMITER ;

调用该存储过程来查询表的大小：

CALL get_table_size('database_name', 'table_name');

MySQL中删除数据的几种方法

【Mysql系列】mysql中删除数据的几种方法_mysql删除数据-CSDN博客

1. DELETE语句
2. DROP TABLE语句
3. TRUNCATE TABLE
4. 使用外键约束