If you’re hoping for detailed explanations, there are much better sites (from much more knowledgeable experts) for that. The
SQLite Home Page is one example. I just wanted some examples to refer to for future reference. This first example shows how to create a new database and then close the connection without adding or retrieving any data (not very practical). We’ll have some more pointed examples below.
>>> import sqlite3
>>> mydatabase="/home/user/.mydatabase.db"
>>> connection=sqlite3.connect(mydatabase)
>>> cursor=connection.cursor()
>>> cursor.execute('CREATE TABLE mytable (Id INTEGER PRIMARY KEY, Date TEXT, Entry TEXT)')
<sqlite3.Cursor object at 0xb7cd7140>
>>> connection.commit()
>>> cursor.close()
>>> quit()
Connect to the Database
Based on the example above, we’ve already made a connection to the database. The process is identical if the database already exists:
>>> import sqlite3
>>> connection=sqlite3.connect("/home/user/.mydatabase.db")
>>> cursor=connection.cursor()
Add to the Database
>>> import time
>>> today=time.strftime("%A, %B %d, %Y")
>>> today
'Monday, June 01, 2009'
>>> cursor.execute('INSERT INTO mytable VALUES(null, ?, ?)', (today, "This entry could be the first item on a To-Do List, or it could be a journal entry, or whatever you want."))
<sqlite3.Cursor object at 0xb7e89aa0>
>>> cursor.execute('INSERT INTO mytable VALUES(null, ?, ?)', (today, "To-Do: Write an SQLite3 tutorial!"))
<sqlite3.Cursor object at 0xb7e89aa0>
>>> connection.commit()
Retrieve from the Database
>>> cursor.execute('SELECT * FROM mytable')
<sqlite3.Cursor object at 0xb7cd6aa0>
>>> allentries=cursor.fetchall()
>>> allentries
[(1, u'Monday, June, 01, 2009', u'This entry could be the first item on a To-Do List, or it could be a journal entry, or whatever you want.'), (2, u'Monday, June, 01, 2009', u'To-Do: Write an SQLite3 tutorial!')]
>>> for x in allentries:
... print "Item number: " + str(x[0]) + " Date: " + x[1] + " Entry: " + x[2]
...
Item number: 1 Date: Monday, June, 01, 2009 Entry: This entry could be the first item on a To-Do List, or it could be a journal entry, or whatever you want.
Item number: 2 Date: Monday, June, 01, 2009 Entry: To-Do: Write an SQLite3 tutorial!
Delete from the Database
>>> cursor.execute('DELETE FROM mytable WHERE Id=1')
<sqlite3.Cursor object at 0xb7e89aa0>
>>> connection.commit()
>>> cursor.execute('SELECT * FROM mytable')
<sqlite3.Cursor object at 0xb7e89aa0>
>>> print cursor.fetchall()
[(2, u'Monday, June, 01, 2009', u'To-Do: Write an SQLite3 tutorial!')]
Close the Database
>>> cursor.close()
>>> quit()
Cheat With a GUI
If your database becomes complicated or you get sick of the terminal, you can also just download the firefox Add-On:
SQLite Manager. It’s pretty handy.
Very Good. Thank you!
From Arg.
Wow that was sweet and simple.
Thanks :)
when you write the command:
mydatabase=”/home/user/.mydatabase.db”
where is the database saved? That directory doesn’t exist on my computer…