1. MySQL Install
2. MySQL Basical Test Demonstration
import pymysql.cursors
import time
# Connect to the database
T1 = time.time()
connection = pymysql.connect(host='localhost',
user='root',
password='mysql',
db='Test',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
T2 = time.time()
try:
with connection.cursor() as cursor:
# Create a new record
sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
cursor.execute(sql, ('[email protected]', 'very-secret'))
# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()
with connection.cursor() as cursor:
# Read a single record
sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
cursor.execute(sql, ('[email protected]',))
result = cursor.fetchone()
print(result)
finally:
connection.close()
T3 = time.time()
print 'T1-T2:',T2-T1,' ','T2-T3:',T3-T2
3. Practice Project
Using the Socket TCP Protocal Transfor the data to another host and storage the data to the local mysql database.
Remote MySQL Database Code:
# -*- uft-8 -*-
import time
import socket
import pymysql.cursors
'''
CREATE TABLE `ENV_TABLE` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`Temp` int(11) NOT NULL,
`Humi` int(11) NOT NULL,
`CO2` int(11) NOT NULL,
`PH` int(11) NOT NULL,
`EC` int(11) NOT NULL,
`ControlState` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=1 ;
'''
def Insert_LocalDB(data):
# Connect to the database
try:
connection = pymysql.connect(host='localhost',user='root',password='mysql',db='Test',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
with connection.cursor() as cursor:
# Create a new record
sql = "INSERT INTO `ENV_TABLE` (`timestamp`, `Temp`, `Humi`, `CO2`, `PH`, `EC`, `ControlState`) VALUES (%d, %d, %d, %d, %d, %d, %d)"
cursor.execute(sql % (data[0], data[1], data[2], data[3], data[4], data[5], data[6]))
# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()
print('Insert Successful.')
finally:
connection.close()
def Get_LocalDB(id):
try:
connection = pymysql.connect(host='localhost',user='root',password='mysql',db='Test',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
with connection.cursor() as cursor:
# Read a single record
sql = "SELECT `*` FROM `users` WHERE `id`=%s"
cursor.execute(sql, (id,))
result = cursor.fetchone()
print(result)
finally:
connection.close()
# Connect Socket
S = socket.socket()
host = '192.168.0.104'
port = 5000
S.connect((host, port))
while True:
# cmd = raw_input('Please enter your cmd:')
cmd = 'G'
if cmd == '':
continue
print 'Sending Command!'
S.sendall(cmd)
data = S.recv(1024)
print data
if data == 'EXD':
continue
data_list = []
data_str_list = data.split('|')
for i in range(8):
data_list.append((int(data_str_list[i])))
# print data_list[i],' type:',type(data_list[i])
print data_list
Insert_LocalDB(data_list)
# print 'Insert Successful.'
time.sleep(1)
T = time.localtime(time.time())
while T.tm_sec != 1:
T = time.localtime(time.time())
time.sleep(0.8)
S.close()
User Client Code:
# -*- coding:utf-8 -*-
import socket
import urllib
import thread
import threading
import time
import json
import sys
reload(sys)
sys.setdefaultencoding('utf-+8')
host = '192.168.0.104'
print host
port = 5000
ENV_Len = 8
ENV_Status = [0,10000,2,23,43,321,498,54]
def Communicate(info):
if info == 'G' or info == 'Get' or info == 'GET':
threadLock.acquire()
ENV_DATA = ''
for i in range(ENV_Len):
ENV_DATA += str(ENV_Status[i])
ENV_DATA += '|'
threadLock.release()
return ENV_DATA
else:
return ''
def Server_Socket(host,port):
Socket_status = 'BUSY'
while True:
while Socket_status == 'BUSY':
try:
clnt = ''
addr = ''
Sk = socket.socket()
Sk.bind((host, port))
Sk.listen(1)
clnt, addr = Sk.accept()
print 'Address is:', addr
print 'I am waiting for Client...'
Socket_status = 'FREE'
pass
except Exception:
print 'Try Socket Error:',Exception
Socket_status = 'BUSY'
pass
print 'Getting a new Connect!'
while Socket_status == 'FREE':
data = clnt.recv(1024)
recv_n = len(data)
print 'Going to:', data , ' Len: ', recv_n
if recv_n == 0:
Sk.close()
print 'Server Socket Closed!'
Socket_status = 'BUSY'
break
result = Communicate(data)
if len(result) == 0:
result = 'EXD'
clnt.sendall(result)
Sk.close()
while True:
threadLock = threading.Lock()
try:
thread.start_new_thread(Server_Socket,(host,port,))
pass
except e:
print 'Thread Create Failed! Main Finished.'
break
while True:
print 'I am the main thread:', ENV_Status[0],'|',ENV_Status[1],'|',ENV_Status[2],'|',ENV_Status[3],'|',ENV_Status[4],'|',ENV_Status[5],'|',ENV_Status[6],'|',ENV_Status[7]
time.sleep(2)
threadLock.acquire()
ENV_Status[0] += 1
ENV_Status[1] -= 2
ENV_Status[2] += 3
ENV_Status[3] += 2
ENV_Status[4] += 5
ENV_Status[5] += 7
ENV_Status[6] -= 1
ENV_Status[7] += 3
threadLock.release()