Python2.7中SQLite3的基本操作

本是古典何须时尚 2022-01-28 12:07 206阅读 0赞

**1、基本操作**

<table style="border-style:solid;width:612.727px;"> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      # -*- coding: utf-8 -*- 
    </div> 
    <div>
      #!/usr/bin/env&nbsp; 
     <a style="color:rgb(32,40,89);" rel="nofollow"><u><strong>python</strong></u></a> 
    </div> 
    <div>
      import sqlite3 
    </div> 
    <div>
      def mykey(x): 
    </div> 
    <div>
      return x[3] 
    </div> 
    <div>
      conn=sqlite3.connect("D:\\demo\\my_db.db") 
    </div> 
    <div> 
     <a style="color:rgb(32,40,89);" rel="nofollow"><u><strong>sql</strong></u></a>&nbsp;= "CREATE TABLE IF NOT EXISTS mytb ( a char , b int , c real, d DATE)" 
    </div> 
    <div>
      # a char , b int , c real 表示该表有三个字段， 
    </div> 
    <div>
      # a 是字符串类型， b 是整数类型, c 是实数类型。 
    </div> 
    <div>
      conn.execute( sql ) 
    </div> 
    <div>
      cs = conn.cursor() 
    </div> 
    <div>
      #cs.execute("DELETE FROM mytb WHERE A='张三' ") 
    </div> 
    <div>
      cs.execute("DELETE FROM mytb &nbsp; ") 
    </div> 
    <div>
      #删除所有记录 
    </div> 
    <div>
      ''''' 
    </div> 
    <div>
      cs.execute( "INSERT INTO mytb ( a,b,c,d ) values('Zhang San',25, 120, '2014-03-04')" ) 
    </div> 
    <div>
      cs.execute( "INSERT INTO mytb ( a,b,c,d ) values( 'Wang Wu',24, 110, '2014-05-01')" ) 
    </div> 
    <div>
      cs.execute( "INSERT INTO mytb ( a,b,c,d ) values( 'Li Si',23, 130, '2014-04-06')" ) 
    </div> 
    <div>
      ''' 
    </div> 
    <div>
      #批量注入，batchdata是一个列表，列表里每一个元素都是一个元组 
    </div> 
    <div>
      batchdata=[('Zhang San',25, 120, '2014-03-04'), 
    </div> 
    <div>
      ( 'Wang Wu',24, 110, '2014-05-01'), 
    </div> 
    <div>
      ( 'Li Si',23, 130, '2014-04-06')] 
    </div> 
    <div>
      cs.executemany('INSERT INTO mytb values (?,?,?,?)',batchdata) 
    </div> 
    <div>
      conn.commit() &nbsp;#将加入的记录保存到磁盘，非常重要！ 
    </div> 
    <div>
      cs.execute("SELECT name, sql FROM sqlite_master WHERE type='table'") 
    </div> 
    <div>
      recs = cs.fetchall( ) 
    </div> 
    <div>
      print ( recs ) 
    </div> 
    <div>
      cs.execute( "SELECT * FROM mytb ")#打开数据表 
    </div> 
    <div>
      recs = cs.fetchall()#取出所有记录 
    </div> 
    <div>
      print ( "there is ", len(recs)," notes." ) 
    </div> 
    <div>
      print &nbsp;recs 
    </div> 
    <div>
      recs.sort(key = mykey) 
    </div> 
    <div>
      print recs 
    </div> 
    <div>
      cs.close() 
    </div> 
    <div>
      conn.close() 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

以上代码参考Python中使用SQLite数据库简明教程，有少量改动

最新内容请见作者的GitHub页：http://qaseven.github.io/