 Now we are gonna have Maria Andrea she's gonna present over SQL light and I know super aunt. How are you Andrea? Well, I'm here. I'm little nervous like I think everybody and I'm very happy to be here very happy Okay, I think everybody everything is going to be okay. Yes And where are you streaming from? I Am streaming from Argentina Really north of Argentina resistance a chaco So I don't know We are everywhere we are the Argentinians Yeah Okay, well if you are ready go ahead and start with good luck and thank you very much Okay, thank you. Good luck. You do I am going to talk about Very well known a little database This little database has a lot of beautiful and interesting features He is just you just use it that you don't need to configure it. You just put this in the executable in the binary executable into your Folder and everything is okay It's free for all users because it's public domain It's compact. It's fast It's the most implemented Database in the world because it's in every file for chrome Android Is more is the most user database and it's very reliable is one of the best tested software in the world to execute has a lot of uses He is used as very used in data analysis because it's a very well known file format and it's Practical to exchange information Is used as the base of proprietary file format? I have used Autopsy forensic software for example and it uses SQLite as his base format He is used as a cache or temporary or in memory database It's more the small free the full free and the ability to use memory as his Source is one of the things that allows that He is used to create single user versions of the And Different software with the user software or for educational Proposes because it's one of the easiest to uh start to use or to do experiments and He really it really shines in embedded mobile and iot war The SQLite of course is not a server database Is not well suited for many concurrent writer Or when you have very big databases Or when your databases are accessed by network So there are some See best practice SQLite you when you connect to SQLite one of the Important interesting thing that you can use row factory row factory. There are some row factory, but the most practical in Python is SQLite row SQLite row gives you the ability to access Every field using the field name and not having to Cure to the index number which is more difficult to debug and understand Another thing is you should use Context context manager in the in this case are used as To to envelope to transfer a transaction. So when you Start your context manager, you start the transaction And when you end the project manager the content manager ends So if everything inside the context manager goes well The transaction is commit and if Some exception occur or or something Stop the context manager to end in the correct way The transaction will be rolled back. Okay Another thing important very important is to use placeholder Placeholder instead of you creating your string using format or f strings or other things Because when you use this kind of Thing you can Incure in conversion errors because sometimes use a in your tests You use just letters or numbers, but sometimes when you have to use your real data work Real data you Maybe have a special characters or maybe So it can have conversion problems Another thing is that it's very important An easy way to prevent SQL injection Okay, another things that is I just See you is you prefer to use directly the connection sometimes Of course, you need the cursor to read all your Rows and the course service It's just for there But where you can you may prefer Execute using the connection you can execute the connection user your sequel Sentences and variables a table with the values To put inside your sequel or you can use execute many execute many For example, if you have a lot of insert Then there are a list of and tuples of any with The values of every one of these inserts Or you can use execute script Execute the script is very useful when you have the situation that you want to Create in this creating priority tables or create just the scheme of the database Totally because you will prefer this So you have one sequel sentences And after the other Okay Another thing that I'm going to see you is that Is that you can configure some option that I'm going to show you some important options Next Using the pragma Sentences the pragma sentences is When you use some option and you put this option you sense option to some value The the schema is when you Uh And you will refer to some database And you can open many database in one connection using the attached sentences So in this case you need to see to explicitly say with one database is what you want to Uh change the configuration, but normally you just use your default database Okay Next I'm going to talk about in very very important things in databases You need to ensure that you are going to have consist consistency For example, if you have Uh One transaction and you and some people give money to the other you cannot Uh subtract from one account and add to the other and and not adding to the other account or this verse Add from one account and subtract it and I'm subtracting for the other because you are or destroying or creating money And this is not allowed So, uh, you need to ensure that everything is okay uh Transactions as you are in This database that is performed the acid The the principle the principle means Atomicity atomicity means that everything Is every every operation inside that this transaction is correctly Done or known What everyone or known Uh In isolation means that nobody is going to Read transaction inside While it is happening because it's going to read an inconsistent data Um See Consistency everything must have be consistent in every time in the every point of the time And durability Which means that uh the data must be stored securely Okay When you, uh, you set up a uh use SQLite you can use the pragma the pragma is your normal Your normal has uh this value of what what means write a head login write a head login uh is Means that uh, you use two Files inside your file system one is the database SQLite database And the other is the login they want right a login When somebody wants to write In the database he It uh the writer writes into the logger The logger file note that directly to the database. So everybody is Reading in the database after that the After finishes the the transaction finished The underwriter uh close it in some period of time the login is Writed back to database and the Data is Stored here stored there This is this allows Uh more concurrency because you have many readers Readers can use the database Easily and one writer Is uh into there right into the loading Another way to use roller boats In uh SQLite is using a rollback files rollback files if the more traditional is Older than the other and If you just write Directly into the data file and The journal is used to Store the previous state of every data. So when you If the transaction will fall back The journal is right again Into the data and return everything in the previous state Uh, you can use a journal in delet Or in truncate or in persist delet means that the journal after after used every every transaction is finished at the journal it lets But truncate means that After every transaction is finished and journal is uh Set to zero length or persist means That the journal is Stay stay there, but uh overrided with with other values um another way to use it is uh In memory in memory is very use Useful because many times you use uh this database to Get perform cache or Or indexing another data or something like that and you need maximize the speed of creation uh Of course, this means that everything is with iris of corruption. So this is not Superval force situation when you need to Uh, to ensure the durability of this data okay Isolation level isolation levels means about when everyone Every older user with the connection can uh read the data that the writer is Updating Normally, uh When you open a connection with sqlite you use a Uh check in the same thread by default means that you have only one thread using this connection, but you can share with older threads changing these options Isolation normally is exclusive which means that only one user is Using the database and only and She or he is the only one that can read or write inside this database The next isolation level is immediate uh, the user request writing on But when the user request writing every operation is completed real or right operation The database is locked for everyone and After the writer finish the update Release the database to use general the ferret is Allows you in more concurrency because The writer request permission to write It puts the database in real only mode that readers can Can Perform everything uh, hello readings in After that after the writer finish writing the database is free for older writers Okay index In database, uh, you Just need to index index. Uh Have a you very important Food in Relation with a performance the performance can change a lot You're using the right index You need to index the fields that you use for searches Or the fields that can be used for relationship for when you have one table that are related to the older You need to avoid to think Is uh carefully about what to index because if you over index your writing will be slower And then this is a little problem Uh, so you need to avoid over indexing Okay, there are some special case index that circulate Super for example, you can create partial index partial index means that not every row In a table is indexed in this guy in this example. I bring you Uh, this is just tm leaders So just uh rows that has tm leader in true are indexed This is useful. Uh, when you want to avoid to Over indexing because we need just some uh rows Uh another Interesting and a special case use is what you want to Index over a deterministic function. Maybe you have for example the eighth and the way of Some rectangle and you want to index Using the area or maybe you want something that is um calculated from Older fields inside this table Uh deterministic means that when you know your input you Has one and just one output for example Not a non deterministic function by definition is the random function when you have some some range And you don't know With output will be uh another special case Is full text search full text search When you need to uh index Text that are uh fields that has a lot of text a lot of words And you uh Can use full text in this this is a plugin, but is you said And But it is uh available in all uh platforms Windows Linux, so you can use it easily you don't need to Compile the database And it's available. You can use The match operator to find Worse inside the text is very very fast And compare it to like operation for example Okay Finally we're going to talk a little about uh data types Uh sequo light has uh some standard data types Strings integers floats And the uh binary object like Large binary officer or jason Uh Day time uh you are in data and sequo light can store them like strings But Uh And you have a lot of uh conversions functions conversion functions allow you to uh convert Uh Dates uh into uh Strings and vice versa For example here you use the function date He you you put for example now and after that you see that you fetch A string representing the day That the date the today This has uh you can uh change them change this kind of function Uh you see for example here I am uh calculating the third sunday on october And in the third sunday on october for example, I start by now Then I go to the start of the year After that I add nine months After that I add 40 days and After that I Seek the next weekday so weekday which is sunday Uh the sunday so you can uh see that you can really have a lot of variation You see this kind of functions and perform some perfect It performs some calculus and some functions um Another way is to use a declare Part of the cloud type Part of the cloud type is an option and you need to use it When you create your database Uh, for example here, uh, we open the connection using sqlite parts the cloud types Are you executed uh declaring that the path is a time stamp? Then you insert just using the daytime uh in the The full library in python, so you import the time and you can use it And the uh data is stored In a binary format And you can fetch it in the same Format that you stored Well, uh with this uh, we are just have See some of the interesting features of these little database Uh, I start with some best practices Uh, I just show you some transaction isolation and concurrency options that sqlite has Uh sqlite has can use concurrency is not Uh at the same level that uh some server database that you can use concurrency Indexing he has a really little Really uh advanced index indexing capabilities And after that I show you a data date Types and uh how to use a date and time in the The data and time date, okay Uh with this I finish, uh Uh I just uh think everything Okay questions Hello Hey, Maria. Thank you so much. Uh, okay. We have a time left For a bunch of questions It was really nice. I've been using a sqlite as well for some projects and every day I found how A powerful it is indeed uh So The first one is could you give some concrete examples of places where you have used sqlite? Well, I have been in Cedepedia I haven't uh using it in a project Uh cedepedia and uh, we just scrap a lot A lot of pages H HTML Pages and We use it to Create the index and really perform very well And we need we need to you need to know that it is going to be distributed in Argentina as The logical material so we need to ensure that this thing And this the software Runs in a very old and very little computers as our students that our uh Poor students can have So it is Help us very very good Nice, thanks. I'm the other question What's the benefit of immediate over deferred isolation? Because we defend the pan right I don't think it is the uh, there are Uh, a benefit. I don't I don't I I don't see any benefit. Uh, because this is less performant Uh, the thing that you need to know is there are a Uh, you when you in circulate for Permissions and every everything related to security You rely on operating system and file system permissions You don't have uh, you search groups like in server database Uh, sometimes you can have some Uh, restriction on your operating system. So you need to file back to Less performant Options, but I think that deferred must be used everywhere Perfect. Okay Well, uh, thank you so much. I don't think you have more questions Uh, thanks a lot Okay It was a pleasure