如果我的db和dataframe列的长度不同，我可以将值附加到db

Question

如果数据库和dataframe列不相等，我可以使用dataframe从dataframe添加值到db。to_sql用于追加值

数据库——

tbody> <<tr>4

A列	B列	C列
1	2	3
	5	6

只有在列允许空值的情况下才能这样做。

下面是一个完整的例子。列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),  # 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)

输出:

(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