A question from Reddit’s /r/mysql:
Really new to MySQL and had a request to export an entire database to csv for review. I can manually export each table using workbench but there are 10+ tables and 10+ databases so I was looking to export the entire database to csv.
It is likely that you have additional requirements on top of this, so it would be best to script this in a way that would allow for customization.
Try this piece of Python 3.7 (or better) as a starting point. It requires
mysqlclient to be installed with pip (preferrably in a venv).
Then customize as needed.
db.use_result() normally is not recommended, because it puts additional burden on the database when handling your result set, and because you cannot jump back and forth in the result set in the client.
mysqldump-like usage as here, the default
db.store_result() won’t work, though, because it downloads the result set (here: entire tables) into client memory, one at a time, and that won’t work. So in this particular case,
db.use_result() is mandatory.
mysqldump, the option
--quick switches to streaming mode, and it is part of the
--opt set of recommended options, and they are enabled by default. When using
mysqldump --skip-quick, buffered mode is used (and your mysqldump will explode due to memory buffering requirements).