Insert filesystem image name in mysql

Question

I use Debian, and I need to capture the image names of a folder and insert the names (as string) into a mysql table.

ss:~/folder_img$
859034809583_img.jpg
458389547389_img.jpg
...

This problem has arisen now and I have no idea how to solve.

I’m not looking for a script, just a light.

Answer 1

One way would be to use a script bash assembling the commands of INSERT and passes as input for the customer mysql, as an example:

ls ~/folder_img \
| xargs -I{} echo "INSERT INTO imagens (arquivo) VALUES ('{}');" \
| mysql -u [usuario] -p [senha] 

If such a load is recurring, such as a scheduled process via crontab, it is interesting to pass the password by parameter or use another form of automatic authentication (reading a protected file, maybe), so that the mysql run without opening prompt password.

Answer 2

Assuming your image files are contained in a single directory:

$ ls -al ./folder_img/*.png 
-rw-rw-r-- 1 lacobus lacobus  349237 Jul  4 18:03 ./folder_img/alpha.png
-rw-rw-r-- 1 lacobus lacobus  312568 Jul  4 18:03 ./folder_img/beta.png
-rw-rw-r-- 1 lacobus lacobus  159315 Jul  4 18:03 ./folder_img/delta.png
-rw-rw-r-- 1 lacobus lacobus 2453952 Jul  4 18:03 ./folder_img/episilon.png
-rw-rw-r-- 1 lacobus lacobus  482526 Jul  4 18:03 ./folder_img/gamma.png
-rw-rw-r-- 1 lacobus lacobus  848590 Jul  4 18:03 ./folder_img/omega.pn

You can use the utility find to generate an SQL script from the image files contained in that directory, in a nonrecursive way. The following example saves an SQL script to the file script.sql, look at you:

$ find ./folder_img -maxdepth 1 -iname "*.png" -type f -exec \
echo "INSERT INTO tb_imagem (nome_arquivo) VALUES ('{}');" \; > script.sql

Exit (script.sql):

INSERT INTO tb_imagem (nome_arquivo) VALUES ('./folder_img/delta.png');
INSERT INTO tb_imagem (nome_arquivo) VALUES ('./folder_img/alpha.png');
INSERT INTO tb_imagem (nome_arquivo) VALUES ('./folder_img/gamma.png');
INSERT INTO tb_imagem (nome_arquivo) VALUES ('./folder_img/beta.png');
INSERT INTO tb_imagem (nome_arquivo) VALUES ('./folder_img/episilon.png');
INSERT INTO tb_imagem (nome_arquivo) VALUES ('./folder_img/omega.png');

To run the generated script in your database:

$ mysql -h [servidor] -u [usuario] -p [senha] -D [database] < script.sql