主页 / 问题 / 1591684
Accepted
Zoombi_89_adm
Asked:2024-08-23 20:11:41 +0000 UTC2024-08-23 20:11:41 +0000 UTC

将字典列表添加到 sqlite3 python 数据库

  • 772

在尝试掌握数据库使用时，我遇到了将字典列表放入数据库文件的问题。我尝试了一下，文本给出了错误。

sqlite3.OperationalError：没有这样的列：

with sqlite3.connect('new_.db') as db:
    cursor = db.cursor()
    query = """
            CREATE TABLE active 
            (price_bue TEXT)
            """
    cursor.execute(query)
    query = f''' INSERT INTO active VALUES ({str(data)})'''
    cursor.execute(query)
    db.commit()

我尝试将其转换为字节，但也收到错误。

sqlite3.OperationalError：表活动已存在

with sqlite3.connect('new_.db') as db:
    cursor = db.cursor()
    query = """
            CREATE TABLE active 
            (price_bue BLOB)
            """
    cursor.execute(query)
    query = f''' INSERT INTO active VALUES ({dumps(data).encode('utf-8')})'''
    cursor.execute(query)
    db.commit()

需记录的数据：

data = [{'price': 588.4, 'quantity': 0.0086, 'actual cost': 5.065300239999999},
        {'price': 589.2, 'quantity': 0.0086, 'actual cost': 5.07218712},
        {'price': 590.0, 'quantity': 0.0086, 'actual cost': 5.079073999999999},
        {'price': 590.8, 'quantity': 0.0086, 'actual cost': 5.08596088},
        {'price': 591.7, 'quantity': 0.0086, 'actual cost': 5.093708620000001},
        {'price': 592.6, 'quantity': 0.0086, 'actual cost': 5.101456359999999},
        {'price': 593.5, 'quantity': 0.0086, 'actual cost': 5.1092040999999995}]
python

2 个回答

  1. Best Answer

    有一个字段可以用来选择条目，对吗？

    import sqlite3


data = [{'price': 588.4, 'quantity': 0.0086, 'actual cost': 5.065300239999999},
        {'price': 589.2, 'quantity': 0.0086, 'actual cost': 5.07218712},
        {'price': 590.0, 'quantity': 0.0086, 'actual cost': 5.079073999999999},
        {'price': 590.8, 'quantity': 0.0086, 'actual cost': 5.08596088},
        {'price': 591.7, 'quantity': 0.0086, 'actual cost': 5.093708620000001},
        {'price': 592.6, 'quantity': 0.0086, 'actual cost': 5.101456359999999},
        {'price': 593.5, 'quantity': 0.0086, 'actual cost': 5.1092040999999995}]

with sqlite3.connect('new_.db') as db:
    cursor = db.cursor()
    query = """
            CREATE  TABLE  IF NOT EXISTS active 
            (price_bue TEXT)
            """
    cursor.execute(query)
    query = f''' INSERT INTO active VALUES (?)'''
    cursor.execute(query,(str(data),))
    db.commit()

    res= cursor.execute('select * from active').fetchone()
    print(res)

    ("[{'price': 588.4, 'quantity': 0.0086, 'actual cost': 5.065300239999999}, {'price': 589.2, 'quantity': 0.0086, 'actual cost': 5.07218712}, {'price': 590.0, 'quantity': 0.0086, 'actual cost': 5.079073999999999}, {'price': 590.8, 'quantity': 0.0086, 'actual cost': 5.08596088}, {'price': 591.7, 'quantity': 0.0086, 'actual cost': 5.093708620000001}, {'price': 592.6, 'quantity': 0.0086, 'actual cost': 5.101456359999999}, {'price': 593.5, 'quantity': 0.0086, 'actual cost': 5.1092040999999995}]",)

    TEXT是你的选择，你也可以使用BLOB，

    import json


#raise TypeError(f'the JSON object must be str, bytes or bytearray, '
#TypeError: the JSON object must be str, bytes or bytearray, not tuple
#Expecting property name enclosed in double quotes По этому
res = res[0].replace("'", '"')

l_dict=json.loads(res)

for i in l_dict:
    print(i)

    {'price': 588.4, 'quantity': 0.0086, 'actual cost': 5.065300239999999}
{'price': 589.2, 'quantity': 0.0086, 'actual cost': 5.07218712}
{'price': 590.0, 'quantity': 0.0086, 'actual cost': 5.079073999999999}
{'price': 590.8, 'quantity': 0.0086, 'actual cost': 5.08596088}
{'price': 591.7, 'quantity': 0.0086, 'actual cost': 5.093708620000001}
{'price': 592.6, 'quantity': 0.0086, 'actual cost': 5.101456359999999}
{'price': 593.5, 'quantity': 0.0086, 'actual cost': 5.1092040999999995}
    • 0

  2. 带模块的MB

    import sqlalchemy
engine = sqlalchemy.create_engine("sqlite:///:memory:", echo=True) 
#dialect[+driver]://user:password@host/dbname
engine.table_names()
#['table']

conn = engine.connect()

meta = sqlalchemy.MetaData()
table = sqlalchemy.Table(
   'table', meta, 
   sqlalchemy.Column('culumn_name', sqlalchemy.String) #maybe need price,quantity, actual cost то есть создать три столбца?
)


meta.create_all(engine)
#CREATE TABLE "table" (column_name VARCHAR, PRIMARY KEY (id))

conn.execute(table.insert(), [{"column_name":'Beta'}])#то есть вставляет список словарей соответсвующих структуре таблицы
    • 0

相关问题