如果我的数据库和数据框的列长度不同,我能将值附加到数据库吗?

huangapple go评论92阅读模式
英文:

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 不允许:

  1. from sqlalchemy import create_engine, MetaData, Table, Column, Integer
  2. from sqlalchemy import insert, select
  3. from sqlalchemy.exc import IntegrityError
  4. engine = create_engine('sqlite://', echo=False)
  5. meta = MetaData()
  6. mytable = Table('mytable', meta,
  7. Column('A', Integer, nullable=False),
  8. Column('B', Integer, nullable=True), # 允许空值
  9. Column('C', Integer, nullable=False)
  10. )
  11. meta.create_all(engine)
  12. with engine.connect() as conn:
  13. conn.execute(insert(mytable).values([(1, 2, 3), (4, 5, 6)]))
  14. curr = conn.execute(select(mytable))
  15. # 完整性错误,因为缺少 C 列
  16. try:
  17. df1 = pd.DataFrame({'A': [7, 9], 'B': [8, 10]})
  18. df1.to_sql('mytable', conn, if_exists='append', index=False)
  19. except IntegrityError as err:
  20. print(err)
  21. # 工作正常,因为列 B 允许空值
  22. df2 = pd.DataFrame({'A': [11, 13], 'C': [12, 14]})
  23. df2.to_sql('mytable', conn, if_exists='append', index=False)
  24. # 导出数据库
  25. df = pd.read_sql('SELECT * FROM mytable', conn)
  26. print(df)

输出:

  1. (sqlite3.IntegrityError) NOT NULL constraint failed: mytable.C
  2. [SQL: INSERT INTO mytable ("A", "B") VALUES (?, ?)]
  3. [parameters: ((7, 8), (9, 10))]
  4. (Background on this error at: https://sqlalche.me/e/14/gkpj)
  5. A B C
  6. 0 1 2.0 3 # 存在的数据
  7. 1 4 5.0 6 # 存在的数据
  8. 2 11 NaN 12 # 来自 df2 的数据
  9. 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:

  1. from sqlalchemy import create_engine, MetaData, Table, Column, Integer
  2. from sqlalchemy import insert, select
  3. from sqlalchemy.exc import IntegrityError
  4. engine = create_engine('sqlite://', echo=False)
  5. meta = MetaData()
  6. mytable = Table('mytable', meta,
  7. Column('A', Integer, nullable=False),
  8. Column('B', Integer, nullable=True), # Allow null values
  9. Column('C', Integer, nullable=False)
  10. )
  11. meta.create_all(engine)
  12. with engine.connect() as conn:
  13. conn.execute(insert(mytable).values([(1, 2, 3), (4, 5, 6)]))
  14. curr = conn.execute(select(mytable))
  15. # Integrity error, failed because C is missing
  16. try:
  17. df1 = pd.DataFrame({'A': [7, 9], 'B': [8, 10]})
  18. df1.to_sql('mytable', conn, if_exists='append', index=False)
  19. except IntegrityError as err:
  20. print(err)
  21. # Worked because B allows null values
  22. df2 = pd.DataFrame({'A': [11, 13], 'C': [12, 14]})
  23. df2.to_sql('mytable', conn, if_exists='append', index=False)
  24. # Dump database
  25. df = pd.read_sql('SELECT * FROM mytable', conn)
  26. print(df)

Output:

  1. (sqlite3.IntegrityError) NOT NULL constraint failed: mytable.C
  2. [SQL: INSERT INTO mytable ("A", "B") VALUES (?, ?)]
  3. [parameters: ((7, 8), (9, 10))]
  4. (Background on this error at: https://sqlalche.me/e/14/gkpj)
  5. A B C
  6. 0 1 2.0 3 # existing data
  7. 1 4 5.0 6 # existing data
  8. 2 11 NaN 12 # data from df2
  9. 3 13 NaN 14 # data from df2

huangapple
  • 本文由 发表于 2023年1月9日 16:21:53
  • 转载请务必保留本文链接:https://go.coder-hub.com/75054672.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定