http://raspberrywebserver.com/sql-databases/using-mysql-on-a-raspberry-pi.html
다운받기
mywebsql-3.6.zipmysql-5.6.26-win32 >> bin >> mysql_upgrade 클릭
>>mysql클릭
show databases; 라고 typing 해보면
=====================================
Using Mysql on a Raspberry PI
SQLite is a great database for many situations,
but there are times when it's not quite up to the job.
SQLite can be used in web sites,
but it's much more common to use MySQL. This is because
●MySQL is more scalable,
●MySQL can be tuned more easily,
●it supports user management and permissions,
●MySQL is better for sites with heavy traffic,
●it can be used in client server architectures
where a database client must access a database remotely.
사용될수있다. in 클라이언트 서버 구조에서
where a DB 클라이언트가 반드시 접속해야하는 a database에 원격으로.
Setting up MySQL on a Raspberry Pi
Before we get started,
we need to install MySQL server and the Python bindings for MySQL:
설치해야만하는 필수 프로그램들
sudo apt-get install mysql-server python-mysqldb
During the installation of MySQL server,
you will be prompted to enter a password for the MySQL root account.
설치동안 of MySQL server를,
넌 will be 제촉하게된다. to enter하기위해 PW를 for the MySQL root account를위해
I'm going to create a database to log temperatures as I did with the SQLite posts.
(▼항목추가하기(=CREATE database ??;), 확인하기(SHOW databases;))
As before, I'm going to create a database named temps to store records with fields for a date, time, zone and temperature.
Like SQLite,
MySQL comes with a shell that can be used for configuration.
We can use the the MySQL shell to create a database:
$ mysql -u root -p Enter password: mysql> CREATE DATABASE temps mysql> USE temps
The 'USE temps' command tells the shell to use that database
in future operations in this shell session.
MySQL supports users accounts,
so we need to create a database user and give it access to the database:
암호넣고 로그인후 아래 3줄을 타이핑하면된다.(대,소문자 구분)
mysql> CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'password'; mysql> GRANT ALL PRIVILEGES ON temps.* TO 'monitor'@'localhost' mysql> FLUSH PRIVILEGES; mysql> quit
This creates a user called monitor (because the database is going to be accessed by a program that monitors temperatures), and assigns it a pass word 'password'. This user is allowed to connect to the database from 'localhost'.
Initially, the new user has no privileges,
so it must be granted some access rights using the 'GRANT' command.
권한없음, so 부여되야만한다. some 접근권한이 ('GRANT'라고 타이핑할것)
I have used 'ALL' in this example,
but in real world applications
it would be better to grant users more limited rights.
A complete list of privilege options is available athttp://dev.mysql.com/doc/refman/5.1/en/grant.html.
The last command quits the shell so that we can re-enter the shell as the user that we just created:
mysql -u monitor -pNow I'm going to create a table with the fields needed for storing data:
CREATE TABLE tempdat (tdate DATE, ttime TIME, zone TEXT, temperature NUMERIC);Accessing a MySQL database with Python
I'm going to use Python to populate the database. As in the previous examples, I'm going to create sample data from one day ago, 12 hours ago and now, and I'm going to record temperatures in three different zones.
This Python code is the start of my script:
#!/usr/bin/env python import MySQLdb db = MySQLdb.connect("localhost", "monitor", "password", "temps") curs=db.cursor()I imported the MySQLdb python module and connected to it with the user name and password that I set up in the shell. The following code then inserts records into the database:# note that I'm using triplle quotes for formatting purposes # you can use one set of double quotes if you put the whole string on one line try: curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE() - INTERVAL 1 DAY, NOW(), 'kitchen', 21.7)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE() - INTERVAL 1 DAY, NOW(), 'greenhouse', 24.5)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE() - INTERVAL 1 DAY, NOW(), 'garage', 18.1)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW() - INTERVAL 12 HOUR, 'kitchen', 20.6)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW() - INTERVAL 12 HOUR, 'greenhouse', 17.1)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW() - INTERVAL 12 HOUR, 'garage', 16.2)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW(), 'kitchen', 22.9)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW(), 'greenhouse', 25.7)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW(), 'garage', 18.2)""") db.commit() print "Data committed" except: print "Error: the database is being rolled back" db.rollback()If there's an error during any of these SQL commands, or if there's an error while committing the changes, the changes will be rolled back. This can be simplified using a Python context manager:with db: curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE() - INTERVAL 1 DAY, NOW(), 'kitchen', 21.7)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE() - INTERVAL 1 DAY, NOW(), 'greenhouse', 24.5)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE() - INTERVAL 1 DAY, NOW(), 'garage', 18.1)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW() - INTERVAL 12 HOUR, 'kitchen', 20.6)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW() - INTERVAL 12 HOUR, 'greenhouse', 17.1)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW() - INTERVAL 12 HOUR, 'garage', 16.2)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW(), 'kitchen', 22.9)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW(), 'greenhouse', 25.7)""") curs.execute ("""INSERT INTO tempdat values(CURRENT_DATE(), NOW(), 'garage', 18.2)""")This will automatically handle commit and rollback operations.
Getting data from the database
Once data has been inserted into the database, we need to be able to retrieve it. We can do this using the SQL select command. The execute function runs the SQL query, and the fetchall function returns a list of records that matched the query:
curs.execute ("SELECT * FROM tempdat") print "\nDate Time Zone Temperature" print "===========================================================" for reading in curs.fetchall(): print str(reading[0])+" "+str(reading[1])+" "+\ reading[2]+" "+str(reading[3])The for loop iterates through the list of results. Each record is a list of values. Note that the time and the temperature values have to be converted to a string before they can be appended to the result string. This code prints the contents of the entire database as a table:Date Time Zone Temperature =========================================================== 2013-09-09 14:41:46 kitchen 21.7 2013-09-09 14:41:46 greenhouse 24.5 2013-09-09 14:41:46 garage 18.1 2013-09-10 2:41:46 kitchen 20.6 2013-09-10 2:41:46 greenhouse 17.1 2013-09-10 2:41:46 garage 16.2 2013-09-10 14:41:46 kitchen 22.9 2013-09-10 14:41:46 greenhouse 25.7 2013-09-10 14:41:46 garage 18.2We can use the WHERE keyword to attach conditions to a query. In this example, I'm going to search for records where the temperature is above 20.0 degrees:
curs.execute ("SELECT * FROM tempdat WHERE temp>%s", (str(20.0),))Note that it's important not to use string substitution to insert parameters into the query as this makes it easier for people to inject malicious SQL code into a query. This query does the same thing, but it's less secure:
curs.execute ("SELECT * FROM tempdat WHERE temp>%s" % str(20.0))In this example, the variable is unconditionally inserted into the query. In the previous one, the query and the parameter are passed to the MySQL library, which checks to see if the parameter is safe before inserting it.
The output from this query is
Date Time Zone Temperature =========================================================== 2013-09-09 14:41:46 kitchen 21.7 2013-09-09 14:41:46 greenhouse 24.5 2013-09-10 2:41:46 kitchen 20.6 2013-09-10 14:41:46 kitchen 22.9 2013-09-10 14:41:46 greenhouse 25.7At the end of the script, we close the connection to the database:
db.close()See also: http://dev.mysql.com/doc/refman/5.5/en/sql-syntax.html.
라즈베리파이 DB
EngineeringGarage
wget https://cayenne.mydevices.com/dl/rpi_w666zd5hsi.h
sudo bash rpi_w666zd5hsi.sh -v
