insert data into MySQL database with bash

Question

insert data into MySQL database with bash

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (0 votes)

2

I have a script that converts me some files, I create the folders if they do not exist and move them to a desired path.

        echo "estacion: "$st;
        fcha=$year2"-"$month"-"$day;
        echo "fecha: "$fcha;

        echo $archivoF " ==> " $rutabase"datos/obs/"$st"/"$year2"/"$archivoF;
        if [ ! -d $rutabase"datos/obs/"$st"/"$year2 ]; then
          mkdir -p $rutabase"datos/obs/"$st"/"$year2;
        fi
        mv $archivoF $rutabase"datos/obs/"$st"/"$year2
        IFS='.' read -ra tipoO <<< "$archivoF"
        tipoOb='echo "."${tipoO[1]}"."${tipoO[2]}'

        echo $archivoG " ==> "$rutabase"rinex/nav/"$st"/"$year2"/"$archivoG;
        if [ ! -d $rutabase"datos/nav/"$st"/"$year2 ]; then
        mkdir -p $rutabase"datos/nav/"$st"/"$year2;
        fi
        mv $archivoG $rutabase"datos/nav/"$st"/"$year2
        IFS='.' read -ra tipoN <<< "$archivoG"
        tipoNa='echo "."${tipoN[1]}'

How I can do to insert data into my database from converted files.

The table I have has the following fields:

name VARCHAR (5) < - $ fileF, $ fileG
type VARCHAR (5) < - $ typeOb, $ typeNa
date DATE
Station VARCHAR (5) < - $ st

mysql bash

asked by Houdini 15.09.2016 в 22:01

source

3 answers

Angularjs hide and show answer form Can I do a counter in macros that registers me accounts times a macro is executed?

score 2 · Answer 1

First, you should make the inserts in SQL. That you record in an SQL file.

And from the console you can launch it like this:

/usr/bin/mysql -h localhost -u USUARIO-pTUPASSWORD TUBASEDEDATOS < /rutadondeguardartubasededatos/import.sql

score 0 · Answer 2

You can do it in a very simple way like this (modify it to exactly match your data):

echo "INSERT INTO nombre_de_tabla (nombre,tipo,fecha,estacion) VALUES ('${archivoF},${archivoG}', '${tipoOb},${tipoNa}', '${fcha}', '${st}')" | mysql -u usuario -pcontraseña -h localhost nombre_base_datos

Note that there is no space between p and the password. The fact of putting the password in the command is a security failure, but that would be another issue to be resolved separately.

score 0 · Answer 3

The SQL query can be done with the following syntax:

mysql -e "CONSULTA SQL" base_de_datos

But you also need configuration parameters that can be configured by adding:

mysql -h localhost -uusuario -pclave -e "CONSULTA SQL" base_de_datos

It should be noted that there is no separation between the parameters -u and -p and the username or password.

Although it is more convenient to configure the data in a configuration file and save it in $HOME/.my.cnf :

[client]
host     = localhost
user     = <usuario>
password = <contraseña>
socket   = /var/run/mysqld/mysqld.sock

It will give you flexibility when it comes to not having to be adding all those parameters in each call to mysql .

Thanks to this last, your line could be summarized in:

mysql -e \
  "INSERT INTO tabla
    (nombre, tipo, fecha, estacion)
  VALUES
  (
    '${archivoF//\'/\'},${archivoG//\'/\'}',
    '${tipoOb//\'/\'},${tipoNa//\'/\'}',
    '${fcha//\'/\'}', '${st//\'/\'}'
  )" base_de_datos

Bash does not have any special function to escape the special characters of a SQL query, so I used the substitution modifier of all matches (Pattern substitution, // ) that will substitute all the single quotes ( ' ) in a single escaped quote ( \' ). It is not very common that the name of a file has a quote, but you will have to take into account that detail.