1. 如何创建SQLite数据库

# 1. create database and table

'''
sqlite3
内置模块，不需要安装
'''
import sqlite3
import os

dbPath = 'data.sqlite'
if not os.path.exists(dbPath): conn = sqlite3.connect(dbPath) c = conn.cursor() c.execute('''create table persons (id int primary key not null, name text not null, age int not null, address char(100), salary real);''') conn.commit() conn.close() print('创建数据库成功') 
  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
 

创建数据库成功

  

 

  
1
 

2. 如何向SQLite表中插入数据

# 2. insert
conn = sqlite3.connect(dbPath)
c = conn.cursor()
c.execute('delete from persons')
c.execute(''' insert into persons(id, name, age, address, salary) values(1, 'Bill', 32, 'Colifornia', 20000);''')
c.execute(''' insert into persons(id, name, age, address, salary) values(2, 'Mike', 30, 'China', 10000);''')
c.execute(''' insert into persons(id, name, age, address, salary) values(3, 'Jhon', 12, 'Norway', 30000);''')
conn.commit()
print('insert success')


  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
 

insert success

  

 

  
1
 

3. 如何查询SQLite表中的数据

# 3. select
persons = c.execute('select name, age, address, salary from persons order by age')
print(type(persons))
result = []
for person in persons: value = {} value['name'] = person[0] value['age'] = person[1] value['address'] = person[2] result.append(value)
conn.close()
print(type(result))
print(result)


import json
resultStr = json.dumps(result)
print(resultStr)

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
 

<class 'sqlite3.Cursor'>
<class 'list'>
[{'name': 'Jhon', 'age': 12, 'address': 'Norway'}, {'name': 'Mike', 'age': 30, 'address': 'China'}, {'name': 'Bill', 'age': 32, 'address': 'Colifornia'}]
[{"name": "Jhon", "age": 12, "address": "Norway"}, {"name": "Mike", "age": 30, "address": "China"}, {"name": "Bill", "age": 32, "address": "Colifornia"}]

  

 

  
1
  
2
  
3
  
4
 

44 - 操作MySQL数据库

文章来源: ruochen.blog.csdn.net，作者：若尘，版权归原作者所有，如需转载，请联系作者。

原文链接：ruochen.blog.csdn.net/article/details/104613800