# pandas与mysql的连接

## pandas与mysql的连接

## 1.SQLAlchemy安装

其提供了SQL工具包及对象关系映射（ORM）工具，pip命令安装：

```
pip install sqlalchemy
```

## 2.连接数据库create\_engine

```python
import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:study12345@localhost:3306/study_db')
```

## 3.查询read\_sql\_query

```python
sql = 'select * from tb1;'
# read_sql_query的两个参数: sql语句， 数据库连接；得到的df是一个datafame对象
df = pd.read_sql_query(sql, engine)
print(df)
```

## 4.创建新的表to\_sql

表名为mydf，不存在会自己创建

```python
# 新建pandas中的DataFrame, 只有id,num两列
df = pd.DataFrame({'id': [1, 2, 3, 4], 'num': [12, 34, 56, 89]})

# 将新建的DataFrame储存为MySQL中的数据表，不储存index列
df.to_sql(name='mydf', con=engine, index=False)
print('Read from and write to Mysql table successfully!')


'''
   id title author submission_date
0   1  java     蒋新      2016-05-06
Read from and write to Mysql table successfully!
'''
```

![img](/files/-LpsmZh31QHeMcTf6cAR)

## 5.将CSV文件写入到MySQL中

* 1）csv文件

![img](/files/-LpsmZh5nfOgUlhysco_)

* 2）pandas读取csv并写入book表

```python
# 导入必要模块
import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:study12345@localhost:3306/study_db')

# 读取本地CSV文件
df = pd.read_csv("book.csv", sep=',')

# 将新建的DataFrame储存为MySQL中的数据表，不储存index列
df.to_sql('book', engine, index=False)

print("Write to MySQL successfully!")
```

* 3）自动创建并显示结果

![img](/files/-LpsmZhAfvTMw1ZeXLaO)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://im-qianuxn.gitbook.io/pytorch/ji-suan-ji/numpy-pandas-matplotlib/pandas/pd-sql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.