Home arrow Python arrow Page 3 - Using SQLite in Python

Writing Data - Python

SQLite is a small C library that implements a self-contained SQL database engine. This article examines pysqlite, one of a number of libraries in Python, that is used as an interface with SQLite.

TABLE OF CONTENTS:
  1. Using SQLite in Python
  2. Connecting to a Database
  3. Writing Data
  4. Retrieving Data
  5. Adapting and Converting
By: Peyton McCullough
Rating: starstarstarstarstar / 132
April 12, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Python Articles          >>> More By Peyton McCullough
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PYTHON ARTICLES

- Python Big Data Company Gets DARPA Funding
- Python 32 Now Available
- Final Alpha for Python 3.2 is Released
- Python 3.1: String Formatting
- Python 3.1: Strings and Quotes
- Python 3.1: Programming Basics and Strings
- Tuples and Other Python Object Types
- The Dictionary Python Object Type
- String and List Python Object Types
- Introducing Python Object Types
- Mobile Programming using PyS60: Advanced UI ...
- Nested Functions in Python
- Python Parameters, Functions and Arguments
- Python Statements and Functions
- Statements and Iterators in Python

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: