英文:
Writing lists to database on python
问题
CAR | country | year
1 | MAZDA | India | 2022
2 |Ferrari| Spain | 2021
3 | BMW | Mexico | 2020
英文:
I have 3 lists with data like:
frame1 = [{'CAR': 'MAZDA'}, {'CAR': 'Ferrari'}, {'CAR': 'BMW'}]
frame2 = [{'country':'India'}, {'country':'Spain'}, {'country':'Mexico'}]
frame3 = [{'year': '2022'}, {'year': '2021'}, {'year': '2020'}]
I want to write lists to a database in the form:
CAR | country | year
______________________________
1 | MAZDA | India | 2022
2 |Ferrari| Spain | 2021
3 | BMW | Mexico | 2020
答案1
得分: 1
使用zip()并行循环遍历列表。然后将字典元素组合成元组，使用executemany()将它们全部插入表中。
sql = 'INSERT INTO tableName (car, country, year) VALUES (?, ?, ?)'
cursor.executemany(sql,
((v1['CAR'], v2['country'], v3['year'])
for v1, v2, v3 in zip(frame1, frame2, frame3))
)
英文:
Use zip() to loop over the lists in parallel. Then combine the dictionary elements into a tuple, and use executemany() to insert all of them into the table.
sql = 'INSERT INTO tableName (car, country, year) VALUES (?, ?, ?)'
cursor.executemany(sql,
((v1['CAR'], v2['country'], v3['year'])
for v1, v2, v3 in zip(frame1, frame2, frame3))
)
答案2
得分: 0
你的问题似乎对我来说有点模糊（至少对我来说是这样）。
但作为一个起点，使用 [tag:pandas]，你可以使用 concat/to_sql：
import sqlite3
import pandas as pd
with sqlite3.connect("foo.db") as conn:
(pd.concat([pd.DataFrame(fr) for fr in [frame1, frame2, frame3]], axis=1)
.to_sql("table", conn, if_exists="fail", index=False)) #在这里调整参数
输出：
英文:
Your question seems too vague (at least for me).
But as a starting point, with [tag:pandas], you can use concat/to_sql :
import sqlite3
import pandas as pd
with sqlite3.connect("foo.db") as conn:
(pd.concat([pd.DataFrame(fr) for fr in [frame1, frame2, frame3]], axis=1)
.to_sql("table", conn, if_exists="fail", index=False)) #adjust the params here
Output :
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论