用python写一个postgre传数据到mysql的案例
时间: 2023-12-06 07:45:17 浏览: 146
首先需要安装相应的Python库:`psycopg2`和`mysql-connector-python`。
然后,可以按照以下步骤将PostgreSQL中的数据传输到MySQL中:
1. 连接到PostgreSQL数据库。
```python
import psycopg2
# 连接到PostgreSQL数据库
conn_pg = psycopg2.connect(
host="localhost",
database="mydb",
user="myuser",
password="mypassword"
)
```
2. 从PostgreSQL中选择要传输的数据。
```python
# 从PostgreSQL中选择要传输的数据
cur_pg = conn_pg.cursor()
cur_pg.execute("SELECT * FROM mytable")
data = cur_pg.fetchall()
```
3. 连接到MySQL数据库。
```python
import mysql.connector
# 连接到MySQL数据库
conn_mysql = mysql.connector.connect(
host="localhost",
user="myuser",
password="mypassword",
database="mydb"
)
```
4. 将数据插入到MySQL中。
```python
# 将数据插入到MySQL中
cur_mysql = conn_mysql.cursor()
for row in data:
cur_mysql.execute("INSERT INTO mytable VALUES (%s, %s, %s)", row)
conn_mysql.commit()
# 关闭连接
cur_pg.close()
cur_mysql.close()
conn_pg.close()
conn_mysql.close()
```
完整代码示例:
```python
import psycopg2
import mysql.connector
# 连接到PostgreSQL数据库
conn_pg = psycopg2.connect(
host="localhost",
database="mydb",
user="myuser",
password="mypassword"
)
# 从PostgreSQL中选择要传输的数据
cur_pg = conn_pg.cursor()
cur_pg.execute("SELECT * FROM mytable")
data = cur_pg.fetchall()
# 连接到MySQL数据库
conn_mysql = mysql.connector.connect(
host="localhost",
user="myuser",
password="mypassword",
database="mydb"
)
# 将数据插入到MySQL中
cur_mysql = conn_mysql.cursor()
for row in data:
cur_mysql.execute("INSERT INTO mytable VALUES (%s, %s, %s)", row)
conn_mysql.commit()
# 关闭连接
cur_pg.close()
cur_mysql.close()
conn_pg.close()
conn_mysql.close()
```
阅读全文