SqlAlchemy vs psycopg2 – compare speed

There is hardly a dispute that the “wrapper” can be faster than the “wrapped” one. And how much slower the wrapper works, here it may be interesting. In this article I will give two scripts that do the same thing in two ways: SqlAlchemy and clean psycopg2

SqlAlchemy it is an ORM to simplify interaction with the database. In case of PostgreSQL, SqlAlchemy acts as a superstructure over psycopg2which in turn is a database adapter.

In the examples below, the operations are exactly the same with the only difference that in one case it is done through SqlAlchemy, in the other on psycopg2.

SqlAlchemy

The code:

def __save_operations(operations, user_id):
for operation in operations:
exist = db.sessionquery(Operation.id)filter_by(id_operation=operation[‘id_operation’])first()

if not exist:
new = Operation(
user_id=user_id,
id_operation=operation[‘id_operation’],
name=operation[‘name’],
currency=operation[‘currency’],
price=operation[‘price’],
quantity=operation[‘quantity’],
payment=operation[‘payment’],
commission=operation[‘commission’],
operation_type=operation[‘operation_type’],
date=operation[‘date’]
)
db.sessionadd(new)
log_message(operation[‘name’], ‘Added new operation’)
db.sessioncommit()

Output:

Updates start
02:41:21 | Cinemark Holdings Inc | Added new operation
02:41:21 | Macerich | Added new operation
02:41:21 | Macerich | Added new operation
02:41:21 | Macerich | Added new operation
02:41:21 | Macerich | Added new operation
02:41:21 | American Airlines Group | Added new operation
02:41:21 | American Airlines Group | Added new operation
02:41:21 | Spirit Airlines Inc | Added new operation
02:41:21 | Carnival | Added new operation
02:41:21 | Carnival | Added new operation
02:41:21 | Macerich | Added new operation
02:41:21 | American Airlines Group | Added new operation
02:41:21 | Spirit Airlines Inc | Added new operation
02:41:21 | Carnival | Added new operation
02:41:21 | Carnival | Added new operation
02:41:21 | Cinemark Holdings Inc | Added new operation
– 0.04560375213623047 seconds –

All measurements:

– 0.04560375213623047 seconds –
– 0.044077157974243164 seconds –
– 0.044499874114990234 seconds –

psycopg2

def __save_operations(operations, user_id):
“” “Add shares to DB” “”
for operation in operations:
query = “” “
INSERT INTO operations (user_id, id_operation, name, currency, price, quantity, payment,
commission, operation_type, date)
VALUES (% s,% s,% s,% s,% s,% s,% s,% s,% s,% s,% s)
ON CONFLICT DO NOTHING
; “” “

db_psycopg.query_update(query, (user_id,
operation[‘id_operation’],
operation[‘name’],
operation[‘currency’],
operation[‘price’],
operation[‘quantity’],
operation[‘payment’],
operation[‘commission’],
operation[‘operation_type’],
operation[‘date’]))
log_message(operation[‘name’], ‘Added new operation’)

def query_update(self, query, arg, message=None):
“” “Updates the data in the table and returns a success message” “”
try:
cur = selfconncursor()
cur.execute(query, arg)
return message

except (Exception, psycopg2.Error) as error:
self.__ error(error)

All measurements:

– 0.012418031692504883 seconds –
– 0.015745878219604492 seconds –
– 0.009712934494018555 seconds –

conclusions

Best result in seconds:
0.044 (SQLAlchemy) vs 0.0097 (psycopg2)

With ORM SQLAlchemy it is more convenient and faster to write code, but as you can see, it loses 3 times in speed, and in one of the cases as much as 5 times.

In my projects, where there are heavy queries, I use pure psycopg2… Where request speed is not needed – SQLAlchemy

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *