Thread: How to Automate INSERT_INTO using C?

Where are you getting your weather information from? If it's through an API for a weather service, then it seems to me that all this insertion into a database is rather unnecessary: just retrieve the data and regenerate the static page with it. You don't need to involve SQL or a CSV file.

Hi laserlight,

I always wanted to learn how to automate my website using a database. There are easier ways to do it but that is not what I am after. The time of day and the temperature at dusk and dawn can be found in two sentences on google header page for any State or City. That makes it easy for me to fill-in my csv file automatically with my program. Now all I need to know is how to insert it in the database without the use of a keyboard, but 90% of all searches is about how to connect remotely to the database using a keyboard. One thing for sure, C is more difficult than SQL because SQL was written in C.

Originally Posted by jc2020

Now all I need to know is how to insert it in the database without the use of a keyboard, but 90% of all searches is about how to connect remotely to the database using a keyboard.

Then you don't need to generate the CSV file. Have your program invoke the database API to insert the data directly to the database. Actually, if you don't need a historical record, then for mysql you should be using insert ... on duplicate key update instead.

The "90% of searches" you're talking about probably just talk about connecting to a database to enter SQL interactively because that's a great way to learn SQL; the same SQL statements can generally be applied via a programming API for the database. You have already made some progress by checking the mysql documentation on mysql_stmt_execute().

If you really do want to generate a CSV file, then perhaps you should just import it into the database, e.g., by invoking mysqlimport since you mentioned mysql.

Originally Posted by jc2020

One thing for sure, C is more difficult than SQL because SQL was written in C.

SQL was not written in C. That relational database systems tend to be written in C does not make C more difficult than SQL. They follow pretty different programming paradigms. I can show you complex use of SQL that would be terribly difficult to decipher compared to well written C but nonetheless do the job well, or I could show you plenty of C programs that are far more difficult to understand than run of the mill use of SQL.

Thanks for the detailed information with keywords that I should been using. I knew nothing about that type of API.

First of all I am sorry I use in the word SQL. I’m using mysql. Now I know what to ask about and what to search for. Also I see that mysql use c-libraries but was not written in c.

Code:

API  - # if a table is public
duplicate key – # if it is shared or owned
mysql_stmt_execute()  - >>  # must do
mysqlimport  – >> # csv yes

For now I need to stick with how to automatically import my csv file. For this question it's many hits--mostly the same:

How to run SQL script in MySQL?

Code:

1) Now, File -> Open SQL Script to open the SQL script. ...
2) After browsing .sql files, you need to select the option 
   “Reconnect to database”
3) Now, it will ask for password to connect with MySQL. ...
4) Note − Press OK button twice to connect with MySQL. ...
5) After that you need to execute the script.

Step-5 defeat the whole purpose. .. It require user interaction, somewhere, somehow..

Automation was the wrong word to use. I should have stuck with the word that most coders can relate to:

How can I run a mysql query as a cron job?
How can I set a crontab to execute a mysql query?

Not much left. It got to be a cron thing to completely eliminate user interaction. That is the question.

Going to see what bing and yahoo say.

Originally Posted by jc2020

How to run SQL script in MySQL?

As I suggested in my previous post, since you want to import a CSV file, use mysqlimport, which you can directly configure as a cron job (but note that you should not provide the password via cron; the docs I linked to suggests more secure alternatives).

Alternatively, you could directly invoke mysql to execute a LOAD DATA SQL statement as mentioned in the documentation for mysqlimport, either by manually specifying the SQL statement, or by saving it in a .sql file and feeding that to mysql (the latter tends to be less error prone), but again be wary about the password where cron is concerned.

If you really want, you can write a program that invokes the database API to run LOAD DATA, but unless you're doing it as part of a larger series of SQL statements, that seems like unnecessary work. Plus if you're going to invoke the database API, you might as well do it to insert directly into the database instead of importing a CSV file that another program you wrote generated.

There is just too much out there for so many difference flavours. Just for the record, everything here work for FreeBSD-10, 11 and 12 running mysql56 at minumum and should be the closest for all of Linux. I believe Security has been well addressed. If not please let me know. For nowe it's one down and two to go.

First we must create a database and a table before moving on:

OK let's go:

weather_am.csv

Code:

March 01 2020,Sunday,6:00am,42 F,Clear and Sunny,Houston,TX,0001

Run it from the command line for testing:

Code:

# mysql -e "LOAD DATA INFILE '/var/db/mysql_secure/weather_am.csv' INTO TABLE weather_am FIELDS TERMINATED BY ','" \
 -u root -p weather;
Enter password:
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

So the battle begin!
Inside mysql Do This:

Code:

root@localhost [mysql]> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/db/mysql_secure/ |
+------------------+-----------------------+

.

Code:

# mv weather_am.csv /var/db/mysql_secure/weather_am.csv

Code:

# chmod 700 /var/db/mysql_secure/weather_am.csv

.
In our my.cnf write these:

Code:

[client]
password="pass39485"
    
[mysql]
local-infile=1

[mysqld]
local-infile=1

.

Code:

chmod 600 my.cnf

or

Code:

chmod 400 my.cnf

.
Then run from the command-line again:
Empty return means success!

Code:

# mysql -e "LOAD DATA INFILE '/var/db/mysql_secure/weather_am.csv' INTO TABLE weather_am FIELDS TERMINATED BY ','" \
 -u root weather;
#
\quit

# ####################
# ####################

or execute it on command-line:

Code:

# ./weather.sh
#
\quit

See Result:

Code:

mysql -u root -p
Enter password: 
root@localhost [(none)]> use mysql;
Database changed
root@localhost [mysql]> use weather;
Database changed
root@localhost [weather]> SELECT * FROM weather_am WHERE 1=1;
+---------------+--------+--------+------+-----------------+---------+-------+----+
| date          | day    | ttime  | temp | view            | City    | state | id |
+---------------+--------+--------+------+-----------------+---------+-------+----+
| March 01 2020 | Sunday | 6:00am | 42 F | Clear and Sunny | Houston | TX    | 12 |
+---------------+--------+--------+------+-----------------+---------+-------+----+
1 row in set (0.00 sec)
root@localhost [weather]>

To automate, I would use cron or whatever possible.

Well it's curtains for my original program. I really feel like @laserlight have made a real programmer out of me overnight. I never read so much in my life. Now it all seem so easy.

Also I realize stripping info the way I was doing it is not good. I seriously did not take into account that the owner of that page might change his format at any time.

Where are you getting your weather information from? If it's through an API for a weather service, …
.
.
.
Then you don't need to generate the CSV file. Have your program invoke the database API to insert the data directly to the database. Actually, if you don't need a historical record, then for mysql you should be using insert ... on duplicate key update instead.

I have not completely figured out what most of this mean. I searched the keywords but found only two links about API other then what is said in the MySQL docs.One talk about the API and certificates and the other was some kind of service or program. But I did not see none of that in the doc's. Maybe I missed something.

I’m guessing the way to do this would be to contact weather.com and apply for an subscription. Would that be where the API and certificates for database access comes in? Sound like common since but sometimes I think i’m missing some screws because I'm still not sure.

I'm beginning to feel like c++

How could I ever Thank you laserlight