Python equivalnt for a dbq?

Question

With bash I can extract the whole database with this command:

DataBaseQuery="SELECT * FROM my_database_list.database"
mysql -uUSER -p'PASSWORD'<< EndOfFile 
$DataBaseQuery
EndOfFile

What would be the python equivalent of this command?

possible duplicate of [How do I connect to a MySQL Database in Python?](http://stackoverflow.com/questions/372885/how-do-i-connect-to-a-mysql-database-in-python) — Wieland, Apr 16 '15 at 06:59

score 2 · Answer 1 · answered Apr 16 '15 at 05:45

2

from subprocess import Popen, PIPE

# Create a new process with the provided arguments:
# - the command itself and its arguments as a list
# - create a new pipe for STDIN and STDOUT and STDERR
cmd = Popen(
    ['mysql', '--user=USER', '--password=Password'],
    stdin=PIPE,
    stdout=PIPE,
    stderr=PIPE,
)

# Feed the query into STDIN and read STDOUT/STDERR into
# the appropriate variables, which are returned as strings
stdout, stderr = cmd.communicate(
    'SELECT * FROM my_database_list.database'
)

answered Apr 16 '15 at 05:45

Nathan Osman

71,149
71
256
361

It'd be worth mentioning `subprocess.check_output()`, which raises an exception on error. – Andrea Corbellini Apr 16 '15 at 08:40
But then you can't use `Popen.communicate` since `check_output` is a blocking call. – Nathan Osman Apr 16 '15 at 17:26
[check_output](https://docs.python.org/dev/library/subprocess.html#subprocess.check_output) accepts `input` as an argument. – Andrea Corbellini Apr 16 '15 at 19:23
@AndreaCorbellini heh - I didn't realize that. Good call. – Nathan Osman Apr 16 '15 at 19:34

Andrea Corbellini · Answer 2 · 2015-04-16T19:33:02.743

1

You can use subprocess.check_output():

import subprocess
result = subprocess.check_output(
    ['mysql', '-uUSER', '-pPASSWORD'],
    input=b'SELECT * FROM ...')

You must be aware of a problem, however: both with this solution and with the Bash solution, you are specifying the password on the command line. An unprivileged user may use ps to get the password of your database, thus decreasing security.

Also, note that with Python you can choose from a number of database drivers (such as MySQLdb) and Object Relational Mappers (such as SQLAlchemy). These drivers/ORMs solve the password problem and offer you easy ways to parse results from queries, along with other benefits.

Check out this example from MySQL offical documentation to see how convenient a database driver is.

edited Apr 16 '15 at 19:33

answered Apr 16 '15 at 19:27

Andrea Corbellini

17,339
3
53
69

Thanks for mentioning the `ps` issue - I noticed that too but forgot to mention it in my answer. I believe the `mysql` command will read the password from STDIN if the password is not included and the `-p` flag is supplied. – Nathan Osman Apr 16 '15 at 19:37
@NathanOsman: actually I believe that `mysql` reads from the tty, rather than from the standard input, to avoid echoing the password to the terminal. – Andrea Corbellini Apr 16 '15 at 19:39
Ah, okay. That's good to know. It looks like Python provides a [module](https://docs.python.org/2/library/pty.html) for creating a process and obtaining access to its TTY. But as you've mentioned - it's probably easier to use one of the many Python MySQL libraries. – Nathan Osman Apr 16 '15 at 22:54

Python equivalnt for a dbq?

2 Answers2