python sqllit3

sqlite3

简介

• SQLite，是一款轻型的数据库，它包含在一个相对小的C库中，很多嵌入式产品中使用了它，其中python就嵌入了它。 至2021年已经接近有21个年头，SQLite也迎来了一个版本 SQLite 3已经发布。

• 可视化工具：DB Broswer for Sqlite

  http://www.sqlitebrowser.org/

创建数据库

• 当创建与 SQLite 的连接时，如果它不存在，它将自动创建一个数据库文件。这个数据库文件是在磁盘上创建的；

• 我们还可以使用 :memory: 和 connect 函数在 RAM 中创建数据库。该数据库称为内存数据库。

• 磁盘上创建数据库

  import sqlite3
   
  from sqlite3 import Error
   
  def sql_connection():
   
      try:
   
          con = sqlite3.connect('test.db')
   
          print("Connection is established: Database is created in memory")
   
      except Error:
   
          print(Error)
   
      finally:
   
          con.close()
  
  sql_connection()
  

  

• 内存上创建数据库

  import sqlite3
   
  from sqlite3 import Error
   
  def sql_connection():
   
      try:
   
          con = sqlite3.connect('test.db')
   
          print("Connection is established: Database is created in memory")
   
      except Error:
   
          print(Error)
   
      finally:
   
          con.close()
  
  sql_connection()
  

  ---

创建表

• 要在 SQLite3 中创建表，可以在execute()方法中使用 Create Table 查询。考虑以下步骤：

  1. 创建连接对象。
  2. 从连接对象创建一个游标对象。
  3. 使用游标对象，以创建表查询为参数调用execute方法。

  让我们创建具有以下属性的员工：

  employees (id, name, salary, department, position, hireDate)

  import sqlite3
   
  from sqlite3 import Error
   
  def sql_connection():
   
      try:
   
          con = sqlite3.connect('test.db')
   
          return con
   
      except Error:
   
          print(Error)
  
  def sql_table(con):
   
      cursorObj = con.cursor()
   
      cursorObj.execute("""
          CREATE TABLE employees(
                            id integer PRIMARY KEY,
                            name text, 
                            salary real, 
                            department text, 
                            position text, 
                            hireDate text)    
      
      """)
   
      con.commit()
  
  con = sql_connection()
   
  sql_table(con)
  

  

  ---

插入数据

• 向表中插入数据

  import sqlite3
   
  con = sqlite3.connect('test.db')
   
  def sql_insert(con, entities):
   
      cursorObj = con.cursor()
      con.commit()          
      
  entities = (2, 'Andrew', 800, 'IT', 'Tech', '2018-02-06') 
  sql_insert(con, entities)
  

  

  ---

更新表

• 要更新表，只需创建一个连接，然后使用该连接创建一个游标对象，最后在execute()方法中使用 UPDATE 语句。

  假设我们要更新 id 等于 2 的员工的姓名。为了更新，我们将使用 UPDATE 语句和 id 等于 2 的员工。我们将使用 WHERE 子句作为选择该员工的条件。

  import sqlite3
   
  con = sqlite3.connect('test.db')
   
  def sql_update(con):
   
      cursorObj = con.cursor()
   
      cursorObj.execute('UPDATE employees SET name = "Rogers" where id = 2')
   
      con.commit()
  
  sql_update(con)
  

  

  ---

获取所有数据

• featchall()

  import sqlite3
   
  con = sqlite3.connect('test.db')
   
  def sql_fetch(con):
   
      cursorObj = con.cursor()
   
      cursorObj.execute('SELECT * FROM employees')
   
      rows = cursorObj.fetchall()
   
      for row in rows:
   
          print(row)
  
  sql_fetch(con)
  
  """输出"""
  (2, 'Rogers', 800.0, 'IT', 'Tech', '2018-02-06')
  

  ---

列出所有表

• 要列出 SQLite3 数据库中的所有表，应该查询 sqlite_master 表，然后使用*fetchall()*从 SELECT 语句中获取结果。

  sqlite_master 是 SQLite3 中的主表，存放所有的表。

  import sqlite3
   
  con = sqlite3.connect('test.db')
   
  def sql_fetch(con):
   
      cursorObj = con.cursor()
   
      cursorObj.execute('SELECT name from sqlite_master where type= "table"')
   
      print(cursorObj.fetchall())
  
  sql_fetch(con)
  
  
  """输出"""
  [('employees',)]
  

  ---

创建-删除表

• 创建时检查表是否存在

  create table if not exists table_name (column1, column2, …, columnN)

• 删除时检查表是否存在

  drop table if exists table_name

• 检查我们要访问的表是否存在

  cursor.(‘SELECT name from sqlite_master WHERE type = “table” AND name = “employees”’)

  print(cursor.fetchall())

批量插入

• executemany插入多行

  import sqlite3
   
  con = sqlite3.connect('test.db')
   
  cursor = con.cursor()
   
  cursor.execute('create table if not exists projects(id integer, name text)')
   
  data = [(1, "Ridesharing"), (2, "Water Purifying"), (3, "Forensics"), (4, "Botany")]
   
  cursor.executemany("INSERT INTO projects VALUES(?, ?)", data)
   
  con.commit()