Python tutorial- connect to a Sqlite database


Connect to a Sqlite database


After you completely install pysqlite, you can import it as module (sqlite3) and then you can connect to the database file directly by using the connect() method of sqlite3 module.
import sqlite3
conn = sqlite3.connect('customerlist.db')
To work with data in the database you need to ceate a cursor object.


curs = conn.cursor()

Now let try to create a table name ‘tblcustomerlist’ that contain the field names: Name, Email, Tel, and Address, and insert some data to the table.
 
import sqlite3
conn = sqlite3.connect('customerlist.db')
curs = conn.cursor()
curs.execute('''
CREATE TABLE tblcustomerlist(
Name TEXT(20) Primary Key,
Email TEXT(25),
Tel TEXT(20),
Address TEXT(150)
)
''')
query="INSERT INTO tblcustomerlist Values('Dara Yuk','yuk.dara@gmail.com', '855 256 724', 'No.252, st.265,Phnom Penh, Cambodia')"
curs.execute(query)
conn.commit()
conn.close()

-The execute() method is used to execute the a sql statement that will insert a customer’s information to the table.
-The commit() method will commit pending transactions. And the close() method will close the connection that is opened by the connect() method.

The firs execute() method will create the tblcustomerlist table that contain some fields as shown above. When you create a new table, you need to supply field names. You also need to specify the type attributes of the fields. Some other attributes can be left.
Please see the table of some common types that can be used in SQLite.
 
Type Description
TEXT For field that stores string value and Unicode characters
INTEGER For field that stores integer value
INTEGER For field that stores long integer value
REAL For field that stores float value

 
A cursor object contains some useful methods and attributes that you will need to work with data in the database. Please see the tables of some common methods and attributes of cursor object as shown below.
 
Method Description
execute(sql,[parameters])
Executes a SQL statement.
close() Closes the cursor.
executemany(sql,seq. of parameters) Executes a SQL command against all parameter sequences or mappings found in the sequence sql.
fetchall() Gets one row from the resultset.
fetchmany() Fetches all rows from the resultset.
fetone() Fetches several rows from the resultset.

 
Attribute Description
description Displays sequence of result column descriptions.
rowcount Gets the number of rows in resultset.
arraysize Specifies the number of rows in fetchmany method (default 1).





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.