创建python+mysql库及简单示例

1.设置MySQL数据库：

        在MySQL中创建一个数据库，并添加相应的表。可以使用MySQL工具（如phpMyAdmin）或命令行执行这些任务。

        使用 mysql-connector-python 或 pymysql 等库来连接到MySQL数据库，并使用DDL（数据定义语言）语句创建表。以下是一个使用 mysql-connector-python 的简单示例：

import pymysql
from sqlalchemy import create_engine
import pandas as pd

# 数据库连接参数
mysql_host = 'localhost'
mysql_user = 'root'
mysql_password = '123456'
mysql_db = 'test'
# 创建连接字符串 URI
uri = f"mysql+pymysql://{mysql_user}:{mysql_password}@{mysql_host}/{mysql_db}"

# # 创建连接字符串 URI
# uri = f"mysql+pymysql://{mysql_user}:{mysql_password}@{mysql_host}/{mysql_db}"

# 创建 SQLAlchemy engine
engine = create_engine(uri)

# 创建连接
db_connection = pymysql.connect(
    host=mysql_host,
    user=mysql_user,
    password=mysql_password,
    database=mysql_db
)

# 创建游标对象
cursor = db_connection.cursor()

# 创建表的DDL语句
create_table_query = """
CREATE TABLE IF NOT EXISTS example_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    column1 INT,
    column2 FLOAT,
    column3 VARCHAR(255),
    column4 DATE
)
"""

# 执行DDL语句
cursor.execute(create_table_query)

# 提交更改
db_connection.commit()

# 生成示例数据
data = {
    'column1': [1, 2, 3, 4],
    'column2': [1.1, 2.2, 3.3, 4.4],
    'column3': ['A', 'B', 'C', 'D'],
    'column4': ['2022-01-01', '2022-01-02', '2022-01-03', '2022-01-04']
}

# 创建DataFrame
df = pd.DataFrame(data)


# 将数据插入数据库表中
df.to_sql('example_table', con=engine, if_exists='replace', index=False)
# 可能的错误代码
# df.to_sql('example_table', con=db_connection, if_exists='replace', index=False)

# 查询数据表
query = "SELECT * FROM example_table"
result_df = pd.read_sql_query(query, db_connection)

# 打印查询结果
print(result_df)

# 关闭游标
cursor.close()

# 关闭连接
db_connection.close()

上述代码首先连接到MySQL数据库，然后使用DDL语句创建一个名为 your_table_name 的表，该表具有四个列：column1（INT类型）、column2（FLOAT类型）和column3（VARCHAR类型）和column4（DATE类型）。可以根据需要调整表的结构。

---

备注：BUG当出现

D:\ruanjian\pycharm\pythonProject\.venv\py_sql_test.py:58: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df.to_sql('example_table', con=db_connection, if_exists='replace', index=False)

   解决方法是将 pymysql 连接对象转换为 SQLAlchemy 的引擎（engine）对象。

    关键是将 df.to_sql() 中的 con 参数修改为 engine，以使用 SQLAlchemy 的引擎对象。这应该解决问题。

---

 各行的解析：

# 提交更改
db_connection.commit()

         这行代码的作用是将之前在数据库中进行的所有修改操作提交，使这些操作生效。在数据库中，一个事务（transaction）是一系列的数据库操作，commit() 就是将这一系列操作永久保存到数据库中的操作。

        在这个具体的例子中，当执行了创建表的DDL语句以及插入数据的操作时，这些更改并没有立即生效，而是被暂时保存在一个事务中。通过调用 commit()，将这个事务中的所有更改提交给数据库，使其永久保存。如果不调用 commit()，那么在脚本结束时，事务将会自动回滚，即取消之前的所有更改。

        总的来说，commit() 是确保数据库更改生效的关键步骤，特别是对于写入或修改数据的操作。

---

# 创建DataFrame
df = pd.DataFrame(data)

        这里的 data 是一个字典，其中包含要创建DataFrame的数据。DataFrame 是Pandas库中的一种数据结构，类似于电子表格或SQL表，它是一个二维、标记的数据结构，可以存储不同类型的数据。
       使用了一个字典 data，其中包含了如下的键值对：

data = {
    'column1': [1, 2, 3, 4],
    'column2': [1.1, 2.2, 3.3, 4.4],
    'column3': ['A', 'B', 'C', 'D'],
    'column4': ['2022-01-01', '2022-01-02', '2022-01-03', '2022-01-04']
}

        这个字典中的每个键都表示DataFrame中的一列，而对应的值是该列的数据。例如，'column1' 列包含整数值 [1, 2, 3, 4]。

        通过 pd.DataFrame(data) 这行代码，将这个字典转换为一个Pandas DataFrame对象。这样做的目的是为了方便在Python中处理和分析数据。DataFrame 提供了许多功能，可以轻松进行数据选择、过滤、转置、统计等操作，这使得在数据处理和分析中变得非常方便。在您的示例中，DataFrame df 被用于将数据插入到MySQL数据库中。

---

        

# 创建游标对象
cursor = db_connection.cursor()

# 执行DDL语句
cursor.execute(create_table_query)

# 关闭游标
cursor.close()

        游标（Cursor）是用于执行SQL语句并处理查询结果的对象。游标可以被认为是一个指向数据库结果集的指针，它允许在结果集中移动并访问数据。

        在Python中，使用数据库连接创建一个游标对象是执行SQL语句的标准做法。创建游标的主要目的是：

1. 执行SQL语句： 游标用于执行SQL语句，包括查询、插入、更新、删除等操作。

2. 处理结果集： 游标能够遍历和处理查询结果。可以通过游标获取单个行或多个行的数据，然后进行适当的处理。

---

df.to_sql('example_table', con=engine, if_exists='replace', index=False)

        这行代码使用 Pandas 的 to_sql 方法将 DataFrame 中的数据写入到数据库表中。下面是各个参数的含义和作用：

• 'example_table': 是要写入的数据库表的名称。在这个例子中，它是在数据库中创建的表的名称。

• con=engine: 指定了数据库连接。engine 是一个 SQLAlchemy 的引擎对象，它提供了与数据库的连接。

• if_exists='replace': 指定了写入表的行为。'replace' 的意思是如果表已经存在，就将其替换。其他可能的选项包括 'fail'（默认，如果表已经存在则抛出异常）、'append'（在表中追加数据）等。

• index=False: 控制是否将 DataFrame 的索引列写入数据库表中。在这里，设置为 False 表示不将索引列写入数据库表。如果设置为 True，则会将索引列作为数据库表的一列写入，默认是 True。

所以，这行代码的作用是将名为 'example_table' 的 DataFrame (df) 的数据写入到数据库表中，使用 replace 的方式，不包括索引列。这通常用于将数据从 Pandas DataFrame 导入到数据库中。