Sooner or later one of your projects is going to need to store some data; and what better place to store that data than a database? This post is going to run through the basics of what you will need to do to get started with databases on your Raspberry Pi. To do so I am going to make use of a database technology called SQLite.
SQLite is an excellent choice of database technologies to get started with on the Pi for a number of reasons:
But I suspect that SQLite will be a good fit for many projects on the Pi.
I said that it was easy to install so here goes. Boot your Pi up to the command line (or if you are already in the graphical interface open LXTerminal) and type:
So now we have installed it, the next step is to create a database. You'll be pleased to know this is equally easy. You will remember from above that I said SQLite databases were a single file. So all we need to decide is what we are going to call that file and where we are going to save it. For this example we shall call it MyFirstDatabase.db and we'll just save it in the default home folder. So still on the same command line type:
SQLite is an excellent choice of database technologies to get started with on the Pi for a number of reasons:
- It is incredibly easy to install.
- Python comes with built in support for connecting to it.
- It is free and open source.
- Databases are stored in a single file which you can directly connect to from your code, without the need of a running server process.
- The library is small.
- And yet a serious product being used in many well known products such as Mozilla's Firefox web browser. Check this page out for a full list.
But I suspect that SQLite will be a good fit for many projects on the Pi.
Installation
I said that it was easy to install so here goes. Boot your Pi up to the command line (or if you are already in the graphical interface open LXTerminal) and type:
sudo apt-get install sqlite3
Wait while it downloads and installs; and you are done.
Creating Your First Database
So now we have installed it, the next step is to create a database. You'll be pleased to know this is equally easy. You will remember from above that I said SQLite databases were a single file. So all we need to decide is what we are going to call that file and where we are going to save it. For this example we shall call it MyFirstDatabase.db and we'll just save it in the default home folder. So still on the same command line type:
sqlite3 MyFirstDatabase.db
You don't need to worry about any configuration options because there are none. You are greeted by the sqlite command prompt
and that is it; you have created your first database and are connected to it. There is one slight caveat to this. SQLite has not yet actually created the database file, so if you were to exit SQLite now and look for MyFirstDatabase.db you wouldn't find it. Why? Well we haven't created any content for our database yet and with no content what do we actually have? So let us start adding some content.
In SQLite, data is stored in data tables which conceptually look much like data tables you would encounter in the real world:
First we need to create the table and then we need to add the data into that table. To do both of these tasks we are going to use a language called SQL.
To create the table as shown type the below at the sqlite> prompt and press enter:
CREATE TABLE fruit ( name TEXT, calories INT, price NUMERIC);
Don't forget the semicolon on the end. If you do (as I always seem to) just put it on the next line and press enter. That's it, you've just created a table called fruit.
So what did we do?
Well fruit is the name of the table that we created and what we will use to refer to the table when we want to put data in and get data out of it. name, calories and price are the names of the three columns; TEXT, INT and NUMERIC are the datatypes for the corresponding columns.
Now if we exit SQLite we will find that it has created MyFirstDatabase.db. Type
.exit
and then
ls
and we can see our newly created database sitting happily in the filesystem:
Well now we have a table the next step is to get some data into it and again we are going to do this using SQL.
Reconnect to the database with the same command as before
sqlite3 MyFirstDatabase.db
Then to insert the first row of data you can see in the table above we type
INSERT INTO fruit values("Raspberry", 60, 4.99);
repeat for the next two rows
INSERT INTO fruit values("Apple", 52, 0.79);
INSERT INTO fruit values("Orange", 85, 2.5);
Great now our table has three rows of data, but how do we check what is there?
To see what data is in a particular table we use the SQL select command.
SELECT * FROM fruit;
which shows us the three rows of data we just added:
the * just means give me all columns. Alternatively if we just wanted to see a subset of columns we could have typed
SELECT name, price FROM fruit;
now we just get the name and price columns returned
What if we want to filter a particular subset of rows? For that we need a where clause
SELECT * FROM fruit WHERE price > 3;
gives us all rows where the price is greater than 3. Or
SELECT * FROM fruit WHERE name = "Apple";
returns just the Apple row of data.
The SELECT statement is really one of the cornerstones of the SQL language and these are just some very basic examples of what you can do with it.
If we want to remove data from our table then we can use the DELETE command.
which is very similar to the SELECT command so
DELETE FROM fruit WHERE name = "Raspberry";
will delete the Raspberry row
DELETE FROM fruit;
will delete all of the rows in the fruit table.
Look out for my next blog where we will look at how we can connect to our database from Python. Essential if we want to start making use of databases in our projects.
You don't need to worry about any configuration options because there are none. You are greeted by the sqlite command prompt
and that is it; you have created your first database and are connected to it. There is one slight caveat to this. SQLite has not yet actually created the database file, so if you were to exit SQLite now and look for MyFirstDatabase.db you wouldn't find it. Why? Well we haven't created any content for our database yet and with no content what do we actually have? So let us start adding some content.
Creating Your First Table
In SQLite, data is stored in data tables which conceptually look much like data tables you would encounter in the real world:
First we need to create the table and then we need to add the data into that table. To do both of these tasks we are going to use a language called SQL.
"Wait a second..." I hear you say. "I thought we were already using SQLite, now we need another SQL? And what's the difference?"
OK, good question. SQL stands for "Structured Query Language" and it is a language much like Python is a language. Though not strictly a programming language in the traditional sense, but specifically tailored for querying data. SQLite is a program which let's you run SQL commands, much like the Python editor on your desktop will let you run a Python program, but also provides the whole mechanism of what to do with those commands. SQL is found in industry applications such as SQL Server, Oracle, MySQL and many others. Every program has a slightly different flavour of SQL, but the basics will be the same across all of them and well worth your time to learn.
To create the table as shown type the below at the sqlite> prompt and press enter:
CREATE TABLE fruit ( name TEXT, calories INT, price NUMERIC);
As a helpful note, all commands in this blog post in blue text are SQL commands and should be entered at the sqlite> prompt. Any commands in red text should be entered at the standard Linux command line $ prompt.
Don't forget the semicolon on the end. If you do (as I always seem to) just put it on the next line and press enter. That's it, you've just created a table called fruit.
So what did we do?
Well fruit is the name of the table that we created and what we will use to refer to the table when we want to put data in and get data out of it. name, calories and price are the names of the three columns; TEXT, INT and NUMERIC are the datatypes for the corresponding columns.
For anyone who has used any other SQL database engines in the past it is worth noting here that SQLite uses a dynamic data type system. This means you can actually store any type of data in any column. The data types that you define when you create the table are merely recommended data types and do not enforce the type of data which can be stored in a particular column. A more detailed explanation can be found on the SQLite website here.
Now if we exit SQLite we will find that it has created MyFirstDatabase.db. Type
.exit
and then
ls
Inserting Data
Well now we have a table the next step is to get some data into it and again we are going to do this using SQL.
Reconnect to the database with the same command as before
sqlite3 MyFirstDatabase.db
Then to insert the first row of data you can see in the table above we type
INSERT INTO fruit values("Raspberry", 60, 4.99);
repeat for the next two rows
INSERT INTO fruit values("Apple", 52, 0.79);
INSERT INTO fruit values("Orange", 85, 2.5);
Great now our table has three rows of data, but how do we check what is there?
Querying Data
To see what data is in a particular table we use the SQL select command.
SELECT * FROM fruit;
which shows us the three rows of data we just added:
the * just means give me all columns. Alternatively if we just wanted to see a subset of columns we could have typed
SELECT name, price FROM fruit;
now we just get the name and price columns returned
What if we want to filter a particular subset of rows? For that we need a where clause
SELECT * FROM fruit WHERE price > 3;
gives us all rows where the price is greater than 3. Or
SELECT * FROM fruit WHERE name = "Apple";
returns just the Apple row of data.
The SELECT statement is really one of the cornerstones of the SQL language and these are just some very basic examples of what you can do with it.
Deleting Data
If we want to remove data from our table then we can use the DELETE command.
which is very similar to the SELECT command so
DELETE FROM fruit WHERE name = "Raspberry";
will delete the Raspberry row
DELETE FROM fruit;
will delete all of the rows in the fruit table.
TIP: If you want to re-add the data you just deleted, a quick way to do this is to press the up arrow on the command prompt to get back the insert commands you initially used to add the data and just press enter on each one to run that command again.
Next
Look out for my next blog where we will look at how we can connect to our database from Python. Essential if we want to start making use of databases in our projects.
I want to connect my database through a Python Program but i cannot find your next blog in which you connected the database with python program, can you please give me a direct link to that blog.. Thanks. Ahsan
ReplyDeleteI'm afraid that post is still a work in progress. Probably be completed next year now I'm afraid. Thanks Adam
DeleteGreat Article Artificial Intelligence Projects
DeleteProject Center in Chennai
JavaScript Training in Chennai
JavaScript Training in Chennai
Thanks for the post, i'll wait for next one.
ReplyDeletehttp://www.blog.pythonlibrary.org/2012/07/18/python-a-simple-step-by-step-sqlite-tutorial/
ReplyDeletethis link might help for python code working with SQLite.
Good blog post. Helpful
Thanks a lot very much for the high quality and results-oriented help. I won’t think twice to endorse your blog post to anybody who wants and needs support about this area.
ReplyDeleteBest CRM System
instagram takipçi satın al - instagram takipçi satın al - tiktok takipçi satın al - instagram takipçi satın al - instagram beğeni satın al - instagram takipçi satın al - instagram takipçi satın al - instagram takipçi satın al - instagram takipçi satın al - binance güvenilir mi - binance güvenilir mi - binance güvenilir mi - binance güvenilir mi - instagram beğeni satın al - instagram beğeni satın al - polen filtresi - google haritalara yer ekleme - btcturk güvenilir mi - binance hesap açma - kuşadası kiralık villa - tiktok izlenme satın al - instagram takipçi satın al - sms onay - paribu sahibi - binance sahibi - btcturk sahibi - paribu ne zaman kuruldu - binance ne zaman kuruldu - btcturk ne zaman kuruldu - youtube izlenme satın al - torrent oyun - google haritalara yer ekleme - altyapısız internet - bedava internet - no deposit bonus forex - erkek spor ayakkabı - webturkey.net - minecraft premium hesap - karfiltre.com - tiktok jeton hilesi - tiktok beğeni satın al - microsoft word indir - misli indir
ReplyDeleteyoutube abone satın al
ReplyDeletetrendyol indirim kodu
cami avizesi
cami avizeleri
avize cami
no deposit bonus forex 2021
takipçi satın al
takipçi satın al
takipçi satın al
takipcialdim.com/tiktok-takipci-satin-al/
instagram beğeni satın al
instagram beğeni satın al
btcturk
tiktok izlenme satın al
sms onay
youtube izlenme satın al
no deposit bonus forex 2021
tiktok jeton hilesi
tiktok beğeni satın al
binance
takipçi satın al
uc satın al
sms onay
sms onay
tiktok takipçi satın al
tiktok beğeni satın al
twitter takipçi satın al
trend topic satın al
youtube abone satın al
instagram beğeni satın al
tiktok beğeni satın al
twitter takipçi satın al
trend topic satın al
youtube abone satın al
takipcialdim.com/instagram-begeni-satin-al/
perde modelleri
instagram takipçi satın al
instagram takipçi satın al
takipçi satın al
instagram takipçi satın al
betboo
marsbahis
sultanbet
instagram takipçi satın al
ReplyDeleteucuz takipçi
takipçi satın al
https://takipcikenti.com
https://ucsatinal.org
instagram takipçi satın al
https://perdemodelleri.org
https://yazanadam.com
instagram takipçi satın al
balon perdeler
petek üstü perde
mutfak tül modelleri
kısa perde modelleri
fon perde modelleri
tül perde modelleri
https://atakanmedya.com
https://fatihmedya.com
https://smmpaketleri.com
https://takipcialdim.com
https://yazanadam.com
yasaklı sitelere giriş
aşk kitapları
yabancı şarkılar
sigorta sorgula
https://cozumlec.com
word indir ücretsiz
tiktok jeton hilesi
rastgele görüntülü sohbet
fitness moves
gym workouts
https://marsbahiscasino.org
http://4mcafee.com
http://paydayloansonlineare.com
This paragraph will help the internet visitors for building up new web site or even a weblog from start to end. 토토
ReplyDeleteI used to be suggested this blog through my cousin. I’m no longer sure whether this submit is written through him as
ReplyDeletenobody else understand such detailed approximately my trouble.
You’re wonderful! Thank you! 카지노
I savour, lead to I found exactly what I used to be having a look for. You’ve ended my four day long hunt! God Bless you man. Have a nice day. Bye 토토
ReplyDeletepond coin hangi borsada
ReplyDeleteslp coin hangi borsada
enjin coin hangi borsada
mina coin hangi borsada
sngls coin hangi borsada
win coin hangi borsada
shiba coin hangi borsada
is binance safe
is binance safe
All your hard work is much appreciated. This content data gives truly quality and unique information. I’m definitely going to look into it. Really very beneficial tips are provided here and, Thank you so much. Keep up the good works.
ReplyDelete카지노사이트
Thanks for sharing this marvelous post. I m very pleased to read this article.
ReplyDelete바카라사이트
Such an amazing and helpful post. I really really love it.
ReplyDelete토토사이트
I'm so happy to finally find a post with what I want. casino You have inspired me a lot. If you are satisfied, please visit my website and leave your feedback.
ReplyDeleteI always think about what is. It seems to be a perfect article that seems to blow away such worries. 온카지노 seems to be the best way to show something. When you have time, please write an article about what means!!
ReplyDeleteFrom some point on, I am preparing to build my site while browsing various sites. It is now somewhat completed. If you are interested, please come to play with 바카라사이트!!
ReplyDeleteI was impressed by your writing. Your writing is impressive. I want to write like you.안전놀이터 I hope you can read my post and let me know what to modify. My writing is in I would like you to visit my blog.
ReplyDeleteGreat information, thanks for sharing it with us 토토사이트
ReplyDeleteAppreciation to my father who stated to me on the topic of this weblog, this webpage is really amazing. 경마사이트
ReplyDeletePerfect piece of work you have done, this web site is really cool with wonderful info. 파친코사이트
ReplyDeletethis piece of writing is pleasant and very informative. 스포츠토토
ReplyDeleteIt's the same topic , but I was quite surprised to see the opinions I didn't think of. My blog also has articles on these topics, so I look forward to your visit.baccarat
ReplyDeleteThis paragraph will assist the internet people for creating new website or even a weblog from start to end. 카지노사이트탑
ReplyDeleteIt’s very simple to find out any topic on web as compared to textbooks, as I found this paragraph at this website. 카지노사이트
ReplyDeleteHi, yeah this article is actually pleasant and I have learned lot of things from it regarding blogging. thanks. 온라인바둑이
ReplyDeleteThat's a really impressive new idea! 메이저토토사이트추천 It touched me a lot. I would love to hear your opinion on my site. Please come to the site I run once and leave a comment. Thank you.
ReplyDeleteThere are also articles on these topics on my blog and I hope you visit once and have a deep discussion!casino api
ReplyDeletePretty useful article. I merely stumbled upon your internet site and wanted to say that I’ve very favored learning your weblog posts. Any signifies I’ll be subscribing with your feed and I hope you publish once additional soon. 메이저사이트
ReplyDeleteFairly certain he will have a good read. Thank you for sharing! 카지노
ReplyDeleteThis is very interesting, You’re a very skilled blogger. 온라인카지노
ReplyDeleteI have joined your feed and look forward to seeking more of your great post. 카지노사이트
ReplyDeleteI finally found what I was looking for! I'm so happy. 우리카지노
ReplyDeleteWhat an interesting story! I'm glad I finally found what I was looking for 메리트카지노.
ReplyDeleteI accidentally searched and visited your site. I still saw several posts during my visit, but the text was neat and readable. I will quote this post and post it on my blog. Would you like to visit my blog later? keonha cai
ReplyDeleteFirst of all, thank you for letting me see this information. I think this article can give me a lot of inspiration. I would appreciate 바카라사이트 if you could post more good contents in the future.
ReplyDeleteI no uncertainty esteeming each and every bit of it. It is an amazing site and superior to anything normal give. I need to grateful. Marvelous work! Every one of you complete an unfathomable blog, and have some extraordinary substance. Keep doing stunning 메이저사이트순위
ReplyDeleteI am someone who works on the web. Sometimes I like to visit overseas sites casino online when I have time. Among them, I like the site related to , but your site seems to be optimized for too!! Very good
ReplyDeletetiktok jeton hilesi
ReplyDeletetiktok jeton hilesi
referans kimliği nedir
gate güvenilir mi
tiktok jeton hilesi
paribu
btcturk
bitcoin nasıl alınır
yurtdışı kargo
Excellent read, I just passed this onto a friend who was doing a little research on that. And he actually bought me lunch as I found it for him smile Therefore let me rephrase that: Thank you for lunch. 메이저사이트
ReplyDeleteFirst of all, thank you for letting me see this information. I think this article can give me a lot of inspiration. I would appreciate 바카라사이트 if you could post more good contents in the future.
ReplyDeleteseo fiyatları
ReplyDeletesaç ekimi
dedektör
instagram takipçi satın al
ankara evden eve nakliyat
fantezi iç giyim
sosyal medya yönetimi
mobil ödeme bozdurma
kripto para nasıl alınır
CasinoMecca
ReplyDeleteYoure so right. Im there with you. Your weblog is definitely worth a read if anyone comes throughout it. Im lucky I did because now Ive received a whole new view of this. 메이저사이트추천
ReplyDeletebitcoin nasıl alınır
ReplyDeletetiktok jeton hilesi
youtube abone satın al
gate io güvenilir mi
binance referans kimliği nedir
tiktok takipçi satın al
bitcoin nasıl alınır
mobil ödeme bozdurma
mobil ödeme bozdurma
mmorpg oyunlar
ReplyDeleteİnstagram Takipci Satın Al
Tiktok jeton hilesi
Tiktok jeton hilesi
Sac ekimi antalya
referans kimliği nedir
instagram takipçi satın al
Mt2 pvp
instagram takipçi satın al
Royalcasino325
ReplyDeleteperde modelleri
ReplyDeleteNumara Onay
mobil ödeme bozdurma
Https://nftnasilalinir.com
ANKARA EVDEN EVE NAKLİYAT
trafik sigortası
DEDEKTOR
web sitesi kurma
aşk kitapları
That's a great article! The neatly organized content is good to see. Can I quote a blog and write it on my blog? My blog has a variety of communities including these articles. Would you like to visit me later? 토토사이트추천
ReplyDeleteSmm panel
ReplyDeleteSmm panel
iş ilanları
instagram takipçi satın al
HIRDAVATÇI BURADA
www.beyazesyateknikservisi.com.tr
Servis
Jeton Hile
That's a really impressive new idea! 안전한놀이터^ It touched me a lot. I would love to hear your opinion on my site. Please come to the site I run once and leave a comment. Thank you.
ReplyDeletebostansepeti.com
ReplyDeletesite kurma
ürünler
vezirsosyalmedya.com
postegro
sosyal medya yönetimi
surucukursuburada.com
patent sorgula
ReplyDeleteyorumbudur.com
yorumlar
tiktok jeton hilesi
mobil ödeme bozdurma
mobil ödeme bozdurma
mobil ödeme bozdurma
pubg uc satın al
pubg uc satın al
betboo
ReplyDeletesüperbahis
1xbet
bedava bonus veren siteler
bonus veren siteler
1xbet
bahigo
anadolucasino
1xbet
Decks, panels, roofing, wagons and siding are available. Secondary providers such as stucco embossing, perforating, laminating and precision curving are provided. Serves the agricultural, construction, container, oil and gas, army, mining and power era industries. In this work, yield surfaces had been measured for binary aluminum-magnesium sheet samples which had been fabricated by totally different processing paths to obtain totally different microstructures. The yielding behavior was measured utilizing biaxial compression Freediving Snorkels tests on cubic specimens produced from laminated sheet samples.
ReplyDelete