Python Programming
Export Tables from Access to PostgreSQL
This article describes how to export a table from Access DB to postgresql via excel and python.
March 14, 2023
No Comments
Menu
- mukiraz@mukiraz.com
Many application layer protocols related to IoT devices have been developed. The commonly used IoT application layer protocols will be explained below.
The Constrained Application Protocol (CoAP) is prepared by IETF. CoAP is a Representational State Transport (REST) web transport protocol for resource-constrained devices running on an IP network. CoAP has many similarities with HTTP, but it also has some fundamental differences. Restricted devices may be large in number but are often related in function or location. For example, all light switches in a building could belong to one group, and all thermostats could belong to another group. Groups can be preconfigured before being deployed or created dynamically during operation. Suppose information needs to be sent to or received from a group of devices. Group communication mechanisms can increase communication efficiency and latency and reduce bandwidth requirements for a given application. HTTP does not support any equivalent functionality to CoAP group communication. (Rahman & Dijk, 2014 1)
Data Distribution Service (DDS) is an application layer protocol built for devices in a distributed architecture. It offers a standardized software application programming interface (API) that the DCPS (Central Data Publish-Subscribe) protocol can be used, which is responsible for providing information to subscribers. Because DDS is implemented as an “infrastructure” solution, it can be added as a communication interface for any software application. (Pardo‐Castellote, Farabaugh , & Warren, 2005 2)
MQTT is a protocol by OASIS. (Banks & Gupta, September 28, 2014 3) MQTT, publish/subscribe to a Client Server messaging transfer protocol. It is designed for use in many situations, including constrained environments such as Machine-to-Machine (M2M) and Internet of Things (IoT) contexts where a small footprint of code is required and/or network bandwidth is important.
It is a protocol prepared by AMQP and OASIS. AMQP is an application layer protocol that is open standard, message-oriented, provides reliable and secure communication, and is suitable for both point-to-point and broadcast subscription models, supporting one-time, one-time, and full-once delivery. (OASIS Advanced Message Queuing Protocol (AMQP), 2012 4)
Extensible Messaging and State Protocol (XMPP) is an open, secure, spam-free application layer protocol based on XML (eXtensible Markup Language). (P. Saint, 2004 5) Provides the following:
It is vitally important to establish an automated and efficient resource management mechanism for logging and discovering resources and services in the IoT network of many elements. We can refer to Multicast Domain Name System (mDNS) and Domain Name System Service Discovery (DNS-SD) as service discovery protocols.
mDNS is an IETF standard. If the local area network has many IoT devices and it is not possible to memorize the IP address of each IoT device, it makes sense to use the mDNS service. Allows IP addresses to be named. It works similarly to a unicast DNS server. The DNS namespace is very flexible because it is used locally without additional configuration. mDNS is the preferred method for built-in IP-based devices for automatic design, no other infrastructure required to manage devices, and high fault tolerance. (Cheshire & Krochmal, Multicast DNS, February 2013 6) Using mDNS in a set of IoT devices will be as follows: The requesting IoT device first sends IP messages to all nodes in the local domain to query identities. With this message, a client prompts different devices with a specific name to respond. After receiving requests, the target device containing its expression sends a multicast response packet containing the IP address. All devices on the IoT network store the name and associated IP address of the target device in the cache for later use of this service.
The DNS-SD protocol is also an IETF standard. DNS-SD uses standard DNS messages on IoT systems to make it easier for users to discover the services requested. The DNS-SD protocol helps connect devices without additional configuration. Service discovery in DNS-SD is a two-step process (Cheshire & Krochmal, DNS-Based Service Discovery, February 2013 6)
RFID (Radio-Frequency Identification) technology in IoT devices is used to identify objects. The RFID tag reader reads the unique tag number on an object and transmits the object name to service applications. The unique tag number on the RFID tag is the Electronic Product Code (EPC). This EPC number mainly helps identify and monitor smart elements. The EPCglobal organization is responsible for developing and managing EPC and RFID standards. (Grasso, 2004 7)
Z – Wave is a low-power communication protocol developed by Z • Wave Alliance and Sigma Designs to support up to 30 m of point-to-point communication. (Al-Fuqaha, Guizani, Mohammadi, Aledhari, & Ayyash, 2015 8)
In wireless communication, the BLE (Bluetooth Low Energy) standard, which is developed and created by the bluetooth core to use energy cost-effectively, is used by IoT devices at a distance of 100 meters. (Mackensen & Lai, 2012 9)
IEEE (Institute of Electrical and Electronics Engineers) 802.15.4 is a technical standard for the LR-WPAN (Low Rate Wireless Personal Area Network) network, which provides the functions of the physical layer and the MAC (Media Access Control) layer. (Howitt &gutierrez, 2003 10) IEEE 802.15.4 provides reliable high-data fast communication with low power consumption. (Zheng &Lee, 2006 11) Its large-scale node processing capability and low data transmission power consumption are widely used in various IoT systems.
Similarly, 802.15.4 forms the basis for developing different protocols used to support communication in IoT systems. These protocols are ZigBee, IPv6, on low-power wireless personal space networks (6LoWPAN). The physical layer of 802.15.4 is based on a direct sequence spectrum (DSSS) and can transmit data in three frequency bands (i.e., 2.4 GHz, 915 MHz, and 868 MHz). It supports communication at different data rates (40 to 250 kbps). Low frequencies cover a long distance and are good at providing better precision. On the other hand, high frequencies support low latency and high efficiency. The MAC layer of IEEE 802.1.4 is based on the CSMA/CA protocol, which can reduce the conflict of continuous concurrent transmission of multiple nodes on the network. (Gutierrez, et al., 2001 12)
Network nodes of the IEEE 802.15.4 standard can communicate in star, peer-to-peer, and cluster-tree topologies. These topologies are in Figure 2.2.
The IEEE 802.11 standard evolved to build infrastructure for low-power sensor communication technologies, resulting in the IEEE 802.11ah protocol. IEEE 802.15.4 provides low power consumption and is suitable for several IoT applications. However, the 802.15.4 protocol is not suitable for IoT applications consisting of many IoT devices distributed over larger areas. (Olyaei, Pirskanen, Raeesi, Hazmi, & Valkama, 2013 14 ) An example of a sample large-scale IoT system for IEEE 802.11ah is in Figure-2.3.
Zigbee is a low-cost, low-power, wireless network standard that targets battery-powered devices in wireless control and monitoring applications. It was created using a self-repairing mesh network that runs on the 2.4 GHz band on IEEE’s 802.15.4 protocol. (Safaric &malaric, 2006 15)
LTE–A (Long-term Evolution – Advanced Low-Speed Wireless Personal Space Network Advanced Long-Term Evolution) is also called 4G LTE. It is a standard of communication for high-speed cellular networks.
Network layer protocols are described under a separate heading.
1. Rahman, A., & Dijk, E. (2014, Ekim). Internet Engineering Task Force (IETF)-7390. https://tools.ietf.org/html/rfc7390 (Back)
2. Pardo‐Castellote, G. P., Farabaugh , B., & Warren, R. (2005). An Introduction to DDS and Data-Centric Communications. Real‐Time Innovations. (Back)
3. Banks, A., & Gupta, R. (28 Eylül 2014). MQTT Version 3.1.1. Candidate OASIS Standard 02. http://docs.oasis-open.org/mqtt/mqtt/v3.1.1/mqtt-v3.1.1.html. (Back)
4. OASIS Advanced Message Queuing Protocol (AMQP). http://docs.oasis-open.org/amqp/core/v1.0/os/amqp-core-overview-v1.0-os.html (Back)
5. P. Saint, A. (2004). Extensible Messaging and Presence Protocol (XMPP): Instant Messaging and Presence. Jabber Software Foundation. https://www.hjp.at/doc/rfc/rfc3922.html (Back)
6. Cheshire, S., & Krochmal, M. (Şubat 2013). DNS-Based Service Discovery. Internet Engineering Task Force (IETF). (Back)
7. Grasso, J. (2004). The EPCglobal network: overview of design, benefits, & security. EPCglobal, 24. (Back)
8. Al-Fuqaha, A., Guizani, M., Mohammadi, M., Aledhari, M., & Ayyash, M. (2015). Internet of Things: A Survey on Enabling Technologies, Protocols, and Applications. IEEE Communications Surveys Tutorials, 17(4), 2347-2376. doi:10.1109/COMST.2015.2444095 (Back)
9. Mackensen, E., & Lai, M. (2012). Bluetooth Low Energy (BLE) based wireless sensors. SENSORS, 2012 IEEE, 1-4. doi:10.1109/ICSENS.2012.6411303 (Back)
10. Howitt, I., & Gutierrez, J. (2003). IEEE 802.15.4 low rate – wireless personal area network coexistence issues. 2003 IEEE Wireless Communications and Networking, 2003. WCNC 2003., 1481-1486 vol.3. doi:10.1109/WCNC.2003.1200605 (Back)
11. Zheng, J., & Lee, M. (2006). A comprehensive performance study of IEEE 802.15. 4. Sensor Network Operations 4, 218–237. (Back)
12. Gutierrez, J., Naeve, M., Callaway, E., Bourgeois, M., Mitter, V., & Heile, B. (2001). IEEE 802.15.4: a developing standard for low-power low-cost wireless personal area networks. IEEE Network, 12-19. doi:10.1109/65.953229 (Back)
13. Cuomo, F., Della Luna, S., Cipollone, E., Todorova, P., & Suihko, T. (2008). Topology Formation in IEEE 802.15.4: Cluster-Tree Characterization. 2008 Sixth Annual IEEE International Conference on Pervasive Computing and Communications (PerCom), 276-281. doi:10.1109/PERCOM.2008.26 (Back)
14. Olyaei, B., Pirskanen, J., Raeesi, O., Hazmi, A., & Valkama, M. (2013). Performance comparison between slotted IEEE 802.15.4 and IEEE 802.1 lah in IoT based applications. 2013 IEEE 9th International Conference on Wireless and Mobile Computing, Networking and Communications (WiMob), 332-337. doi:10.1109/WiMOB.2013.6673381 (Back)
15. Safaric, S., & Malaric, K. (2006). ZigBee Wireless Standard. Proceedings ELMAR 2006, (s. 259-262). Zadar, Croatia. doi:10.1109/ELMAR.2006.329562 (Back)
This article describes how to export a table from Access DB to postgresql via excel and python.
In this blog post, the Flooding Attack, Decreased Rank Attack and Version Number Increase Attack in the RPL protocol were trained and detected by “Decision Tree”, “Logistic Regression”, “Random Forest”, “Naive Bayes”, “K Nearest Neighbor” and “Artificial Neural Networks” algorithms.
The test results for the attacks were compared, as a result of the comparison, the Artificial Neural Networks algorithm with an accuracy rate of 97.2% in the detection of Flooding Attacks, the K Nearest Neighbor algorithm with an accuracy rate of 81% in the detection of Version Number Increase Attacks, and the Artificial Neural Networks with an accuracy rate of 58% in the detection of Decreased Rank attacks algorithm has been found to show success.
I continue to share how I did my master’s thesis titled Comparison of Machine Learning Algorithms for the Detection of Vulnerability of RPL-Based IoT Devices, my experiences in this process, and the codes in this thesis in a series of articles on my blog.
So far, I have provided detailed information about the RPL protocol and the attacks that take place in the RPL protocol. Then, I experimented with Flooding Attacks, Version Number Increased Attack, and Decreased Rank Attack, extracting the raw data and making sense of that raw data. I compared the results of experiments with weak knots with statistical methods.
In this section, I will interpret the numerical results of the attacks we detect with machine learning algorithms.