Print "\nHere's a listing of all the records in the table:\n"įor row in cursor.execute("SELECT rowid, * FROM albums ORDER BY artist"): Print cursor.fetchall() # or use fetchone() Sql = "SELECT * FROM albums WHERE artist=?" You just use normal SQL syntax to run the queries and then have the cursor object execute the SQL. Queries in SQLite are pretty much the same as what you'd use for other databases, such as MySQL or Postgres. Thus is looked for any records that had "John Doe" in its artist field and deleted it. The SQL is only 2 lines! In this case, all we had to do was tell SQLite which table to delete from (albums) and which records to delete using the WHERE clause. Let's check that out!ĭeleting is even easier than updating. Wasn't that easy? Note that if you don't commit the changes, then your changes won't be written out to the database. You can use SET to change a field, so in this case we change the artist field to be "John Doe" in any record WHERE the artist field is set to "Andy Hunter". Here we use SQL's UPDATE command to update out albums table. We'll be covering both of those topics in this section. Sometimes you will need to delete rows from your data too. If you can't update, then your data will become out of date and pretty useless very quickly. Updating and Deleting Recordsīeing able to update your database records is key to keeping your data accurate. The question mark method is much better and using SQLAlchemy is even better because it does all the escaping for you so you won't have to mess with the annoyances of converting embedded single quotes into something that SQLite will accept. Using string substitution is NOT safe and should not be used as it can allow SQL injection attacks to occur. Note that we're using question marks (?) instead of string substitution (%s) to insert the values. The next piece of code shows how add multiple records at once be using the cursor's executemany method. Anyway, to save the record to the database, we have to commit it. This can get complicated when you need to insert strings that include single quotes in them. Note that each item had to have single quotes around it. Here we use the INSERT INTO SQL command to insert a record into our database. ('The Good Life', 'Trip Lee', '', 'Reach Records', 'CD')]Ĭursor.executemany("INSERT INTO albums VALUES (?,?,?,?,?)", albums) ('The End is Where We Begin', 'Thousand Foot Krutch', '', 'TFKmusic', 'CD'), ('Until We Have Faces', 'Red', '', 'Essential Records', 'CD'), # insert multiple records using the more secure "?" methodĪlbums = [('Exodus', 'Andy Hunter', '', 'Sparrow Records', 'CD'), Let's build on this code and insert some data into our new table!Ĭursor.execute("INSERT INTO albums VALUES ('Glow', 'Andy Hunter', '', 'Xplore Records', 'MP3')") SQLite only supports five data types: null, integer, real, text and blob. Here we use SQL syntax to create a table named albums with 5 text fields: title, artist, release_date, publisher and media_type. Next we create a cursor object, which allows you to interact with the database and add records, among other things. In our case, we created it on disk in a file called mydatabase.db. You can pass it a file path, file name or just use use the special string ":memory:" to create the database in memory. (title text, artist text, release_date text,įirst we have to import the sqlite3 library and create a connection to the database. Here's some code that will create a database to hold music albums:Ĭonn = nnect("mydatabase.db") # or use :memory: to put it in RAM If you want to inspect your database visually, you can use the SQLite Manager plugin for Firefox or if you like the command line, you can use SQLite's command line shell How to Create a Database and INSERT Some DataĬreating a database in SQLite is really easy, but the process requires that you know a little SQL to do it. This article will be similar in function to the recent SQLAlchemy tutorial that appeared on this site earlier this month. In this article you will learn the following: Mozilla uses SQLite databases for its popular Firefox browser to store bookmarks and other various pieces of information. Python gained the sqlite3 module all the way back in version 2.5 which means that you can create SQLite database with any current Python without downloading any additional dependencies. SQLite is a self-contained, server-less, config-free transactional SQL database engine.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |