The question is published on by Tutorial Guruji team.
I’m running a python script which returns several lines of text, which I use the following truncate command tr and want to add to my database.
I start here: this removes the unformatted line delimiters.
tr -d '1532' < long_text > unixfile.txt
I’m then left with an output which looks like this:
Happy Birthday Stackoverflow Happy Birthday to you Happy Birthday Stackoverflow
I use the following command to place this into a variable:
lyrics=$(cat unixfile.txt)
mysql --user=USER --password=PASSWORD --database='DB' --execute='INSERT INTO `song_lyrics` (`id`, `song_id`, `lyrics`, `info`) VALUES ('"'$i'"', '"'$i'"', '"$lyrics"', '0');'
ERROR 1064 (42000) at line 1:
It seems that MySQL is seeing my entire 3 line string (shown above) in three different commands, because the output of the unixfile.txt appears in the error output.
Answer
This problem can be solved using the following:
Ensure the long text string is being captured in a file, instead of a variable – then re-capture the file/variable.
lyrics=$(cat unixfile.txt)
mysql --user="$user" --password="$password" --database="$my_db" <<END
use my_db;
INSERT INTO song_lyrics (id, song_id, lyrics, info) VALUES ("$i", "$i", "$lyrics", '0');
END
this method ensures the variable is passed properly.
the only remaining issues would be with various types of punctuation although this post does not describe this.