无法使用SQLAlchemy将 pandas to_sql中的表删除 [英] Cannot drop table in pandas to_sql using SQLAlchemy

问题描述

我试图删除现有表，执行查询，然后使用pandas to_sql函数重新创建表.该查询在pgadmin中有效，但在此处无效.关于这是熊猫错误还是我的代码错误有任何想法吗?

I'm trying to drop an existing table, do a query and then recreate the table using the pandas to_sql function. This query works in pgadmin, but not here. Any ideas of if this is a pandas bug or if my code is wrong?

特定错误为ValueError: Table 'a' already exists.

import pandas.io.sql as psql
from sqlalchemy import create_engine

engine = create_engine(r'postgresql://user@localhost:port/dbname')

c = engine.connect()
conn = c.connection

sql = """
drop table a;
select * from some_table limit 1;
"""
df = psql.read_sql(sql, con=conn)
print df.head()
df.to_sql('a', engine)

conn.close()

推荐答案

为什么要这样做?有一种更短的方法:to_sql中的if_exists kwag.试试这个:

Why are you doing this like that? There is a shorter way: the if_exists kwag in to_sql. Try this:

import pandas.io.sql as psql
from sqlalchemy import create_engine

engine = create_engine(r'postgresql://user@localhost:port/dbname')

c = engine.connect()
conn = c.connection

sql = """
select * from some_table limit 1;
"""
df = psql.read_sql(sql, con=conn)
print df.head()
# Notice how below line is different. You forgot the schema argument
df.to_sql('a', con=conn, schema=schema_name, if_exists='replace')

conn.close()

根据 docs :

替换:如果存在表，则将其删除，重新创建并插入数据.

replace: If table exists, drop it, recreate it, and insert data.

---

Ps.附加提示:

---

Ps. Additional tip:

这是处理连接的更好方法:

This is better way to handle the connection:

with engine.connect() as conn, conn.begin():
    sql = """select * from some_table limit 1"""
    df = psql.read_sql(sql, con=conn)
    print df.head()
    df.to_sql('a', con=conn, schema=schema_name, if_exists='replace')

因为它可以确保您的连接始终关闭，即使您的程序因错误退出.这对于防止数据损坏很重要.此外，我只用这个:

Because it ensures that your connection is always closed, even if your program exits with an error. This is important to prevent data corruption. Further, I would just use this:

import pandas as pd
...
pd.read_sql(sql, conn)

代替您的操作方式.

因此，如果我在您的位置编写该代码，则它看起来像这样:

So, if I was in your place writing that code, it would look like this:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine(r'postgresql://user@localhost:port/dbname')

with engine.connect() as conn, conn.begin():
    df = pd.read_sql('select * from some_table limit 1', con=conn)
    print df.head()
    df.to_sql('a', con=conn, schema=schema_name, if_exists='replace')

这篇关于无法使用SQLAlchemy将 pandas to_sql中的表删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！