SQLite3 传递参数的方法 | SQLite3 Methods of passing arguments

Python Maggie ⋅ 于 2020-05-08 02:01:53 ⋅ 446 阅读

SQLite3传递参数的方法

介绍

当我在做一个图书馆管理系统的时候,我使用SQLite来存储数据。SQLite是世界上使用最多的数据库引擎, 而SQLite3是用于SQLite数据库的DB-API 2.0接口。

一开始,我在传递参数(尤其是变量)时遇到了一些问题,我在寻找教程上浪费了大量时间。最后通过查看 python.org 上的文档,我终于解决了问题。我整理了相关信息来完成了这个教程,希望能给大家带来一些参考。

前期准备工作

在开始在表中插入/选择变量之前,你应该确保完成了以下步骤。

import sqlite3    #导入sqlite3这个模块
conn = sqlite3.connect('example.db')   #连接到你输入的双引号中的数据库(如果你电脑里没有这个db文件,这行命令会默认为你创建一个)
c = conn.cursor()   #创建一个游标对象,这样我们就可以调用它的execute()方法来执行SQL命令

具体方法

通常,你的SQL操作会需要使用来自Python变量的值,这也是为什么我们需要清楚地知道如何在SQLite3中传递参数。

:x:下面是我最初写代码的方式。

t = 'Example'
c.execute("SELECT * FROM tableName WHERE columnName = t")    
#虽然你希望在这里使用t作为变量,但在这行代码中,sqlite会把t视为字符串,并在特定的列中查找"t"(而不是"Example")。

:x:然后我尝试了下面的方法,但这种用法是不安全的,极易遭到SQL注入攻击。

你不应该使用Python的字符串操作来装配你的查询,因为这样做是不安全的;它会使你的程序容易受到SQL注入攻击(参见https://xkcd.com/327/)。
翻译自(https://docs.python.org/3/library/sqlite3.html)

# Never do this -- insecure!
t = 'Example'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % t)  
#"%s"在这里用作占位符,传递了t这个存储了字符串的变量

:white_check_mark:正确的方法是使用DB-API的参数进行替换。SQLite3中常用两种占位符: 1.问号 2.命名占位符。

1.问号

问号(' ? ')是一个占位符,可以放在您希望使用值的任何地方。
然后你需要提供一个值的元组作为游标的execute()方法的第二个参数。

#传递一个元组
t = 'Example'
c.execute("SELECT * FROM tableName WHERE columnName = ?", (t,)) 
print(c.fetchone())

t = ('Example',)
c.execute("SELECT * FROM tableName WHERE columnName=?", t)
print(c.fetchone())

who = 'Ann'
age = '18'
gender = 'female'
c.execute("INSERT INTO tableName VALUES (?, ?, ?)", (who, age, gender))

#值得注意的是,我们不需要向表中所有字段插入值。
#对于像"ID"这类的列,在你不传递任何内容时,值会默认增加1。
#对于表中的其他字段,当你没有传递任何值时,表中相应位置则为空值。

#传递列表
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany("INSERT INTO stocks VALUES (?,?,?,?,?)", purchases)    
# 括号里有五个问号作为占位符,数目与表格的列数一致
2.命名占位符
#2使用命名占位符传递元组
who = 'Ann'
age = '18'
c.execute("SELECT * FROM tableName WHERE name_last=:who and age=:age", {"who": who, "age": age})      
#在"name_last=:"后的"who" 和"age=:"后的"age"是占位符,{}中的内容表明"who"和"age"是储存了字符串的变量名

重要的步骤

在数据库中选择/插入内容后,你还需要遵循两个很重要的步骤。

conn.commit()  #这是为了保存对数据库所做的所有更改。如果你在提交前关闭数据库连接,你将失去所有之前执行的结果。
conn.close()   #关闭数据库的连接。

附录

  1. https://www.sqlite.org/mostdeployed.html
  2. https://docs.python.org/3/library/sqlite3.html
  3. https://www.runoob.com/sqlite/sqlite-insert.html

SQLite3 Methods of passing arguments

Introduction

While I was making a library management system, I used SQLite for data storage. SQLite is the most used database engine in the world. And SQLite3 is the DB-API 2.0 interface for SQLite databases.

In the beginning, I had some problems in passing arguments, particularly variables, and I wasted a lot of time in finding particularly good tutorials written online about it. I finally got my problems solved after reviewing documentation on python.org. I sorted out relavant information to write this tutorial and hopefully give you some useful references.

Preparations

Before you start to insert/select variables in the table, you should make sure you have done the following steps.

import sqlite3    #import this module
conn = sqlite3.connect('example.db')    #connect to the database you typed in the pair of single quotes (if you don't have this .db file, this command line will create one for you by default)
c = conn.cursor()     #create a Cursor object so that we can call its execute() method to perform SQL commands later

Methods

Usually your SQL operations will need to use values from Python variables. And this is why we need to know how to pass arguments in SQLite3.

:x: Below is how I wrote my codes in the first place .

t = 'Example'
c.execute("SELECT * FROM tableName WHERE columnName = t") 
#Although you want to use t here as a variable, sqlite will treat t as a string and look for "t" (not "Example") in the specific column.

:x: Then I tried the method below, which is identified insecure in the python documentation .

You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see https://xkcd.com/327/ for humorous example of what can go wrong).
(https://docs.python.org/3/library/sqlite3.html)

# Never do this -- insecure!
t = 'Example'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % t)  
#"%s" serves as a placeholder here, and t was passed as a variable which stored a string

:white_check_mark: The right way to do this is to use the DB-API’s parameter substitution. And there are two kinds of placeholders: 1. quotation marks, and 2. named placeholders.

1.Quotation Marks

The question mark (?) is a placeholder that should be put wherever you want to use a value. And then you need to provide a tuple of values as the second argument to the cursor’s execute() method.

# passing a tuple
t = 'Example'
c.execute("SELECT * FROM tableName WHERE columnName = ?", (t,)) 
print(c.fetchone())

t = ('Example',)
c.execute("SELECT * FROM tableName WHERE columnName = ?", t)
print(c.fetchone())

who = 'Ann'
age = '18'
gender = 'female'
c.execute("INSERT INTO tableName VALUES (?, ?, ?)", (who, age, gender))

# It is noteworthy that we don't need to insert values to all fields of the table. 
# For a column like "ID", the value increases 1 every time by default (t he same as the ROWID of the row) when you don't pass anything. 
# For other fields in the table, when no value is passed, the value would be NULL.  

# passing a list
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)    
# There are five question marks in the bracket as the first argument. 
# They serve as placeholders, and is accord to the number of columns in the list.
2.Named Placeholders
# using named placeholders to pass a tuple of values
who = 'Ann'
age = '18'
c.execute("SELECT * FROM tableName WHERE name_last=:who and age=:age", {"who": who, "age": age})    
# "who" after "name_last=:" and "age" after "age=:" are placeholders. 
# The contents in {} indicate that "who" and "age" are names of variables other than strings

Important Steps Afterwards

After selecting or inserting elements in the table, there are two more important steps you should follow after you make all those executions in the database.

conn.commit()     # This is to save all the changes you did to the database. You will lose everything if you closed the connection before commiting.
conn.close()      # This is to close the connection to your database.

Bibliography

  1. https://www.sqlite.org/mostdeployed.html
  2. https://docs.python.org/3/library/sqlite3.html
  3. https://www.runoob.com/sqlite/sqlite-insert.html
本帖已被设为精华帖!
本帖由 YX 于 6月前 加精
点赞
成为第一个点赞的人吧 :bowtie:
回复数量: 0
    暂无评论~~
    • 请注意单词拼写,以及中英文排版,参考此页
    • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
    • 支持表情,使用方法请见 Emoji 自动补全来咯,可用的 Emoji 请见 :metal: :point_right: Emoji 列表 :star: :sparkles:
    • 上传图片, 支持拖拽和剪切板黏贴上传, 格式限制 - jpg, png, gif
    • 发布框支持本地存储功能,会在内容变更时保存,「提交」按钮点击时清空
    Ctrl+Enter