Python 搞搞数据库 简单的SQLite操作

---恢复内容开始---

sqlite3.ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings.

 

sqlite3 插入程序出错如上

 

---恢复内容结束---

由于sqlite3 是默认用的unicode，

所以输出的字符串都是 u'a_string' 这样的格式，前面带个u

我们可以这样：

于是我在代码中添加了一行

con.text_factory = str

有效果了，就将数据导入到了sqlite数据库里面。可以使用sqlite browser查看，或者直接使用sqlite3

其完整代码为

creatTable.py

import sqlite3 as dbapi
import matrix3
con = dbapi.connect('census.db')
cur = con.cursor()
cur.execute('CREATE TABLE Density(Region TEXT, Population INTEGER, Area REAL)')

data = matrix3.returnList('chart1.txt')
con.text_factory = str

for i in data:
    cur.execute('INSERT INTO Density VALUES (?,?,?)',(i[0],i[1],i[2]))
con.commit()
print "All the content in the database"
cur.execute("SELECT * FROM Density")
print cur.fetchall()

print "Output the population"
cur.execute('SELECT Population FROM Density')
print cur.fetchall()

print "Output the Region whose population is lower than 1000000"
cur.execute('''
    SELECT Region FROM Density
    WHERE (Density.Population < 1000000)
''')
print cur.fetchall()

print "Output the Region whose population is lower than 1000000 or larger than 5000000"
cur.execute('''
    SELECT Region FROM Density
    WHERE (Density.Population < 1000000 OR Density.Population > 5000000)
''')
print cur.fetchall()

print "Output the Region whose population is larger than 1000000 and lower than 5000000"
cur.execute('''
    SELECT Region FROM Density
    WHERE (Density.Population > 1000000 AND Density.Population < 5000000)
''')
print cur.fetchall()

print "Output the Population of the region whose area is larger than 200000"
cur.execute('''
    SELECT Population FROM Density
    WHERE (Density.Area > 200000)
''')
print cur.fetchall()

print "Output the Population density of each region"
cur.execute('''
    SELECT (Population / Area) FROM Density
''')
print cur.fetchall()

cur.close()
con.close()

运行结果分别是：

createTable2.py代码

import sqlite3 as dbapi
import matrix3
con = dbapi.connect('census.db')
cur = con.cursor()
cur.execute('CREATE TABLE Capital(Region TEXT, Capital TEXT, Population INTEGER)')

data = matrix3.returnList('chart2.txt')
con.text_factory = str

for i in data:
    cur.execute('INSERT INTO Capital VALUES (?,?,?)',(i[0],i[1],i[2]))
# To save data
con.commit()
print "All Content"
cur.execute('SELECT * FROM Capital')
print cur.fetchall()

print "Region and Region Population"
cur.execute('SELECT Region, Population FROM Capital')
print cur.fetchall()

print "The area of a provience whose capital's population is larger than 1000000"
cur.execute('SELECT Region, Population FROM Capital')
print cur.fetchall()

写入进去的效果，用sqlite brrowser 查看：

 

上面已经写好的matrx3.py 代码如下，这个我写的一个一步将数据从网上复制下来，放到txt文档里直接返回数组的程序。

一些附件：

matrix3.py

def returnList(file_addr):
    data = open(file_addr,'r')
    temp = []        # declare an empty list ['']
    for line in data:
        temp.append(str(line.strip()))
    temp.pop()        # we have to delete the last Null element of the list
                # delete him: ['']
    
    #####
    ##    lines Number essential. No need to care about how many rows there are.
    #####
    
    temp_big = []
    for k in range(13):        # 13 refers to how many lines of the chart 
        new = []
        for i in range(k,len(temp),13):    # len(temp) is of x*13. x = rowsNumber
            new.append(temp[i])
        temp_big.append(new)
    return temp_big

chart1.txt 文档

Newfoundland and Labrador
Prince Edward Island
Nova Scotia
New Brunswick
Quebec
Ontario
Manitoba
Saskatchewan
Alberta
British Columbia
Yukon Territory
Northwest Territories
Nunavut
512930
135294
908007
729498
7237479
11410046
1119583
978933
2974807
3907738
28674
37360
26745
370501.69
5684.39
52917.43
71355.67
1357743.08
907655.59
551937.87
586561.35
639987.12
926492.48
474706.97
1141108.37
1925460.18

chart2.txt文档

Newfoundland and Labrador
Prince Edward Island
Nova Scotia
New Brunswick
Quebec
Ontario
Manitoba
Saskatchewan
Alberta
British Columbia
Yukon Territory
Northwest Territories
Nunavut
St. John's
Charlottetown
Halifax
Fredericton
Quebec
Toronto
Winnipeg
Regina
Edmonton
Victoria
Whitehorse
Yellowknife
Iqaluit
172918
58358
359183
81346
682757
4682897
671274
192800
937845
311902
21405
16541
5236

这是pdf的书硬，我拷贝下来的，没有输入一个数字。用计算机写的程序转换成的数组。

 

 

基本的结果就是，复制完成后，经过python--》上传到了SQL数据库里面。很爽的