Skip to content
Advertisement

Difference between using a database driver and subprocess [closed]

I always using a driver for database adapter because thread safety. But I have seen that some developers (specially system engineers) using subprocess for database queries, something like this:

import subprocess
command = "mysql -u username --password= blah blah query statement"
p = subprocess.Popen(command, stdout=subprocess.PIPE)
p.communicate()

What is the correct way? What is the difference in performance?

Advertisement

Answer

There are almost no advantages to using an external command. The only reason someone might use that is because they don’t quite know how to install a suitable Python library to connect to that database engine, and they used that command line in the shell before or from a shell script.

By using a native Python library instead you get

  • Connection reuse
  • Transaction support; combining multiple statements together that are committed together or rolled back if they can’t be completed.
  • Native type support, with Python types mapped to database types and vice versa.
  • Prepared statements and SQL parameters to protect your application from injection attacks and better performance

and much much more, all things that running an external process for each query doesn’t ever give you. There is no point in comparing performance here, an external child process is not going to be fast, anyway.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement