Integrate #SQLite #database with #nodered | #beginners

foreign so the first thing that we are going to do is to create other required tables and to do that I'm just getting into the folder where I'll create my TV file uh so this already I have created but uh a new one through that I'll just use escalate 3 and let's say create a new one database sensors.tp and here I'll create a new table for sensors to do that I'll use this comment or query basically correctable sensors ID into the primary key whatever comment then the sensor name value unit and time stop just hit enter and now I should get the table created okay so to use SQL 3 you need to have this installed so I'm over here using a Raspberry Pi and here then you can check out my other tutorial on this for which I have given the link Down Below in the description now we are done with this uh now let's get back to node red and we'll see how we can you know interact with this table that I created here now note that uh by default it doesn't support uh escalate to add the support you need to go to manage palette then sqlite and should use this one node red Rescue light click install now what we are going to use the lso and we should have this census DB okay now here add the escalate node your database change this now like for me and then sqlite slash hit and done and equal you should see something like this now what I'm going to do is I'm just going to use the fixed statement over here and that is Select star from sensors done I'll add a debug note to print out the result and this should add an array of rows from the database and we should also have a trigger that can trigger this query that's deploy open the debug pin click this and you see there's no rules basically on the table that's why we are getting it ft now let's try to insert some data I'll just clone this and over here instead of fixed statement I am going to we have message to topic and to use that we need a function mode I think that's it message dot topic and this will basically have a query so insert into sensors so I have that ready actually I can just copy this and you can find the link to this gist over here in the description here twist it is stop so we should have this what we can do is scale okay now we can just hit well one and I do [Music] we can right click if values otherwise we can pass it as parameters and now we need to pass the values using message dot payload doing this way you can always you know input the payload from another node and that will also help you in certain values this is going to be an array and the first thing is the sensor name three models and the value and then uh value 3 is basically to the unit so let's say kilowatt then that's that's it and then we need a insect node to trigger the insertion now let's trigger this foreign [Music] okay so this is one of the way that uh you know you can insert record into the table and there's another way that is called as a prepared statement and to do that I'll just copy these three nodes and of layer what you can do is prepared statement and here what you can do is best video okay now in the function node we need to set message.parents and that is going to be an object but and for that we will have Bell one and value so this T value pair already depends whatever variable or parameter you have given in the prepared statement so this will change accordingly [Music] let's I'll make it to four then put it done deploy now let's check fetch records and you can see we should get the latest one here sensor Four B [Applause] so there are two ways and there's another way that also we can use uh using batch uh queries basically so over here you can use batch without response so we will not get any output out of it when running the query but that will help you you know something like if you are trying to insert multiple records uh you know anytime that you can use so basically in that case the message dot topic will have string of queries and that it will basically you know help you insert data into the table so let's test that now we have got four records so let's try to add two oh sorry [Music] so just to try to escape this okay done deploy we should have six records now and yeah you can see I've got six books so these are three ways uh that you can run queries on the sqlite connection okay so it gonna try out some other queries uh you can have different select treatment and all that that you can do so that's for [Music] so that's all for today guys uh hope you have liked this video if so hit the thumbs up button and do subscribe to our channel for videos like this see you in our next video till then bye [Music] [Music] foreign

Channel: WGLabz

Views: 4,666

Keywords: oksbwn, WearGenius, bikash panda, bikash home automation, Raspberry pi, arduino, wglabz, sqlite nodered, sqlite node red, node red sqlite database, node red sqlite example, how sqlite node red, how to sqlite node red, where sqlite node red, node-red, sql, sqlite, nodered sqlite, node red sqlite, node red sqlite raspberry pi, node red sqlite prepared statement, how to node red sqlite, how node red sqlite, what node red sqlite, iot, tutorial

Id: qOXy4FOW410

Length: 11min 43sec (703 seconds)

Published: Fri Mar 17 2023