英文:
if my db and dataframe column is different length can i append the value to db
问题
如果数据库和数据框的列不相等，我可以使用DataFrame.to_sql将数据框的值添加到数据库以进行值的追加吗？
数据库 -
| 列A | 列B | 列C |
|---|---|---|
| 1 | 2 | 3 |
| 4 | 5 | 6 |
数据框 -
| 列A | 列B |
|---|---|
| 7 | 8 |
| 9 | 10 |
现在我的数据框有2列，而我的数据库有3列，我可以使用DataFrame.to_sql追加值吗？
英文:
If database and dataframe column is not in equal can i add the value from dataframe to db using DataFrame.to_sql for appending the value
DATABASE -
| Column A | Column B | Column C |
|---|---|---|
| 1 | 2 | 3 |
| 4 | 5 | 6 |
DataFrame -
| Column A | Column B |
|---|---|
| 7 | 8 |
| 9 | 10 |
now to can we my dataframe has 2 column and my db has 3 column can i append the value uing DataFrame.to_sql
答案1
得分: 1
只有在你的列允许空值时才能这样做。
这里有一个完整的示例。列 B 允许空值，而列 A 和 C 不允许：
from sqlalchemy import create_engine, MetaData, Table, Column, Integer
from sqlalchemy import insert, select
from sqlalchemy.exc import IntegrityError
engine = create_engine('sqlite://', echo=False)
meta = MetaData()
mytable = Table('mytable', meta,
Column('A', Integer, nullable=False),
Column('B', Integer, nullable=True), # 允许空值
Column('C', Integer, nullable=False)
)
meta.create_all(engine)
with engine.connect() as conn:
conn.execute(insert(mytable).values([(1, 2, 3), (4, 5, 6)]))
curr = conn.execute(select(mytable))
# 完整性错误，因为缺少 C 列
try:
df1 = pd.DataFrame({'A': [7, 9], 'B': [8, 10]})
df1.to_sql('mytable', conn, if_exists='append', index=False)
except IntegrityError as err:
print(err)
# 工作正常，因为列 B 允许空值
df2 = pd.DataFrame({'A': [11, 13], 'C': [12, 14]})
df2.to_sql('mytable', conn, if_exists='append', index=False)
# 导出数据库
df = pd.read_sql('SELECT * FROM mytable', conn)
print(df)
输出：
(sqlite3.IntegrityError) NOT NULL constraint failed: mytable.C
[SQL: INSERT INTO mytable ("A", "B") VALUES (?, ?)]
[parameters: ((7, 8), (9, 10))]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
A B C
0 1 2.0 3 # 存在的数据
1 4 5.0 6 # 存在的数据
2 11 NaN 12 # 来自 df2 的数据
3 13 NaN 14 # 来自 df2 的数据
英文:
You can only do that if your columns allow null values.
Here is a complete example. Column B allows empty values while columns A and C do not:
from sqlalchemy import create_engine, MetaData, Table, Column, Integer
from sqlalchemy import insert, select
from sqlalchemy.exc import IntegrityError
engine = create_engine('sqlite://', echo=False)
meta = MetaData()
mytable = Table('mytable', meta,
Column('A', Integer, nullable=False),
Column('B', Integer, nullable=True), # Allow null values
Column('C', Integer, nullable=False)
)
meta.create_all(engine)
with engine.connect() as conn:
conn.execute(insert(mytable).values([(1, 2, 3), (4, 5, 6)]))
curr = conn.execute(select(mytable))
# Integrity error, failed because C is missing
try:
df1 = pd.DataFrame({'A': [7, 9], 'B': [8, 10]})
df1.to_sql('mytable', conn, if_exists='append', index=False)
except IntegrityError as err:
print(err)
# Worked because B allows null values
df2 = pd.DataFrame({'A': [11, 13], 'C': [12, 14]})
df2.to_sql('mytable', conn, if_exists='append', index=False)
# Dump database
df = pd.read_sql('SELECT * FROM mytable', conn)
print(df)
Output:
(sqlite3.IntegrityError) NOT NULL constraint failed: mytable.C
[SQL: INSERT INTO mytable ("A", "B") VALUES (?, ?)]
[parameters: ((7, 8), (9, 10))]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
A B C
0 1 2.0 3 # existing data
1 4 5.0 6 # existing data
2 11 NaN 12 # data from df2
3 13 NaN 14 # data from df2
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论