Method analysis of Windows Platform Python connecting to sqlite3 database

This article illustrates how Python, the Windows platform, connects to the sqlite3 database. To share for your reference, specific as follows:

I haven't contacted sqlite database before, only heard my colleague talking about it.

Once, my colleague asked me to write an sql for me on the mobile phone, and then said that it could not run, but it was a mistake. I asked what database it was, and he said it was sqlite, and then I learned that there was also sqlite.

Next, Python connects to the sqlite database. It is very easy, because the sqlite module in python also follows the specification of ES18en-ES19en 2.0, so it is the same as sql server, MySQL and oracle databases.

1. Install SQLite on Windows:

(1) please visit SQLite download page, download precompiled from Windows area 2 hexadecimal file: http: / / www sqlite. org/download html

(2) Since my win 7 is 64-bit, download the ES41en-ES42en-win64 -*.zip and ES45en-ES46en-ES47en64 -*.zip zip file.

(3) Create folder C:\sqlite and unzip the above two compressed files under this folder to get es53EN3.def, sqlite3.dll and ES57en3.exe.

(4) Add C:\sqlite to PATH environment variable. Finally, at the command prompt, use sqlite3 command to display the following results:

C:\Users\Administrator>sqlite3
SQLite version 3.15.2 2016-11-28 19:13:37
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

2. Create a database test.db

Create the database directly from the command line sqlite3, then query the database in the system with the command.database.

C:\Users\Administrator>sqlite3 test.db
SQLite version 3.15.2 2016-11-28 19:13:37
Enter ".help" for usage hints.
sqlite> .database
seq name       file
--- --------------- ----------------------------------------------------------
0  main       C:\Users\Administrator\test.db

Note: Do not exit because next python will connect to the database (when you finally close the database, you can exit sqlite3 with the.quit command).

3. python connects sqlite3

The sqlite module is built into python, so there is no need to install it and you can import it and use it directly.

It is important to pay special attention to put the written program files in the same directory as the ES90en.db database, here is: C:\Users\Administrator, otherwise you will find that a new test.db will be created in the program, and it is in the directory where the current program is running, and you will not be able to see the database changes.

# -*- coding:gbk -*-
import sqlite3
conn = sqlite3.connect('test.db')
cur = conn.cursor()
cur.execute('create table t(id int,v varchar(20));');
cur.execute("insert into t values(%d,'%s')" % (1,'xxx'))
cur.execute("insert into t values(%d,'%s')" % (2,'yyy'))
cur.execute("update t set v = '%s' where id = %d" % ('zzz',2))
cur.execute("select * from t;")
results = cur.fetchall()
for row in results:
  print row
conn.commit()
cur.close()
conn.close()

Each piece of data is a meta ancestor, and all records form a list.

Output results:

================ RESTART: C:\Users\Administrator\Desktop\r.py ================
(1, u'xxx')
(2, u'zzz')

The code is very simple, as is the case with python connection sqlite3

More about Python related content interested readers to view this site project: "common database operations Python skills summary", "Python data structure and algorithm tutorial", "Python function using techniques", "Python string skills summary", "Python introduction and advanced tutorial" and "Python file and directory skills summary"

I hope this article is helpful for Python programming.