Using SQLite in Python - Writing Data
(Page 3 of 5 )
We're ready to construct a table in our database now. To do this, we must call the execute method of our cursor, passing the necessary statement. We'll create a table that stores names and e-mail addresses, with each row possessing a unique number:
>>> cursor.execute('CREATE TABLE names (id INTEGER PRIMARY KEY,
name VARCHAR(50), email VARCHAR(50))')
Now that we've created our table, we can add some data using the execute method as well:
>>> cursor.execute('INSERT INTO names VALUES (null, "John Doe",
"jdoe@jdoe.zz")')
>>> cursor.execute('INSERT INTO names VALUES (null, "Mary Sue",
"msue@msue.yy")')
It's also possible to get the number of the last row from the cursor, should we need it:
>>> cursor.lastrowid
2
Of course, let's say we needed to insert a row with values that depend on user input. Obviously, user input is unsafe to use as it stands. A malicious user could easily pass in a value that manipulates the query and does some real damage to things. This is unacceptable, but, thankfully, pysqlite contains a way to ensure security. Let's pretend we have two variables containing the name and e-mail address of a person:
>>> name = "Luke Skywalker"
>>> email ="use@the.force"
Now, these are pretty safe values since we defined them ourselves, but it might not always be that way. To place these values in a query safely, we simply have to put question marks in their place, and pysqlite will take care of the rest:
>>> cursor.execute('INSERT INTO names VALUES (null, ?, ?)',
(name, email))
Now that we are done making our changes, we must save, or commit, them. This is done by calling the commit method of our connection:
>>> connection.commit()
If you attempt to close a connection that has been modified without a call to the commit method, then pysqlite will raise an error. This behavior may cause problems, however, if you don't want to save changes that you've made. This is where the rollback method comes in, which has the power to erase any unsaved changes. For example, let's add another row to our table:
>>> cursor.execute('INSERT INTO names VALUES (null, "Bobby John", "bobby@john.qq")')
Now let's say we don't want the change to take effect. We can simply call the rollback method:
>>> connection.rollback()
There, the change has been erased.
Next: Retrieving Data >>
More Python Articles
More By Peyton McCullough
