sqlite如何重新排序id号_使用Python和SQLite的SQL（二）

数据库提供了许多功能，通过这些功能，人们可以通过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() 

输出：

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() 

输出：

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以绘制其特征。