4

I'm currently using sqlalchemy as my package for working on MySQL databases. The databases are all large and were built before this software that is working with them, so I have not made ORM classes to interact with them in a session in sqlalchemy. I'm currently working on them by creating an engine and then using a transaction in the following way

from sqlalchemy import create_engine
mysql_engine = create_engine(**connection_parameters)
connection = mysql_engine.connect()
trans = connection.begin()
try:
  connection.execute("INSERT INTO FROM table (id, name) VALUES (1, 'foo')")
  trans.commit()
  res = conn.execute("SELECT * FROM table").fetchall()
  print(res)
except:
  trans.rollback()

Which comes partly from sqlalchemy tutorial. Is my understanding correct that the engine is mostly a template for making connections? Also, is there an advantage to using sqlalchemy for databases if I'm not utilizing ORMs and using the connections from it with raw SQL statements? Is it faster or have benefits over using something like pymysql or MySQLdb?

TheStrangeQuark
  • 2,257
  • 5
  • 31
  • 58
  • Honestly it depends on 2 factors. 1. Time: if you need to deliver something really fast use ORM, 2: complexity : Your app needs complex SQL Queries then use non ORM and also will make you fast db query returns as the database grows. – Eddwin Paz Nov 06 '18 at 14:33
  • 1
    "Your app needs complex SQL Queries then use non ORM and also will make you fast db query returns as the database grows." What about the good ORM's frameworks which also support raw queries which now most of them do nowadays ? @eddwinpaz .. "Time: if you need to deliver something really fast use ORM" Besides using a ORM not always speeds up development.. ORM's are great if they support the thing you need if not you are going to struggle to get a working solution well a great deal of the times writting a native SQL code was faster to implement. – Raymond Nijland Nov 06 '18 at 14:37
  • There's no compelling reason for or against SQLAlchemy in this scenario. You could get rid of it and use a low-level MySQL driver, but if it's working already, the benefit to removing SQLAlchemy is minimal (one fewer package to install). I'd keep it in place so your app can grow into taking advantage of the ORM features as needed. – Jim Stewart Nov 06 '18 at 14:42
  • 1
    The engine holds a pool of connections, which can reduce latency (from having to open new ones). – Ilja Everilä Nov 07 '18 at 05:27
  • I suppose this is at least related to https://stackoverflow.com/questions/494816/using-an-orm-or-plain-sql, https://stackoverflow.com/questions/15426104/why-use-sqlalchemy-is-it-very-convinent-for-coding, https://stackoverflow.com/questions/40696435/sqlalchemy-orm-vs-raw-sql-queries – Ilja Everilä Nov 07 '18 at 19:57

0 Answers0