使用Python和SQLite的SQL（二）「终于解决」

欢迎大家来到IT世界,在知识的湖畔探索吧!

数据库提供了许多功能，通过这些功能，人们可以通过web轻松管理大量信息，并通过文本文件等典型文件输入和输出大量数据。SQL是一种查询语言，在数据库中非常流行。许多网站使用MySQL。SQLite是一个“轻量级”版本，其语法与SQL非常相似。

SQLite是一个自包含、高可靠性、嵌入式、全功能、公共域的SQL数据库引擎。它是互联网中使用最多的数据库引擎。

Python有一个用于访问SQLite数据库的库，称为sqlite3，用于与该数据库一起使用，该库自2.5版以来已包含在Python软件包中。

在本文中，我们将讨论如何使用诸如Update和Delete之类的命令查询数据库，以及如何通过图形显示数据。

更新和删除操作

# code for update operation 
import sqlite3 
  
# database name to be passed as parameter 
conn = sqlite3.connect('mydatabase.db') 
  
# update the student record 
conn.execute("UPDATE Student SET name = 'Sam' where unix='B113059'") 
conn.commit() 
  
print "Total number of rows updated :", conn.total_changes 
  
cursor = conn.execute("SELECT * FROM Student") 
for row in cursor: 
   print row, 
  
conn.close() 

欢迎大家来到IT世界,在知识的湖畔探索吧!

输出：

欢迎大家来到IT世界,在知识的湖畔探索吧!Total number of rows updated : 1
(u'B113053', u'Geek', u'2017-01-11 13:53:39', 21.0), 
(u'B113058', u'Saan', u'2017-01-11 13:53:39', 21.0), 
(u'B113059', u'Sam', u'2017-01-11 13:53:39', 22.0)

# code for delete operation 
import sqlite3 
  
# database name to be passed as parameter 
conn = sqlite3.connect('mydatabase.db') 
  
# delete student record from database 
conn.execute("DELETE from Student where unix='B113058'") 
conn.commit() 
print "Total number of rows deleted :", conn.total_changes 
  
cursor = conn.execute("SELECT * FROM Student") 
for row in cursor: 
   print row, 
  
conn.close() 

输出：

欢迎大家来到IT世界,在知识的湖畔探索吧!Total number of rows deleted : 1
(u'B113053', u'Geek', u'2017-01-11 13:53:39', 21.0),
 (u'B113059', u'Sam', u'2017-01-11 13:53:39', 22.0)

用户输入的数据

# code for executing query using input data 
import sqlite3 
  
# creates a database in RAM 
con = sqlite3.connect(":memory:") 
cur = con.cursor() 
cur.execute("create table person (name, age, id)") 
  
print ("Enter 5 students names:") 
who = [raw_input() for i in range(5)] 
print ("Enter their ages respectively:") 
age = [int(raw_input()) for i in range(5)] 
print ("Enter their ids respectively:") 
p_id = [int(raw_input()) for i in range(5)] 
n = len(who) 
  
for i in range(n): 
  
    # This is the q-mark style: 
    cur.execute("insert into person values (?, ?, ?)", (who[i], age[i], p_id[i])) 
  
    # And this is the named style: 
    cur.execute("select * from person") 
  
    # Fetches all entries from table 
    print cur.fetchall() 

输出：

(u'Navin', 34, 113053)
(u'Basu', 42, 113058)
(u'Firoz', 65, 113059)
(u'Tim', 47, 113060)
(u'Varun', 54, 113061)

用SQLite作图

# graph visualization using matplotlib library 
import matplotlib.pyplot as plt 
  
def graph_data(p_id,age): 
  
    # plotting the points     
    plt.plot(p_id, age, color='yellow', linestyle='dashed', linewidth = 3, 
    marker='*', markerfacecolor='blue', markersize=12) 
  
    # naming the x axis 
    plt.xlabel('Persons Id') 
  
    # naming the y axis 
    plt.ylabel('Ages') 
  
    # plt.plot(p_id,age) 
    plt.show() 
  
print ("Enter 5 students names:") 
who = [raw_input() for i in range(5)] 
print ("Enter their ages respectively:") 
age = [int(raw_input()) for i in range(5)] 
print ("Enter their ids respectively:") 
p_id = [int(raw_input()) for i in range(5)] 
  
# calling graph function 
graph_data(p_id,age) 

通过这种方式，我们可以使用SQL查询来执行此类操作，以与数据库进行通信并显着绘制Graph以绘制其特征。