创建数据库

  1. import sqlite3
  2. import os
  3. dbpath = 'data.sqlite'
  4. if not os.path.exists(dbpath):
  5.     conn = sqlite3.connect(dbpath)
  6.     c = conn.cursor()
  7.     c.execute('''create table person
  8.     (id int primary key not null,
  9.      name text not null,
  10.      age int not null,
  11.      address char(100),
  12.      salary real);''')
  13.     conn.commit()
  14.     conn.close()
  15.     print('创建数据库成功')

插入数据

  1. import sqlite3
  2. import os
  3. dbpath = 'data.sqlite'
  4. conn = sqlite3.connect(dbpath)
  5. c = conn.cursor()
  6. c.execute('delete from person')
  7. c.execute('''
  8. insert into person(id,name,age,address,salary)
  9. values(1,'Bill',32,'California',20000)
  10. ''')
  11. c.execute('''
  12. insert into person(id,name,age,address,salary)
  13. values(2,'Mike',30,'California',20000)
  14. ''')
  15. c.execute('''
  16. insert into person(id,name,age,address,salary)
  17. values(3,'John',45,'California',20000)
  18. ''')
  19. conn.commit()
  20. conn.close()
  21. print('insert success')

查询数据

  1. import sqlite3
  2. import os
  3. dbpath = 'data.sqlite'
  4. conn = sqlite3.connect(dbpath)
  5. c = conn.cursor()
  6. person = c.execute('select name,age,address,salary from person order by age')
  7. result = []
  8. for p in person:
  9.     value = {}
  10.     value['name'] = p[0]
  11.     value['age'] = p[1]
  12.     value['address'] = p[2]
  13.     value['salary'] = p[3]
  14.     result.append(value)
  15. conn.close()
  16. print(result)
  17. >>[{'name': 'Mike', 'age': 30, 'address': 'California', 'salary': 20000.0}, 
  18.    {'name': 'Bill', 'age': 32, 'address': 'California', 'salary': 20000.0}, 
  19.    {'name': 'John', 'age': 45, 'address': 'California', 'salary': 20000.0}]