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.