title
Python SQLite Tutorial: Complete Overview - Creating a Database, Table, and Running Queries

description
In this Python SQLite tutorial, we will be going over a complete introduction to the sqlite3 built-in module within Python. SQLite allows us to quickly get up and running with databases, without spinning up larger databases like MySQL or Postgres. We will be creating a database, creating a table, insert, select, update, and delete data. Let's get started... The code from this video can be found at: https://github.com/CoreyMSchafer/code_snippets/tree/master/Python-SQLite ✅ Support My Channel Through Patreon: https://www.patreon.com/coreyms ✅ Become a Channel Member: https://www.youtube.com/channel/UCCezIgC97PvUuR4_gbFUs5g/join ✅ One-Time Contribution Through PayPal: https://goo.gl/649HFY ✅ Cryptocurrency Donations: Bitcoin Wallet - 3MPH8oY2EAgbLVy7RBMinwcBntggi7qeG3 Ethereum Wallet - 0x151649418616068fB46C3598083817101d3bCD33 Litecoin Wallet - MPvEBY5fxGkmPQgocfJbxP6EmTo5UUXMot ✅ Corey's Public Amazon Wishlist http://a.co/inIyro1 ✅ Equipment I Use and Books I Recommend: https://www.amazon.com/shop/coreyschafer ▶️ You Can Find Me On: My Website - http://coreyms.com/ My Second Channel - https://www.youtube.com/c/coreymschafer Facebook - https://www.facebook.com/CoreyMSchafer Twitter - https://twitter.com/CoreyMSchafer Instagram - https://www.instagram.com/coreymschafer/ #Python

detail
{'title': 'Python SQLite Tutorial: Complete Overview - Creating a Database, Table, and Running Queries', 'heatmap': [{'end': 609.761, 'start': 552.19, 'weight': 0.76}, {'end': 788.1, 'start': 731.56, 'weight': 0.757}, {'end': 1323.908, 'start': 1301.034, 'weight': 0.754}, {'end': 1521.674, 'start': 1478.586, 'weight': 0.732}, {'end': 1612.413, 'start': 1585.805, 'weight': 0.713}], 'summary': 'Tutorial provides a comprehensive overview of sqlite, highlighting its benefits for small to medium-sized applications, testing, and prototyping, and covers creating a database, table, and running queries. it includes examples of using sqlite in python, sql insert and select statements, database insertion with python, using sql placeholders, and practical application of sqlite in python for testing and prototyping.', 'chapters': [{'end': 88.526, 'segs': [{'end': 69.815, 'src': 'embed', 'start': 0.169, 'weight': 0, 'content': [{'end': 4.373, 'text': "Hey there, how's it going everybody? In this video we're going to be learning how to work with SQLite.", 'start': 0.169, 'duration': 4.204}, {'end': 12.419, 'text': "Now SQLite is extremely useful when you need some database functionality and don't want to spin up a full-fledged database like MySQL or Postgres.", 'start': 4.693, 'duration': 7.726}, {'end': 19.345, 'text': 'So you can use SQLite for small to medium-sized applications, where your database is just going to live on disk,', 'start': 13.02, 'duration': 6.325}, {'end': 22.868, 'text': 'or you can use it for testing and prototyping out an application.', 'start': 19.345, 'duration': 3.523}, {'end': 26.912, 'text': 'And if you have the need to move up to a larger database, then you can later port that over.', 'start': 23.188, 'duration': 3.724}, {'end': 32.256, 'text': "And SQLite is actually part of the standard library, so there's no need to even install anything,", 'start': 27.572, 'duration': 4.684}, {'end': 34.559, 'text': 'and we can just start working with it right out of the box.', 'start': 32.256, 'duration': 2.303}, {'end': 42.367, 'text': "And it's extremely easy to use because your database can just be a simple file, or it can even be an in-memory database that just lives in RAM.", 'start': 34.999, 'duration': 7.368}, {'end': 50.015, 'text': 'Now one thing I wanted to point out is that this video is just going to focus on using SQLite and not how to program the SQL language itself.', 'start': 42.847, 'duration': 7.168}, {'end': 54.5, 'text': "So I'm going to assume that anyone watching this video has some basic knowledge of SQL.", 'start': 50.336, 'duration': 4.164}, {'end': 58.885, 'text': "And if you don't know SQL, then I do have a short series on getting started with the basics.", 'start': 54.821, 'duration': 4.064}, {'end': 60.967, 'text': "Okay, so let's go ahead and get started.", 'start': 59.406, 'duration': 1.561}, {'end': 66.752, 'text': 'So first of all, I have some sample code here so that we have something to work with when creating our SQLite database.', 'start': 61.308, 'duration': 5.444}, {'end': 69.815, 'text': 'Now this sample code is from my object oriented series.', 'start': 67.132, 'duration': 2.683}], 'summary': 'Video tutorial on working with sqlite for small to medium-sized applications and testing, part of the standard library and easy to use.', 'duration': 69.646, 'max_score': 0.169, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA169.jpg'}], 'start': 0.169, 'title': 'Sqlite benefits', 'summary': 'Introduces the benefits of using sqlite for small to medium-sized applications, testing, and prototyping, as well as its ease of use and integration with the standard library. it emphasizes its suitability for those familiar with sql and provides a sample code for creating a sqlite database.', 'chapters': [{'end': 88.526, 'start': 0.169, 'title': 'Working with sqlite', 'summary': 'Introduces the benefits of using sqlite for small to medium-sized applications, testing, and prototyping, as well as its ease of use and integration with the standard library, emphasizing its suitability for those familiar with sql. it also mentions the availability of prior sql knowledge and introduces a sample code for creating a sqlite database.', 'duration': 88.357, 'highlights': ['SQLite is extremely useful for small to medium-sized applications and testing, with the ability to live on disk or in-memory.', 'SQLite is part of the standard library, eliminating the need for additional installations and allowing immediate usage.', 'The video focuses on using SQLite rather than programming the SQL language itself, assuming basic SQL knowledge.', 'Introduction to a sample code for creating a SQLite database, which is part of an object oriented series.']}], 'duration': 88.357, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA169.jpg', 'highlights': ['SQLite is extremely useful for small to medium-sized applications and testing, with the ability to live on disk or in-memory.', 'SQLite is part of the standard library, eliminating the need for additional installations and allowing immediate usage.', 'The video focuses on using SQLite rather than programming the SQL language itself, assuming basic SQL knowledge.', 'Introduction to a sample code for creating a SQLite database, which is part of an object oriented series.']}, {'end': 496.642, 'segs': [{'end': 116.64, 'src': 'embed', 'start': 89.026, 'weight': 0, 'content': [{'end': 91.947, 'text': 'And the other file here is called SQLite demo.', 'start': 89.026, 'duration': 2.921}, {'end': 94.648, 'text': "And that's where we're going to learn how to use SQLite.", 'start': 92.147, 'duration': 2.501}, {'end': 99.289, 'text': 'So if I open up that SQLite demo, then we just have a blank script here.', 'start': 95.028, 'duration': 4.261}, {'end': 104.291, 'text': "So right now, let's not worry about the employee class and instead just jump right into working with SQLite.", 'start': 99.709, 'duration': 4.582}, {'end': 107.152, 'text': "Let's say that we want to create an application.", 'start': 104.911, 'duration': 2.241}, {'end': 113.537, 'text': 'where we have employees and we want to be able to add, update and delete employees from our database,', 'start': 107.532, 'duration': 6.005}, {'end': 116.64, 'text': 'as well as being able to grab employee information from that database.', 'start': 113.537, 'duration': 3.103}], 'summary': 'Learning to work with sqlite to manage employee data.', 'duration': 27.614, 'max_score': 89.026, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA89026.jpg'}, {'end': 170.149, 'src': 'embed', 'start': 125.929, 'weight': 1, 'content': [{'end': 130.352, 'text': "So we can say import SQL Lite 3, and there's only one L there.", 'start': 125.929, 'duration': 4.423}, {'end': 134.036, 'text': 'And now we need a connection object that represents our database.', 'start': 131.013, 'duration': 3.023}, {'end': 142.245, 'text': "So I'll go ahead and create a variable here named con and set that equal to sqlite3.connect.", 'start': 134.337, 'duration': 7.908}, {'end': 144.227, 'text': 'Now within the connect method.', 'start': 142.846, 'duration': 1.381}, {'end': 150.554, 'text': 'here we can either pass in a file where we want to store our data or we can even make an in-memory database.', 'start': 144.227, 'duration': 6.327}, {'end': 156.078, 'text': "To do an in-memory database, we'll have a string here and say colon memory and another colon.", 'start': 150.874, 'duration': 5.204}, {'end': 158.42, 'text': "So that's how you would do an in-memory database.", 'start': 156.499, 'duration': 1.921}, {'end': 161.923, 'text': "But for our example, we're going to instead use a file name.", 'start': 158.901, 'duration': 3.022}, {'end': 165.366, 'text': "And for this file name, we're going to call this employee.db.", 'start': 162.344, 'duration': 3.022}, {'end': 170.149, 'text': 'So now with just these two lines of code.', 'start': 167.648, 'duration': 2.501}], 'summary': "Using python's sqlite3 to create a connection object for an employee database.", 'duration': 44.22, 'max_score': 125.929, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA125929.jpg'}, {'end': 455.344, 'src': 'embed', 'start': 412.38, 'weight': 3, 'content': [{'end': 416.503, 'text': "So this commits the current transaction, and it's easy to forget this step.", 'start': 412.38, 'duration': 4.123}, {'end': 417.604, 'text': 'A lot of people leave that out.', 'start': 416.523, 'duration': 1.081}, {'end': 424.129, 'text': "So if you aren't seeing something in the database that you think should be there, then make sure that you're committing your changes.", 'start': 418.164, 'duration': 5.965}, {'end': 432.354, 'text': "And also at the end here, it's a good practice to close the connection to the database, and we can do that with a con.close.", 'start': 424.649, 'duration': 7.705}, {'end': 435.917, 'text': "So now let's go ahead and execute all of this code.", 'start': 432.915, 'duration': 3.002}, {'end': 439.001, 'text': "And we didn't get any errors when we ran that, so that's good.", 'start': 436.457, 'duration': 2.544}, {'end': 444.669, 'text': 'So that created our employees table, and that will be obvious if we try to run this code again.', 'start': 439.382, 'duration': 5.287}, {'end': 449.396, 'text': 'Because if I try to run this code again, now you can see that this time we did get an error.', 'start': 445.01, 'duration': 4.386}, {'end': 455.344, 'text': "And if we look at the error here, It's saying that the table employees already exists.", 'start': 449.797, 'duration': 5.547}], 'summary': 'Commit transactions, close connection, and avoid re-creating existing tables to prevent errors.', 'duration': 42.964, 'max_score': 412.38, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA412380.jpg'}], 'start': 89.026, 'title': 'Using sqlite and python', 'summary': 'Covers learning sqlite basics and working with sqlite in python. it includes creating a database application, connecting to a sqlite database, creating a table, and interacting with it using python. it emphasizes committing changes, closing the connection, and includes examples of successful and failed table creation.', 'chapters': [{'end': 170.149, 'start': 89.026, 'title': 'Learning sqlite basics', 'summary': 'Introduces the basics of using sqlite for creating a database application, including importing sqlite, establishing a database connection, and deciding between storing data in a file or an in-memory database.', 'duration': 81.123, 'highlights': ['We learn how to import SQLite and create a connection object to represent the database, without the need for additional installations.', "The chapter explains the option of using either a file or an in-memory database for storing data, providing the example of creating a file named 'employee.db' for the database."]}, {'end': 496.642, 'start': 170.149, 'title': 'Working with sqlite in python', 'summary': 'Demonstrates how to connect to a sqlite database, create a table, and interact with it using python, emphasizing the importance of committing changes and closing the connection, with examples showing successful and failed table creation.', 'duration': 326.493, 'highlights': ['The chapter demonstrates how to connect to a SQLite database, create a table, and interact with it using Python', 'Emphasizes the importance of committing changes and closing the connection', 'Examples showing successful and failed table creation']}], 'duration': 407.616, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA89026.jpg', 'highlights': ['The chapter demonstrates how to connect to a SQLite database, create a table, and interact with it using Python', 'We learn how to import SQLite and create a connection object to represent the database, without the need for additional installations', "The chapter explains the option of using either a file or an in-memory database for storing data, providing the example of creating a file named 'employee.db' for the database", 'Emphasizes the importance of committing changes and closing the connection', 'Examples showing successful and failed table creation']}, {'end': 853.13, 'segs': [{'end': 551.829, 'src': 'embed', 'start': 496.982, 'weight': 0, 'content': [{'end': 506.689, 'text': "I'm just going to say insert into employees values, and now I want to fill in those that first column, last column and pay column.", 'start': 496.982, 'duration': 9.707}, {'end': 516.416, 'text': "so I'll do the name as Corey and a comma last name as Schaefer, and for pay I'll just make up 50.", 'start': 506.689, 'duration': 9.727}, {'end': 520.26, 'text': "So now, if I run this code then we didn't get any errors.", 'start': 516.416, 'duration': 3.844}, {'end': 520.9, 'text': "so that's good.", 'start': 520.26, 'duration': 0.64}, {'end': 525.585, 'text': 'And to the best of our knowledge, that data was inserted into our employee database.', 'start': 521.301, 'duration': 4.284}, {'end': 530.95, 'text': "But let's go ahead and find out by querying the database for that employee.", 'start': 525.885, 'duration': 5.065}, {'end': 533.913, 'text': "So to do this, we're going to execute a SELECT statement.", 'start': 531.25, 'duration': 2.663}, {'end': 537.556, 'text': "So I'll comment out this INSERT statement here.", 'start': 534.253, 'duration': 3.303}, {'end': 541.32, 'text': "Now let's create our SELECT statement.", 'start': 539.418, 'duration': 1.902}, {'end': 545.604, 'text': "So at first, I'm just going to hard code in what we're looking for.", 'start': 541.72, 'duration': 3.884}, {'end': 549.007, 'text': "And I'll do that by saying c.execute.", 'start': 545.964, 'duration': 3.043}, {'end': 551.829, 'text': "And now we're going to type in our SQL command.", 'start': 549.527, 'duration': 2.302}], 'summary': "Inserted 'corey schaefer' with $50 into employees database and executed select statement successfully.", 'duration': 54.847, 'max_score': 496.982, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA496982.jpg'}, {'end': 609.761, 'src': 'heatmap', 'start': 552.19, 'weight': 0.76, 'content': [{'end': 556.253, 'text': "So I'll say SELECT star FROM employees.", 'start': 552.19, 'duration': 4.063}, {'end': 560.04, 'text': "And I'll put in a where clause here to find that employee.", 'start': 557.438, 'duration': 2.602}, {'end': 564.404, 'text': "So I'll say where last equals Schaefer and save that.", 'start': 560.321, 'duration': 4.083}, {'end': 568.888, 'text': 'Now that select statement is going to provide some results that we can iterate through.', 'start': 564.865, 'duration': 4.023}, {'end': 575.794, 'text': 'So to iterate through that query result, then we can use a few different methods here.', 'start': 569.289, 'duration': 6.505}, {'end': 587.431, 'text': 'So we have c.fetch1 And what that will do is it will get the next row in our results and only return that row.', 'start': 576.115, 'duration': 11.316}, {'end': 591.593, 'text': "And if there's no more rows available, then it just returns none.", 'start': 587.731, 'duration': 3.862}, {'end': 597.815, 'text': 'So we have fetch one, we also have fetch many, and this takes an argument of a number.', 'start': 592.433, 'duration': 5.382}, {'end': 599.796, 'text': 'So say you said fetch many five.', 'start': 597.895, 'duration': 1.901}, {'end': 605.559, 'text': 'So what that will do is it will return that number of rows as a list.', 'start': 600.737, 'duration': 4.822}, {'end': 609.761, 'text': 'And if there are no more rows available, then it will just return an empty list.', 'start': 605.879, 'duration': 3.882}], 'summary': 'Using sql to fetch employee data, iterating through query results with methods like fetch1 and fetch many.', 'duration': 57.571, 'max_score': 552.19, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA552190.jpg'}, {'end': 605.559, 'src': 'embed', 'start': 576.115, 'weight': 1, 'content': [{'end': 587.431, 'text': 'So we have c.fetch1 And what that will do is it will get the next row in our results and only return that row.', 'start': 576.115, 'duration': 11.316}, {'end': 591.593, 'text': "And if there's no more rows available, then it just returns none.", 'start': 587.731, 'duration': 3.862}, {'end': 597.815, 'text': 'So we have fetch one, we also have fetch many, and this takes an argument of a number.', 'start': 592.433, 'duration': 5.382}, {'end': 599.796, 'text': 'So say you said fetch many five.', 'start': 597.895, 'duration': 1.901}, {'end': 605.559, 'text': 'So what that will do is it will return that number of rows as a list.', 'start': 600.737, 'duration': 4.822}], 'summary': 'The fetch1 function returns the next row in results, while fetch many can return a specified number of rows as a list.', 'duration': 29.444, 'max_score': 576.115, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA576115.jpg'}, {'end': 759.479, 'src': 'embed', 'start': 731.56, 'weight': 4, 'content': [{'end': 739.847, 'text': "But the way you'll most likely be using this in Python is that you'll have some Python variables and you want to put the value from those variables into your query.", 'start': 731.56, 'duration': 8.287}, {'end': 745.451, 'text': "So to see an example of this, let's start using that employee class that we looked at before.", 'start': 740.287, 'duration': 5.164}, {'end': 749.553, 'text': "And like I said, if you don't know what this code is doing, then don't worry too much about it.", 'start': 745.971, 'duration': 3.582}, {'end': 759.479, 'text': 'This class just allows us to create employees, and when we create an employee, it comes in and it sets the first name, last name, and pay.', 'start': 750.173, 'duration': 9.306}], 'summary': 'Using python variables to input values into queries, as demonstrated with the employee class.', 'duration': 27.919, 'max_score': 731.56, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA731560.jpg'}, {'end': 788.1, 'src': 'heatmap', 'start': 731.56, 'weight': 0.757, 'content': [{'end': 739.847, 'text': "But the way you'll most likely be using this in Python is that you'll have some Python variables and you want to put the value from those variables into your query.", 'start': 731.56, 'duration': 8.287}, {'end': 745.451, 'text': "So to see an example of this, let's start using that employee class that we looked at before.", 'start': 740.287, 'duration': 5.164}, {'end': 749.553, 'text': "And like I said, if you don't know what this code is doing, then don't worry too much about it.", 'start': 745.971, 'duration': 3.582}, {'end': 759.479, 'text': 'This class just allows us to create employees, and when we create an employee, it comes in and it sets the first name, last name, and pay.', 'start': 750.173, 'duration': 9.306}, {'end': 767.123, 'text': "And the email and full name use those variables to create those attributes, but we aren't going to use those in this example.", 'start': 760.059, 'duration': 7.064}, {'end': 770.766, 'text': "So now let's switch back to our SQLite demo here.", 'start': 767.643, 'duration': 3.123}, {'end': 774.51, 'text': "And I'm going to import that employee class.", 'start': 771.206, 'duration': 3.304}, {'end': 780.555, 'text': "And I can do this because that employee module is in the same directory as the script that we're currently in.", 'start': 774.95, 'duration': 5.605}, {'end': 786.319, 'text': 'So I can just say from employee import that employee class.', 'start': 780.875, 'duration': 5.444}, {'end': 788.1, 'text': 'And I misspelled that there.', 'start': 786.799, 'duration': 1.301}], 'summary': 'Using python variables to populate query values and importing the employee class from the same directory.', 'duration': 56.54, 'max_score': 731.56, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA731560.jpg'}], 'start': 496.982, 'title': 'Sql insert, select, and sqlite query methods', 'summary': 'Covers sql insert and select statements, including an example of inserting employee data and querying the database. it also demonstrates the usage of fetch methods in sqlite and the implementation of a python class to access query results.', 'chapters': [{'end': 551.829, 'start': 496.982, 'title': 'Sql insert and select statements', 'summary': 'Covers the process of inserting data into a database using sql insert statements, verifying the data insertion, and querying the database using select statements, with an example of inserting employee data and then querying the database to verify the insertion.', 'duration': 54.847, 'highlights': ['The chapter covers the process of inserting data into a database using SQL insert statements, verifying the data insertion, and querying the database using select statements.', 'An example of inserting employee data and then querying the database to verify the insertion is demonstrated.', 'The process involves executing SQL commands to insert and query employee data, with an example of inserting a new employee named Corey Schaefer with a pay of 50.']}, {'end': 853.13, 'start': 552.19, 'title': 'Sqlite query methods', 'summary': 'Demonstrates the usage of fetch methods such as fetch one, fetch many, and fetch all in sqlite to retrieve query results, also showcasing the implementation of a python class to create instances and access attributes for query values.', 'duration': 300.94, 'highlights': ['The chapter demonstrates the usage of fetch methods such as fetch one, fetch many, and fetch all in SQLite to retrieve query results.', 'The code showcases the implementation of a Python class to create instances and access attributes for query values.']}], 'duration': 356.148, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA496982.jpg', 'highlights': ['The chapter covers the process of inserting data into a database using SQL insert statements, verifying the data insertion, and querying the database using select statements.', 'The chapter demonstrates the usage of fetch methods such as fetch one, fetch many, and fetch all in SQLite to retrieve query results.', 'An example of inserting employee data and then querying the database to verify the insertion is demonstrated.', 'The process involves executing SQL commands to insert and query employee data, with an example of inserting a new employee named Corey Schaefer with a pay of 50.', 'The code showcases the implementation of a Python class to create instances and access attributes for query values.']}, {'end': 1036.088, 'segs': [{'end': 973.56, 'src': 'embed', 'start': 933.822, 'weight': 0, 'content': [{'end': 940.565, 'text': 'If you are accepting any values from an end user, so say from like a website or something like that, for example,', 'start': 933.822, 'duration': 6.743}, {'end': 943.466, 'text': 'then this is vulnerable to SQL injection attacks.', 'start': 940.565, 'duration': 2.901}, {'end': 951.829, 'text': 'And basically all that means is that there are values that I could set these variables equal to that could break the entire database.', 'start': 943.846, 'duration': 7.983}, {'end': 955.47, 'text': "And the reason for that is because it's not properly escaped.", 'start': 952.129, 'duration': 3.341}, {'end': 959.032, 'text': 'So let me show you the correct way to do this.', 'start': 955.77, 'duration': 3.262}, {'end': 962.353, 'text': "And there's actually two different ways and I'll show you both ways here.", 'start': 959.332, 'duration': 3.021}, {'end': 970.618, 'text': 'So the first way to do this is instead of using our regular brace placeholders here, we can instead use question marks.', 'start': 962.813, 'duration': 7.805}, {'end': 973.56, 'text': 'And this is a DB API placeholder.', 'start': 970.838, 'duration': 2.722}], 'summary': 'Accepting user input without proper escaping is vulnerable to sql injection attacks. using db api placeholders is a secure alternative.', 'duration': 39.738, 'max_score': 933.822, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA933822.jpg'}, {'end': 1047.615, 'src': 'embed', 'start': 1015.413, 'weight': 3, 'content': [{'end': 1018.916, 'text': 'you still need to put it within a tuple, which can look a little strange.', 'start': 1015.413, 'duration': 3.503}, {'end': 1023.379, 'text': "And I'll show you how that looks when we run our SELECT statement in just a second.", 'start': 1019.396, 'duration': 3.983}, {'end': 1025.22, 'text': "So I'm not going to run this quite yet.", 'start': 1023.759, 'duration': 1.461}, {'end': 1030.324, 'text': "I'm going to show you the second proper way to use these placeholders.", 'start': 1025.26, 'duration': 5.064}, {'end': 1036.088, 'text': "So I'm going to do another INSERT statement for our second employee here, Jane Doe.", 'start': 1030.684, 'duration': 5.404}, {'end': 1040.05, 'text': 'And this second way of doing the proper placeholders is my personal favorite.', 'start': 1036.548, 'duration': 3.502}, {'end': 1047.615, 'text': "So instead of these question marks, we're instead going to put a colon and a name describing the placeholder.", 'start': 1040.391, 'duration': 7.224}], 'summary': 'The transcript discusses using proper placeholders in sql, including a demonstration of using a colon and a name instead of question marks.', 'duration': 32.202, 'max_score': 1015.413, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA1015413.jpg'}], 'start': 853.631, 'title': 'Database insertion with python', 'summary': 'Discusses inserting python objects into a database, highlighting pitfalls of using string formatting and vulnerability to sql injection attacks. it emphasizes the importance of proper escaping. additionally, it demonstrates the proper usage of db api placeholders, including the use of question marks instead of brace placeholders and the necessity of using tuples even for single values.', 'chapters': [{'end': 955.47, 'start': 853.631, 'title': 'Inserting data into database with python', 'summary': 'Discusses inserting python objects into a database, highlighting the pitfalls of using string formatting and the vulnerability to sql injection attacks, emphasizing the importance of proper escaping.', 'duration': 101.839, 'highlights': ['The use of string formatting to insert values into a database in Python is discouraged due to vulnerability to SQL injection attacks, which can potentially break the entire database.', 'Proper escaping is crucial when accepting values from an end user to prevent SQL injection attacks, safeguarding the integrity of the database.', 'Explains the potential risks of using string formatting for database insertion, emphasizing the importance of considering vulnerabilities when handling user input.']}, {'end': 1036.088, 'start': 955.77, 'title': 'Proper usage of db api placeholders', 'summary': 'Demonstrates the two proper ways of using db api placeholders, including the use of question marks instead of brace placeholders and the necessity of using tuples even for single values, with a demonstration of an insert statement for a second employee.', 'duration': 80.318, 'highlights': ['The first way to use DB API placeholders is by replacing regular brace placeholders with question marks, eliminating the need for quotes to specify string values.', 'An additional argument needs to be passed in the execute method, where a tuple of all the values is included, even if only one value is being passed.', 'Single values need to be enclosed within a tuple, which may appear unconventional, demonstrated in the context of running a SELECT statement.']}], 'duration': 182.457, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA853631.jpg', 'highlights': ['Proper escaping is crucial to prevent SQL injection attacks, safeguarding the database integrity.', 'Using string formatting for database insertion in Python is discouraged due to SQL injection vulnerability.', 'Replacing brace placeholders with question marks in DB API eliminates the need for quotes for string values.', 'Enclosing single values within a tuple is necessary, even for a single value in DB API placeholders.']}, {'end': 1229.754, 'segs': [{'end': 1081.536, 'src': 'embed', 'start': 1036.548, 'weight': 0, 'content': [{'end': 1040.05, 'text': 'And this second way of doing the proper placeholders is my personal favorite.', 'start': 1036.548, 'duration': 3.502}, {'end': 1047.615, 'text': "So instead of these question marks, we're instead going to put a colon and a name describing the placeholder.", 'start': 1040.391, 'duration': 7.224}, {'end': 1054.42, 'text': "So for example, I'll do colon first and colon last and then colon pay.", 'start': 1047.915, 'duration': 6.505}, {'end': 1059.082, 'text': "And now we're still passing in a second argument to the execute method.", 'start': 1055.06, 'duration': 4.022}, {'end': 1061.924, 'text': "But instead of a tuple, it's going to be a dictionary.", 'start': 1059.422, 'duration': 2.502}, {'end': 1068.267, 'text': 'And the dictionary keys are going to be the names of each of these placeholders in our SQL.', 'start': 1062.404, 'duration': 5.863}, {'end': 1071.869, 'text': 'And the values will be what we want those placeholders to be.', 'start': 1068.667, 'duration': 3.202}, {'end': 1076.692, 'text': 'So in this example, this would be, like I said, this will be a dictionary now.', 'start': 1072.449, 'duration': 4.243}, {'end': 1081.536, 'text': 'And now our keys will be all of these values that we want to fill in.', 'start': 1077.172, 'duration': 4.364}], 'summary': 'Using a dictionary with names as placeholders in sql, instead of question marks or tuples.', 'duration': 44.988, 'max_score': 1036.548, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA1036548.jpg'}, {'end': 1159.816, 'src': 'embed', 'start': 1107.475, 'weight': 4, 'content': [{'end': 1110.518, 'text': 'and now we wanted to insert the second employee here.', 'start': 1107.475, 'duration': 3.043}, {'end': 1114.201, 'text': 'so instead of employee one, this is going to be employee two.', 'start': 1110.518, 'duration': 3.683}, {'end': 1119.646, 'text': 'now this line is getting a little long here, so if you wanted to break this up onto another line, you could, i think.', 'start': 1114.201, 'duration': 5.445}, {'end': 1121.628, 'text': "i'm just going to leave it the way it is for now.", 'start': 1119.646, 'duration': 1.982}, {'end': 1124.531, 'text': 'Now, even though this one is longer.', 'start': 1122.128, 'duration': 2.403}, {'end': 1132.521, 'text': "the reason that I like this method of doing the placeholders is because when you only have one placeholder value, I think it's a lot more readable.", 'start': 1124.531, 'duration': 7.99}, {'end': 1135.244, 'text': "And we'll look at that when we run our SELECT statements.", 'start': 1132.761, 'duration': 2.483}, {'end': 1137.968, 'text': "But for now, let's go ahead and run this code.", 'start': 1135.725, 'duration': 2.243}, {'end': 1141.109, 'text': 'and get these employees added to our database.', 'start': 1138.508, 'duration': 2.601}, {'end': 1146.851, 'text': "I'm going to go ahead and run that and we're still printing out the SELECT statement from before,", 'start': 1141.129, 'duration': 5.722}, {'end': 1150.492, 'text': "but now I'm going to go ahead and comment out these INSERT statements.", 'start': 1146.851, 'duration': 3.641}, {'end': 1155.734, 'text': 'So now for this SELECT statement instead of searching for the last name of Schaefer,', 'start': 1151.013, 'duration': 4.721}, {'end': 1159.816, 'text': "let's also run a SELECT statement that searches for the last name of Doe.", 'start': 1155.734, 'duration': 4.082}], 'summary': 'Inserting second employee, running select and insert statements, searching for last name doe.', 'duration': 52.341, 'max_score': 1107.475, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA1107475.jpg'}], 'start': 1036.548, 'title': 'Using sql placeholders', 'summary': 'Explains using a dictionary and question mark and colon placeholders in sql queries, focusing on passing values as a dictionary and the advantages of using placeholders for readability and database manipulation, and showcasing the use of placeholders in searching for specific last names.', 'chapters': [{'end': 1107.475, 'start': 1036.548, 'title': 'Using dictionary for sql placeholders', 'summary': 'Explains using a dictionary as a way to fill in sql placeholders, where instead of question marks, a colon and a name are used as placeholders, and the values are then passed in as a dictionary to the execute method.', 'duration': 70.927, 'highlights': ['Using a dictionary as a way to fill in SQL placeholders, where instead of question marks, a colon and a name are used as placeholders, and the values are then passed in as a dictionary to the execute method.', 'The keys of the dictionary are the names of each placeholder in the SQL, and the values are what we want those placeholders to be filled with.', 'This method involves passing a dictionary as a second argument to the execute method instead of a tuple.']}, {'end': 1141.109, 'start': 1107.475, 'title': 'Inserting employee two', 'summary': 'Discusses the process of inserting employee two and the advantages of using placeholders, with a focus on readability and database manipulation.', 'duration': 33.634, 'highlights': ['Using placeholders for readability and database manipulation', 'Inserting employee two']}, {'end': 1229.754, 'start': 1141.129, 'title': 'Using placeholders in sql queries', 'summary': 'Demonstrates the use of question mark and colon placeholders in sql queries, showcasing how both methods look and function, with a focus on searching for the last names of schaefer and doe.', 'duration': 88.625, 'highlights': ['The chapter demonstrates the use of question mark and colon placeholders in SQL queries.', 'The SELECT statement is modified to search for the last names of Schaefer and Doe, showcasing the use of both placeholder methods.', "The value for the question mark placeholder is the string 'Schaefer', requiring it to be put inside a tuple with a comma.", 'The need to use a tuple and a comma with the question mark approach is highlighted, emphasizing its peculiarity with a single value.']}], 'duration': 193.206, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA1036548.jpg', 'highlights': ['Using a dictionary as a way to fill in SQL placeholders, where instead of question marks, a colon and a name are used as placeholders, and the values are then passed in as a dictionary to the execute method.', 'The keys of the dictionary are the names of each placeholder in the SQL, and the values are what we want those placeholders to be filled with.', 'This method involves passing a dictionary as a second argument to the execute method instead of a tuple.', 'The chapter demonstrates the use of question mark and colon placeholders in SQL queries.', 'The SELECT statement is modified to search for the last names of Schaefer and Doe, showcasing the use of both placeholder methods.', 'The need to use a tuple and a comma with the question mark approach is highlighted, emphasizing its peculiarity with a single value.', 'Using placeholders for readability and database manipulation', 'Inserting employee two']}, {'end': 1776.065, 'segs': [{'end': 1260.288, 'src': 'embed', 'start': 1230.194, 'weight': 1, 'content': [{'end': 1233.836, 'text': "And now the way that we fill out the second argument is it's a dictionary.", 'start': 1230.194, 'duration': 3.642}, {'end': 1239.519, 'text': 'And we want to say that use this key last, which is going to fill in that placeholder.', 'start': 1234.256, 'duration': 5.263}, {'end': 1243.113, 'text': 'and we say that we wanna search for the value of dough.', 'start': 1239.991, 'duration': 3.122}, {'end': 1251.46, 'text': "And, like I said, I think that this is a little more readable because even with the one value, it's a little more obvious that we're saying okay,", 'start': 1243.574, 'duration': 7.886}, {'end': 1256.184, 'text': 'we want this last placeholder here to have the value of dough.', 'start': 1251.46, 'duration': 4.724}, {'end': 1260.288, 'text': 'So now, if I go ahead and run the code that we have now,', 'start': 1256.684, 'duration': 3.604}], 'summary': "Demonstrating how to use a dictionary to fill in a placeholder with the value of 'dough'.", 'duration': 30.094, 'max_score': 1230.194, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA1230194.jpg'}, {'end': 1326.869, 'src': 'heatmap', 'start': 1301.034, 'weight': 2, 'content': [{'end': 1307.718, 'text': "Now what that's gonna do is it will give us a database that lives in RAM and that's useful for testing, if you want a fresh,", 'start': 1301.034, 'duration': 6.684}, {'end': 1309.479, 'text': 'clean database on every run.', 'start': 1307.718, 'duration': 1.761}, {'end': 1318.725, 'text': "So for example, now I can uncomment out my create table here and also I'm gonna uncomment out these insert statements.", 'start': 1310.04, 'duration': 8.685}, {'end': 1323.908, 'text': "So now if I run this code, then you can see that we didn't get any errors.", 'start': 1319.186, 'duration': 4.722}, {'end': 1326.869, 'text': "And that's because it starts completely fresh.", 'start': 1323.988, 'duration': 2.881}], 'summary': 'Using an in-memory database for testing provides a fresh, error-free starting point for each run.', 'duration': 25.835, 'max_score': 1301.034, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA1301034.jpg'}, {'end': 1410.805, 'src': 'embed', 'start': 1348.316, 'weight': 3, 'content': [{'end': 1351.177, 'text': 'Now, since this did start from scratch,', 'start': 1348.316, 'duration': 2.861}, {'end': 1357.3, 'text': "one of our select statements here isn't returning any values because we overwrote those insert statements from earlier.", 'start': 1351.177, 'duration': 6.123}, {'end': 1360.621, 'text': 'So there are no longer any employees with the last name Schaefer.', 'start': 1357.64, 'duration': 2.981}, {'end': 1367.923, 'text': "So the in memory database is nice when you're testing and you don't want to keep deleting a database file over and over and over.", 'start': 1361.221, 'duration': 6.702}, {'end': 1370.524, 'text': 'It just automatically gives you a fresh slate.', 'start': 1368.523, 'duration': 2.001}, {'end': 1377.166, 'text': "And when you're ready, then you can just pass in a file and then your database will be stored just like we did earlier in the video.", 'start': 1370.984, 'duration': 6.182}, {'end': 1385.19, 'text': "So, with our database and memory, Let's quickly prototype out a basic application where we use our created table to insert, select,", 'start': 1377.647, 'duration': 7.543}, {'end': 1389.472, 'text': 'update and delete employees from a database, just so we can tie everything together.', 'start': 1385.19, 'duration': 4.282}, {'end': 1394.115, 'text': "And I'll also show you a little trick when we do this to make our executions more Pythonic.", 'start': 1389.793, 'duration': 4.322}, {'end': 1400.978, 'text': "So to do this, I'm going to create four functions here right above where we create our employees.", 'start': 1394.455, 'duration': 6.523}, {'end': 1405.921, 'text': "And just so you don't have to watch me type these out, I'm actually going to grab these from my snippets here.", 'start': 1401.339, 'duration': 4.582}, {'end': 1410.805, 'text': "So I'm going to copy these over and I'm going to paste these right above our created employees.", 'start': 1406.243, 'duration': 4.562}], 'summary': 'In-memory database allows for easy testing and prototype application with basic operations on employees.', 'duration': 62.489, 'max_score': 1348.316, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA1348316.jpg'}, {'end': 1521.674, 'src': 'heatmap', 'start': 1478.586, 'weight': 0.732, 'content': [{'end': 1485.589, 'text': 'And with SQL light, Connection objects can be used as context managers that automatically commit or roll back transactions.', 'start': 1478.586, 'duration': 7.003}, {'end': 1491.871, 'text': "So transactions will automatically be committed unless there's an exception, and then it'll automatically be rolled back.", 'start': 1485.889, 'duration': 5.982}, {'end': 1498.873, 'text': 'So to do this with SQLite, we can say with con, which is our connection.', 'start': 1492.251, 'duration': 6.622}, {'end': 1502.594, 'text': 'And then within this block, we just want to put our execute statement.', 'start': 1499.233, 'duration': 3.361}, {'end': 1509.839, 'text': "And now since we're executing this insertion from within our context manager, we no longer have the need for a commit statement after this.", 'start': 1502.93, 'duration': 6.909}, {'end': 1511.361, 'text': 'So now this is done.', 'start': 1510.019, 'duration': 1.342}, {'end': 1519.511, 'text': "So now for our get employee by name function, let's grab our select statement from down here towards the bottom.", 'start': 1511.901, 'duration': 7.61}, {'end': 1521.674, 'text': "And I'll just grab this one here.", 'start': 1520.112, 'duration': 1.562}], 'summary': 'Using sql light, connection objects can be used as context managers to automatically commit or roll back transactions.', 'duration': 43.088, 'max_score': 1478.586, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA1478586.jpg'}, {'end': 1612.413, 'src': 'heatmap', 'start': 1585.805, 'weight': 0.713, 'content': [{'end': 1595.007, 'text': "And we're basically just setting the employee's pay where their first name and last name equal the first name and last name of the employee that we pass in.", 'start': 1585.805, 'duration': 9.202}, {'end': 1600.928, 'text': "And for our remove employee here, we're using a context manager again since we're executing a delete statement.", 'start': 1595.447, 'duration': 5.481}, {'end': 1607.79, 'text': "And we're just deleting an employee where the first name and last name equal the first name and last name of the employee we pass in.", 'start': 1601.269, 'duration': 6.521}, {'end': 1612.413, 'text': "So now let's delete all of the code from earlier and instead use these new functions.", 'start': 1608.27, 'duration': 4.143}], 'summary': 'Setting employee pay and removing employee using context manager.', 'duration': 26.608, 'max_score': 1585.805, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA1585805.jpg'}, {'end': 1768.301, 'src': 'embed', 'start': 1744.709, 'weight': 0, 'content': [{'end': 1751.474, 'text': "And when you're ready, you could easily just replace that with a Postgres or MySQL database without changing hardly any of the code.", 'start': 1744.709, 'duration': 6.765}, {'end': 1756.376, 'text': 'So if anyone has any questions about what we covered in this video, then feel free to ask in the comment section below,', 'start': 1752.014, 'duration': 4.362}, {'end': 1757.577, 'text': "and I'll do my best to answer those.", 'start': 1756.376, 'duration': 1.201}, {'end': 1761.818, 'text': 'Now, if you enjoy these tutorials and would like to support them, then there are several ways you can do that.', 'start': 1757.897, 'duration': 3.921}, {'end': 1764.56, 'text': 'The easiest way is to simply like the video and give it a thumbs up.', 'start': 1762.179, 'duration': 2.381}, {'end': 1768.301, 'text': "And also it's a huge help to share these videos with anyone who you think would find them useful.", 'start': 1764.86, 'duration': 3.441}], 'summary': 'Easily switch to postgres or mysql without code changes. encourages support through likes and sharing.', 'duration': 23.592, 'max_score': 1744.709, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA1744709.jpg'}], 'start': 1230.194, 'title': 'Using sqlite in python', 'summary': 'Covers using sqlite in python, including working with a dictionary as the second argument, fetching entries, setting the connection to memory, testing, prototyping, and practical application of sqlite for testing and prototyping. it demonstrates creating functions to insert, select, update, and delete employees and leveraging context managers to simplify sqlite code.', 'chapters': [{'end': 1326.869, 'start': 1230.194, 'title': 'Working with sqlite in python', 'summary': 'Explains how to use a dictionary as the second argument to fill in placeholders, demonstrates the fetching of entries from a sqlite database, and illustrates setting the connection to memory for a fresh database on every run.', 'duration': 96.675, 'highlights': ["The chapter explains how to use a dictionary as the second argument to fill in placeholders, making it more readable and obvious (e.g., specifying 'dough' as the value for a placeholder).", 'It demonstrates the fetching of entries from a SQLite database, showing the results of queries and the values added using instances of an employee class.', 'Illustrates setting the connection to memory in SQLite, allowing for a fresh, clean database on every run, which can be useful for testing.']}, {'end': 1776.065, 'start': 1327.209, 'title': 'Using sqlite for testing and prototyping', 'summary': 'Discusses using an in-memory sqlite database for testing and prototyping, creating functions to insert, select, update, and delete employees, and leveraging context managers to simplify sqlite code, ultimately demonstrating the practical application of sqlite for testing and prototyping in python.', 'duration': 448.856, 'highlights': ['The chapter discusses using an in-memory SQLite database for testing and prototyping', 'Creating functions to insert, select, update, and delete employees', 'Leveraging context managers to simplify SQLite code']}], 'duration': 545.871, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/pd-0G0MigUA/pics/pd-0G0MigUA1230194.jpg', 'highlights': ["The chapter explains how to use a dictionary as the second argument to fill in placeholders, making it more readable and obvious (e.g., specifying 'dough' as the value for a placeholder).", 'Illustrates setting the connection to memory in SQLite, allowing for a fresh, clean database on every run, which can be useful for testing.', 'The chapter discusses using an in-memory SQLite database for testing and prototyping', 'Creating functions to insert, select, update, and delete employees', 'It demonstrates the fetching of entries from a SQLite database, showing the results of queries and the values added using instances of an employee class.', 'Leveraging context managers to simplify SQLite code']}], 'highlights': ['SQLite is extremely useful for small to medium-sized applications and testing, with the ability to live on disk or in-memory.', 'The video focuses on using SQLite rather than programming the SQL language itself, assuming basic SQL knowledge.', 'The chapter covers the process of inserting data into a database using SQL insert statements, verifying the data insertion, and querying the database using select statements.', 'Proper escaping is crucial to prevent SQL injection attacks, safeguarding the database integrity.', 'Using a dictionary as a way to fill in SQL placeholders, where instead of question marks, a colon and a name are used as placeholders, and the values are then passed in as a dictionary to the execute method.', "The chapter explains how to use a dictionary as the second argument to fill in placeholders, making it more readable and obvious (e.g., specifying 'dough' as the value for a placeholder).", 'The chapter demonstrates the usage of fetch methods such as fetch one, fetch many, and fetch all in SQLite to retrieve query results.', 'Illustrates setting the connection to memory in SQLite, allowing for a fresh, clean database on every run, which can be useful for testing.', 'Creating functions to insert, select, update, and delete employees', 'Leveraging context managers to simplify SQLite code', 'Using placeholders for readability and database manipulation', 'SQLite is part of the standard library, eliminating the need for additional installations and allowing immediate usage.', 'The chapter demonstrates how to connect to a SQLite database, create a table, and interact with it using Python', "The chapter explains the option of using either a file or an in-memory database for storing data, providing the example of creating a file named 'employee.db' for the database", 'The process involves executing SQL commands to insert and query employee data, with an example of inserting a new employee named Corey Schaefer with a pay of 50.', 'The keys of the dictionary are the names of each placeholder in the SQL, and the values are what we want those placeholders to be filled with.']}