![4f06b255304dd446d0c8161d495ee366.png](https://i-blog.csdnimg.cn/blog_migrate/bd1873dae7f7e68b42eb8088f3be9b52.jpeg)
数据库提供了许多功能,通过这些功能,人们可以通过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以绘制其特征。