SQLAlchemy bulk insert from JSON data
When using the get()
function, SQLAlchemy is getting a dict, which is not expected in an SQL Insert function, which is producing the error.
To make it work, just pass the None
object instead of the {}
.
import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import *
basedir = os.path.abspath(os.path.dirname(__file__))
app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'main.db')
db = SQLAlchemy(app)
data = {
"all_orders": [
{
"id": 456213548,
"created_at": "2018-11-04T23:18:18-02:00",
"number": null
},
{
"id":4562222222,
"created_at": "2018-11-04T23:18:18-02:00",
"number": 1982,
}
]
}
class SalesOrders(db.Model):
__tablename__ = 'sales_orders'
order_id = db.Column(db.String(50), primary_key=True, server_default='')
order_number = db.Column(db.String(50))
created_at = db.Column(db.String(50))
def update_orders(data):
orders_entries = []
for orders in data["all_orders"]:
new_entry = SalesOrders(order_id=orders['id'],
order_number=orders['number'])
orders_entries.append(new_entry)
db.session.add_all(orders_entries)
db.session.commit()
if __name__ == "__main__":
app.run(debug=True)
Example:
>>> import app
>>> from app import *
>>> data
{'all_orders': [{'id': 456213548, 'created_at': '2018-11-04T23:18:18-02:00', 'number': 1982}, {'id': 4562222222, 'created_at': '2018-11-04T23:18:18-02:00', 'number': 1982}]}
>>> update_orders(data)
>>> SalesOrders.query.all()
[<SalesOrders 456213548>, <SalesOrders 4562222222>]
Related videos on Youtube
lowercase00
Updated on June 04, 2022Comments
-
lowercase00 almost 2 years
My first try with an ORM, trying to understand how it works, but having a bit of a challenge:
What I'm trying to do is, fit a JSON from an API into a SQLAlchemy model, and then bulk insert all entries into my database. But for some reason I'm getting an error in SQLAlchemy.
From what I understand of the error, at some point in time, when assigning the data to my model, it's being converted into a dict, when I thought it should be a list of
class objects
.My expected result is a successful insert of all the entries. Can anybody help figuring what I might be doing wrong?
Appreciate.*
The error
sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) Failed processing pyformat-parameters; Python 'dict' cannot be converted to a MySQL type
The JSON:
{ "all_orders": [ { "id": 456215587, "created_at": "2018-11-04T23:18:18-02:00", "order_number": null, }, { "id": null, "created_at": "2018-11-04T23:18:18-02:00", "order_number": 1982, }] }
The Function
def update_orders(all_orders): cursor = db_cursor() session = db_session() orders_entries = [] for orders in all_orders: order_id = orders.get("id", {}) order_number = orders.get("order_number", {}) created_at = orders.get("created_at", {}) new_entry = SalesOrders(order_id=order_id, order_number=order_number, created_at=created_at) orders_entries.append(new_entry) session.add_all(orders_entries) session.commit()
The Model
class SalesOrders(db.Model): __tablename__ = 'sales_orders' id = Column(Integer, nullable=False, index=True) order_id = Column(String(50), primary_key=True, server_default=text("''")) order_number = Column(String(50)) created_at = Column(String(100))
EDIT to make it verifiable. The function .get() is was creating a dictionary, instead of a
None
object, and SQLAlchemy wasn't able to insert a Python Dictionary.-
Ilja Everilä over 5 years"PS: Nevermind field names, I edited the code to make it smaller." is no excuse for not providing a minimal reproducible example, stressing verifiable. The code and the data you've presented contains multiple errors, but not the one that you are asking about.
-
lowercase00 over 5 yearsYou're right. I'll try to make it verifiable without pasting tons of code.
-
lowercase00 over 5 years@IljaEverilä edited it to make it verifiable and ended up finding out the problem. Thanks for the input.
-
-
lowercase00 over 5 yearsThanks. As a user pointed out above, the question was not properly verifiable, as the ID's were indeed unique. What happened was that I was using the
get()
function with{}
as the parameter, so SQLAlchemy was understanding the input as Python Dict. If you could just adjust your answer to record the "real" problem", I'll accept it. Thanks. -
simanacci over 5 yearsThe difference between
order_id=orders['id']
andorder_id=orders.get('id', default='some_value')
is the first method raises aKeyError
ifid
key is not found, while for the second it defaults tosome_value
. In your case it defaults to an empty dictionary{}
. -
lowercase00 over 5 yearsthanks, that's it. it took me a long time to realize due to lack of knowledge on the
get()
function.