Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Insert a list of dictionary using sqlalchemy efficiently

I have a list containing dictionaries as elements. All the dictionaries confronts to my schema, is there a simple or efficient way to insert these details in db with sqlalchemy?

my list is below

[{id:'12',name:'a':lang:'eng},{id:'13',name:'b':lang:'eng},{id:'14',name:'c':lang:'eng}]

and I am having a schema given below

id String(10)
name String(10)
lang String(10)
like image 971
Ishan Bhatt Avatar asked Oct 01 '14 12:10

Ishan Bhatt


People also ask

Is SQLAlchemy faster than sqlite?

Interesting to note that querying using bare sqlite3 is still about 3 times faster than using SQLAlchemy Core. I guess that's the price you pay for having a ResultProxy returned instead of a bare sqlite3 row. SQLAlchemy Core is about 8 times faster than using ORM. So querying using ORM is a lot slower no matter what.

Is it worth using SQLAlchemy?

SQLAlchemy is the ORM of choice for working with relational databases in python. The reason why SQLAlchemy is so popular is because it is very simple to implement, helps you develop your code quicker and doesn't require knowledge of SQL to get started.

What is Session flush in SQLAlchemy?

session. flush() communicates a series of operations to the database (insert, update, delete). The database maintains them as pending operations in a transaction.


1 Answers

As stated in SQLAchemy documentation, you can insert many records in your table by calling your connection.execute() method, using table.insert() and your list of records as it's parameters, like this:

connection.execute(table.insert(), [ 
        {'id':'12','name':'a','lang':'eng'},
        {'id':'13','name':'b','lang':'eng'},
        {'id':'14','name':'c','lang':'eng'},
    ]
)

Assuming, of course, that your table really has those column names as stated in your dictionary's keys and your values doesn't violate any constraint that you might have defined.

like image 118
tilacog Avatar answered Oct 27 '22 08:10

tilacog