SQLite, an (un) known super ant
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 115 | |
| Author | ||
| Contributors | ||
| License | CC Attribution - NonCommercial - ShareAlike 4.0 International: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this | |
| Identifiers | 10.5446/58799 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
EuroPython 202144 / 115
1
3
19
25
31
34
36
38
41
42
44
46
48
52
60
61
62
65
69
73
76
82
83
84
92
94
96
103
110
113
00:00
Inheritance (object-oriented programming)GoogolCompact spaceStandard deviationTime domainFreewareInheritance (object-oriented programming)Presentation of a groupDatabaseLevel (video gaming)Functional (mathematics)Matching (graph theory)Connected spaceBinary codeStandard deviationOperator (mathematics)String (computer science)IRIS-TMultiplication signBounded variationArithmetic meanBinary fileDefault (computer science)Operating systemFile systemData conversionReal numberNumberInjektivitätFile formatWordComputer configurationCustomer relationship managementServer (computing)MehrplatzsystemFree variables and bound variablesConcurrency (computer science)Subject indexingSoftware testingError messageTable (information)Physical systemStudent's t-testInformation securityData typeGroup actionLogic gateDatabase transactionException handlingLengthData analysisComputer fileEnvelope (mathematics)Asynchronous Transfer ModeRight angleCASE <Informatik>State of matterField (computer science)LoginComputing platformDisk read-and-write headObject (grammar)Office suiteData storage deviceFactory (trading post)FrequencyWindowSemiconductor memoryRoutingRow (database)Raw image formatInterior (topology)Function (mathematics)SatellitePlug-in (computing)ConsistencyNeuroinformatikReading (process)SoftwareACIDDesign by contractRollback (data management)Point (geometry)outputVariable (mathematics)Cursor (computers)WeightWeb pageDifferent (Kate Ryan album)Configuration spaceContext awarenessAreaDifferenz <Mathematik>RectangleComputer forensicsCategory of beingRelational databaseProjective planeRevision controlSlide ruleWritingSource codeOrder (biology)Library (computing)InformationFreewareAndroid (robot)SequelGraphical user interfaceNumbering schemeDomain nameInsertion lossElectronic mailing listNetwork topologyScripting languageDeclarative programmingTupleZirkulation <Strömungsmechanik>Thread (computing)Film editingMereologyCorrelation and dependenceProof theoryParsingPublic domainCompact spaceMeeting/InterviewComputer animation
02:15
Revision controlSingle-precision floating-point formatData analysisFile formatRead-only memoryCache (computing)Computer networkForm (programming)Computer forensicsFile formatInternet der DingeInformationSoftwareSource codeIn-Memory-DatenbankData analysisDatabaseRevision controlTouchscreenSemiconductor memoryCache (computing)MehrplatzsystemServer (computing)Computer animation
04:01
Row (database)Variable (mathematics)Cursor (computers)DatabaseDatabase transactionScripting languageInsertion lossDatabaseContext awarenessFree variables and bound variablesRow (database)Subject indexingCustomer relationship managementException handlingFile formatString (computer science)Connected spaceMathematicsElectronic mailing listTupleCASE <Informatik>Computer configurationSequelDefault (computer science)Field (computer science)Factory (trading post)InjektivitätMetropolitan area networkTable (information)Data conversionVariable (mathematics)NumberCursor (computers)Software testingError messageComputer animation
09:01
Database transactionConcurrency (computer science)Rollback (data management)WritingAsynchronous Transfer ModeSynchronizationRead-only memoryLevel (video gaming)Exclusive orOperations researchFreewareWeightDatabaseDatabase transactionState of matterRollback (data management)DistanceGroup actionArithmetic meanOperator (mathematics)Set (mathematics)Differenz <Mathematik>LengthConsistencyData storage deviceMultiplication signOrder (biology)LoginFrequencyComputer fileConcurrency (computer science)Right angleAsynchronous Transfer ModeBitComputer configurationClosed setLevel (video gaming)Default (computer science)Reading (process)ResultantSubject indexingPoint (geometry)Thread (computing)Network topologyConnected spaceComputer animation
16:53
Subject indexingUniqueness quantificationRegulärer Ausdruck <Textverarbeitung>Table (information)Mathematical singularityEmailVirtual realityOperator (mathematics)E-textSubject indexingTable (information)Inheritance (object-oriented programming)Arithmetic meanDatabaseField (computer science)DeterminismOperator (mathematics)AreaWindowProteinRectangleComputing platformFunctional (mathematics)Relational databaseoutputMatching (graph theory)Row (database)Series (mathematics)CASE <Informatik>WritingOrder (biology)Function (mathematics)Range (statistics)Computer animation
20:40
Standard deviationData typeBinary fileObject (grammar)ParsingTable (information)Gamma functionCursor (computers)Database transactionConcurrency (computer science)Subject indexingStandard deviationData conversionMultiplication signData typeBinary codeDatabaseMathematicsFunctional (mathematics)String (computer science)Computer configurationDatabase transactionConcurrency (computer science)Subject indexingLibrary (computing)CuboidConnected spaceParticle systemObject (grammar)Binary fileOffice suiteCalculusBounded variationFile formatComputer virusServer (computing)Declarative programmingLevel (video gaming)Computer animation
25:23
Inheritance (object-oriented programming)Subject indexingMultiplication signSoftwareGroup actionWeb pagePerfect groupDatabaseRight anglePhysical systemInformation securityOnline helpComputer configurationServer (computing)Student's t-testFile systemProjective planeNeuroinformatikLecture/ConferenceComputer animation
Transcript: English(auto-generated)
00:06
Now we're gonna have a Maria Andrea, she's gonna present over SQL lite and unknown super and 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
00:26
Okay, I think everybody everything is going to be okay, yes Where are you streaming from I Am streaming from Argentina
00:42
North of Argentina resistance a Chaco, so I don't know Everywhere we are there's a difference Yeah
01:01
Okay Well, if you are ready Go ahead and start with good luck. And thank you very much. Okay. Thank you. Good luck I am going to talk about
01:22
very well known 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
01:41
table in the binary acceptable into your Folder and everything is okay. It's free for all users because it's public domain Compact is fast Is the most implemented? Database in the world because it's in every Firefox Chrome
02:03
Android is more is the most used Database and it's very reliable is one of the best tested software in the world to circulate has a lot of uses He is very
02:21
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 you said? Autopsy forensic software for example, and it uses a
02:45
spotlight as his base form He is used as a cache or temporary or in memory Database I Is more that's more free the full screen and the ability to use memory as his
03:05
Source is one of the things that allows that he is user to create single user versions of a Different software with the user software or for educational
03:21
proposes because it's one of the easiest to Start to use or to do experiments and He really it really shines in better mobile and iot work Secular, of course is not a server database
03:45
Is not well suited for many concurrent writer or when you have very big Databases or when your databases are accessed by that work So there are some
04:00
Best practice Seculite you when you connect to seculite one of the important Interesting thing that you can use throw factory or factory. There are some rough factory, but the man the most practical in Python is sickle a crow
04:20
Secular row give 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 the back understand another thing is
04:41
You should use Context context manager in the in this case are you said as To develop to transfer a transaction so when you Start your content manager you start the transaction and we you and the folder manual the content manager
05:03
And so if everything inside the context manager goes well the transaction is commit and if some exception occur or or something a Stop the context managers to end in the correct way a
05:24
The transaction will be rolled back. Okay Another thing important very important is to use placeholder Placeholder instead of Creating your string using format or f-strings or other things
05:44
because a when you use this kind of Thing you can cure 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 a
06:06
real data you maybe have special characters or maybe So it can have conversion problems Another thing is that it's very important and an easy way to prevent sequel injection
06:28
okay, another thing that is I just See you is you prefer to use directly the connection sometimes
06:40
Of course, you need the cursor to read all your rows and the course It's just for that but where you can you may prefer Execute using the connection you can execute the connection user your sequel
07:02
sentences and variables couple but the value is 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 n tuples of any
07:21
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 index create temporary tables or create just the schema of the database
07:46
Totally because you will prefer this so you have one sequel sentences and after the owner Okay Another thing that I'm going to see you is that is
08:04
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
08:27
The schema is when you And You just refer to some database You can open many database in one connection using the attached sentences
08:42
so in this case you need to see to Explicitly say with one database is what you want to Change the configuration, but normally you just use your default database
09:01
Okay Next I'm going to talk about in very very important things Databases you need to ensure that you are going to have consists consistency for example if you have One transaction and you and some people give money to the other you can not
09:27
Subtract from one account and as to the other and I'm not adding to the other account or vice versa so add from one account and subtractive and
09:41
Subtracting for the other because you are or destroying or creating money and this is not So you need to ensure that everything is okay And Transactions and sure in this database that it performed the AC
10:05
They did principle There is a principle means atom easy atom easy means that everything is Every every operation inside the distance action is correctly Don't or known
10:22
What everyone or known Isolation means that nobody is going to read transaction in size While it is happening because it's going to read an inconsistent data
10:43
Consistency see Consistency everything must have be consistent in every Time in the every point in the time and durability Which means that the data must be store secure
11:03
Okay When you Use a bit a use SQLite. You can use the pragma the pragma is your normal Your normal has this value. Well what means right-ahead login right-ahead login?
11:21
Is Means that you use two Files inside your file system. What is the database? You create a database and the other is the logging they were right-ahead logging When somebody wants to write in the database, he it the writer right into the logger
11:44
the logger file not that directly to the database so Everybody is reading in the database after that Finishes the transaction finish then the writer a close it
12:03
in some period of time the login is Righted back to the base and the Data is A Store here. It's order. This is this allows
12:21
More concurrency because you have many readers Readers can use the database Easily and one right there Inside in into their right into the loving another way to use roller boats in
12:44
SQLite is using Rollback files rollback files if they're more traditional is Older than the other You just write directly into the data file and
13:04
The journal is used to store the previous state of every data so when you If the transaction will fall back The journal it writes Again into the data and return everything in their previous state
13:26
You can use your in the net or In truncate or in persist it then it means that the journal after after you used Everything every transaction is finished and the journal it lets
13:42
Pertrucate means that after every transaction is finished and journal is Set to zero length or persist means that the journal is stay stay there, but overwrite them
14:05
Another way to use it is Very useful Because many time you use this database to perform cashier or indexing another data or something like that and you need
14:25
Maximize the speed of creation of Course this means that everything is with a rise of corruption. So this is not suitable for a
14:41
Situation when you need to Get to ensure the durability of the state Okay Isolation level isolation levels means About when everyone every older you serve you the connection can read the data that the writer is
15:05
Updating Normally When you open a connection in SQLite you use a Check in the same thread by default means that you have only one
15:21
Thread using this connection, but you can share with all the trees changing these options Isolation normally is exclusive which means that only one user Is
15:41
Using the database and only She or he is the only one that can read or write inside this The next isolation level is immediate and they use a request writing
16:03
But when the user request right every operation is completed via or write operation The database is locked for everyone and After the writer finish the update release the database to use general
16:23
The ferret is Allows you in more concurrency because the writer request permission to write it put the database in we are only mode that results and Can perform everything
16:41
He allowed readings After that after the writer finish writing the database is free for other writers Okay indexing In other ways you just need to index index
17:02
Have a you very important And Put 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
17:27
for relationship for When you have one table that are related to the order You need to avoid to think is Carefully about what to index because if you over index your writing will be slower
17:45
And then this is a little problem So you need to avoid over indexing? Okay, there are some special case index that circulate Super for example, you can create partial index partially
18:02
That means that not every row in a table is indexed in this guy In this example, I bring you This is just the am leaders such as Rows that has TM leader in true
18:20
index this is useful when you want to avoid to Over indexing because you need just some clothes Another interesting special case use is what you want to Index over a terminus take function. Maybe you have for example the a and the way of
18:48
Some rectangle and you want to index Using the area or maybe you want something that is
19:00
Calculated from all the fields inside this table deterministic mean that when you know your input you Has one and just one or put for example Not a non-deterministic function by definition is the random function when you have some
19:25
some Range and you don't know with output will be another special case is full text series full text series when you need to in this
19:44
Text that are a field that has a lot of text a lot works and you Can use full text in there. This is a protein but is you said and
20:00
But it is available in all platforms Windows Linux so you can use it easily. You don't need to Compile the database You can use the match operator to find
20:27
Words inside the text is very very fast Compared to like operation for example, okay Finally, we are going to talk a little about
20:43
the types Secular has some standard types strings integers floats and the binary object my large binary officer or Jason
21:02
Daytime you Like can store them like strings, but Things and you have a lot of conversions functions conversion functions allow you to
21:22
convert Dates into strings and vice versa For example here you use the function date He you put for example now and after that you see that you fetch
21:46
String representing the day the today This has you can Change them change this kind of function
22:01
You see for example here. I am calculating the third Sunday October and In the third Sunday or October, for example, I start by now Then I go to the start of the year After that, I add nine moons
22:21
after that 40 days and after that, I Seek the next weekday. So weekday, which is Sunday the Sunday so you can See that you can really have a lot of variation using this kind of functions
22:44
perform a Perform some calculus and functions Another way is to use declare particular type
23:00
Particle time is an option and you need to use it when you create your database for example here We open the connection using SQLite particular types Are you executed? Declaring that the box is a time stamp
23:24
Then you insert just using the daytime in the For library in Python, so you import a time and you can use it and
23:40
The data is stored in A binary format and you can fetch it in the same Format that you stored
24:09
Well with this we are just See some interesting features of this little database I I
24:20
Start with best practices. I just show you some transaction isolation and concurrency options that SQLite has SQLite has can Use concurrency is not The same level
24:40
Some server database, but you can use concurrency Indexing he has a really little really advanced indexes indexing capabilities and after that I Show you a date date types how to use a date and time in the
25:05
Day then time day Okay with this I finish I I Just think everything
25:20
Okay questions. Hello. Hey, man. Yeah. Thank you so much Okay, we have a Time left For a bunch of questions. It was a really nice
25:42
I've been using a SQLite as well for some projects and every day I found how Powerful it is indeed So the first one is could you give some concrete examples of places where you have used SQLite
26:00
Well, I have been in Syria I have been using it in a project Syria and We just Scrap a lot a lot of pages H HTML
26:21
pages and We use it to create an index and really perform very well We need we need to you need to know that it is going to be distributed in Argentina as A logical material so we need to ensure that this thing
26:47
this software runs in very old and very little computers and our students our Poor students can have so it's help us
27:08
The other question What's the benefit of immediate over the ferret isolation? because with the Great, right. I
27:21
Don't think it is there there are I don't I don't like I don't see any benefit because this is less performant The thing that you need to know is there are a
27:41
You when you in SQLite for Permissions and every everything related to security you rely on operating system and file system Permissions you don't have users groups like in server database
28:02
Sometimes you can have some Restriction on your operating system. So you need to fall back to Less performant Options but I think that the ferret must be used
28:20
everywhere Perfect. Okay Well, hey, thank you so much Okay Thanks a lot, okay, it was a pleasure