ADM-201 dump PMP dumps pdf SSCP exam materials CBAP exam sample questions

用Python使用SQL数据库1-插入一个数据库 – 译学馆
最新评论 (0)


SQL Database with Python Part 1 - Inserting into a Database

各位好 欢迎再次来到 Python 教学视频
Hello and welcome to another python tutorial.
这次我们要讲讲如何通过 Python 操作数据库
This one we’re going to be talking about using python with a database.
更准确的说 我们要通过 Python SQLite3 模块操作 SQLite 数据库
Most specifically we’re going to use SQLite database and with python SQLite3.
如果你想要下载相关模块 访问 去那找就行
If you want to get that, it’s, just go there.
如果你安装好了模块 你就可以 import sqlite3 了
You could import bascially, once you finally have it “import sqlite3”,
然后你就可以用 sqlite 模块了
and you’ll be able to use it.
有人也许会好奇 为什么要用数据库而不是
Some people might be curious about why you actually want a database
over just using like a text file or something like that.
数据库最大的用处就是 一旦你想一次录入多条数据
The biggest thing is for if you’re entering multiple pieces of data at the same time
or if you’re like maybe entering data and reading data and pulling data and pending data
之后可能还要录入更多数据 那这时候仅仅用文本文档是肯定不够的
and then maybe entering more data at the same time. You can’t do that with a text file.
文本文档根本不支持这些功能 这样只会让它报一堆错
It’s just not supported. It’s going to give you a lot of errors.
这时候你的数据崩溃的风险就很高了 当然如果你在用数据库的时候
And you risk data corruption and you still risk data corruption without using proper
没有使用合适的多线程技巧 那依然会有数据崩溃的风险
mutli-threading techniques and stuff with even a database.
But the point is the database allows you to do that a text file really doesn’t.
不过这里我还是要纠正一下自己 其实文本文件
So anyway and actually I’ll have to correct myself a text file
也是一种数据库 不过这种数据库格式很烂 真的不行
really is kind of a database but bad database, I mean actually.
.db 文件是一类使用数据库编码语言的文件
“.db” file some sort or something using like a database coding language.
也就是说 让 SQLite 或 SQL 有它自己的编码语言
With that, SQLite or SQL is actually its own coding language.
SQL is used with a lot of like web hosting and stuff people have SQL databases.
It’s its own programing language entirely.
它有自己的语法 一旦我们遇到了 我会告诉你们
So it has own syntax, so once we get into it, I’ll point out some of the different syntax
structures bascially in terminology and stuff that to use.
Because you’ll have to understand that you really embedding a different type of programming.
But the good thing is if you have a question about maybe the
proper syntax for doing something or the proper command,
就不用去查 Python 相关的资料了 直接查 SQL 方面的资料就可以
you don’t have to look for something that’s python specific, you just look for something SQL specific.
最后要提醒你们的就是 如它的名字 SQLite 所表示的一样
And the final thing I’ll just mention is this is just like its name sounds, it’s SQLite
它是 SQL 数据库的一个轻量级版本 它可能并不会
it’s a light… lightweight version of SQL, so it’s not going to have everything that
包含所有 SQL 数据库的功能
like a full SQL database is going to have.
So if you are looking for like really like out there kind of commands
that aren’t typical really common,
and you might need a better
一个基本版的或者是完整版的 SQL 数据库
bascially SQL import and maybe just like a legitimate whole SQL.
但对于大多数情况 我觉得你们应该用不上那些
But for most uses, I can’t imagine why you need it.
对于 SQLite 你可以
Like SQLite you can… you can do
使用一些常见的数学函数 也能
any major math function and also you can anything like
programming wise or logic wise.
你可以在 Python 脚本中操作数据 然后将结果录入数据库
You can just do in your python script and then enter it into the database. So I’m not too
我觉得你可能完全不需要一个完整版的 SQL 数据库
sure why you would ever need like a full on SQL import.
差不多就这样 让我们开始撸代码吧
But anyway enough of that, Let’s get into the code.
So the first thing you are going to see
on most database scripts, you are going to have to have something that actual connects to the database.
在大多数情况下把它叫做 con 或者 conn
In this case… most cases, it’s either called con or conn,
and then people define this is “sqlite3.connect”.
And then you have to specify whatever database file you want to connect to.
这里我们就用 tutorial.db
In our case we’re going to use “tutorial.db”,
and it’s even if you don’t have a database file already created,
那它就会给你创建一个 就好像你要打开一个文件
it’ll just create it for you, kind of like if you try to like open a file
或者往文件中存些东西 但文件还没被创建 那它就会给你新建一个文件
or save something to a file that doesn’t exist yet, it just makes the file.
确实没问题 不过如果你什么时候想在某个表中录入数据
There’s no problem. But later on whenever you want to enter stuff into a specific table,
你还是得先建好表 表是不会自动创建的
you’re going to have to have created that table already. It won’t just make it for you,
因为操作一个表需要一堆参数和设置 记住这一点
because you have to specify a lot of the parameters and stuff for the table. So anyway keep that in mind.
The next thing you need to do is you have to specify your cursor for the database.
就是 conn.cursor()
And that’s going to be “conn.cursor()”.
创建好了 基本上指针就像你的鼠标光标
So now we’ve done that and bascially your cursor just like you can kind of think of it like your mouse cursor
like where do you want me to go and start working.
So next thing we want to do is we’re going to have to make that table.
先定义 tableCreate 这个函数
So do “tableCreate”, that’s what we call function.
然后这里的 c 代表引导指针
And then this gonna be “c” for conduct cursor,
execute 这个命令就意味着执行创建命令
execute this means just like build this.
然后你所有的 Python 不好意思 是所有的 SQL 语句
And then all your like python or I mean SQL query bascially
are encased in double quotes.
或者单引号 你当然也可以用单引号
So or well really quote, I’m pretty sure you can just use regular quotes.
And… that will be for now.
然后像 Python
Anyway so then the actual like python or…, sorry,
哦不好意思 老是说错 我刚说 Python 其实想说的是 SQL
keeps messing them up so I’m talking about python while I’m really talking about SQL.
SQL 类的命令 全用大写 所以我们写 CREATE TABLE
The SQL like commands. You put them in all Caps. so we’re going to “CREATE TABLE”
全部大写 然后你得给表起个名字
and all Caps. then you’re going to have to name the table.
我们就叫它 stuffToPlot 了
So we’re going to call the “stuffToPlot”,
because I’m going to probably use this in another video
可能把这段视频用在 matplotlib 教程里
or probably just include this video in the matplotlib tutorial serious
然后我也会把这些和基础 Python 教程放一起
and then also I’ll just leave it in this basic python stuff.
因为两边都可能会用到这个 不过
Since really both crowds might use it, but you guys probably
or not everybody watching this video is going to care about how to plot with it.
所以 “CREATE TABLE stuffToPlot”
Anyway so “CREATE TABLE stuffToPlot”
在这个语句里 你就可以去
and then within that, you start specifying the
限定数据库里都有哪些字段 这些你得想一想
columns of your database bascially, that’s what you need to think about.
大多数用 SQL 数据库的人会把第一个字段
So the first column what most people do is within SQL database
and stuff you can have something that’s called a primary key.
And what people usually do with the primary key is just make it an auto incrementing
这样就可以作为 id 使用了
number and then make it the id.
它会从1开始 每新增一个值就增加1
So it starts at one and every new one is just another plus one bascially.
然后 2 3 4 5 6 这么一直增加下去
So go one two three four five six and all the way through bascially.
所以首先我们要创建 ID 字段
So the first thing that we’ll call is we’ll say ID.
在 SQL 中作为一个普通整数型变量来调用 所以 ID 应该是整数
And in SQL to call just like a regular number variable. It should be integer.
就像我之前说的 SQL 是一个完全不同的编程语言 所以接下来
And it’s like what I was saying before, it’s a different programing language. So what you will see next
你会发现浮点数和实数两种叫法 其实是一个东西
is the difference between a float and a real number base there or really the same thing.
But they’re called different things depending on the language.
接下来要做的就是储存我们要录入的数据的 UNIX 时间戳
So next thing we’re going to do is we’re going to store the UNIX timestamp of this data that we’re entering.
我们就把这一列叫做 UNIX 吧
And so we’ll call that column “UNIX”, but it’s going to be a …
这个不能是整数 它一般是有小数点的
it can’t be an integer, it usually has a decimal point.
所以这种数是实数 Python 中叫浮点数 float
So it’s actually called a real, so in python you would call it float
SQL 中则叫实数 real
in SQL is called a real.
Next you’re going to need …, we’re going to want a datestamp
基本上就是 月 日 年 这类东西了
which is bascially the month, day, year all that kind of stuff.
很少人看到 UNIX 时间戳会说:哦 我知道这是什么
Very few people can look at a UNIX stamp and be like… Okay, I know what that is.
There you want a datestamp that’s going to be a text
SQL 中叫文本 text Python 中叫字符串 string
called text in SQL versus string python.
然后我们需要一个关键词字段 keyword 用来表示
And then we’re going to want a keyword you know what are we…
what’s the thing that we’re actually going to plot here.
它应该也是文本类型的数据 然后我们需要一个叫做 value 的字段 它应该是实数
Say that can be text as well, and then we’re going to have a “value” and that’s going to be real number.
这就是我们要用 SQL 创建的数据表
So that’s our table that we’re going to creat within SQL.
让我们保存下来 然后看看我们是不是搞错了什么 运行一下吧
So let’s go aheah and save that and just see if we’ve messed anything up. We’ll run it.
希望能成功创建出来数据表 让我好展示给你们看
And hopefully it’ll create us a table and I’ll show you the table.
好的 运行 然后调用函数创建
Okay. So run and we’ll call our function to create.
不错 我们把这个拉上来 这只是个文件 这就是 tutorial 数据库了
Sure enough, let’s pull this over. This is just the file here. This is the “tutorial”.
是一个 db 格式的文件 你不用下载这个程序
It’s a “db” file. You don’t have to download this program.
我不是要推荐这个程序 我只是偶尔要浏览数据库的时候用它
I don’t endorse this program at all, I just use it personally sometimes when I want to be in the database.
这个软件就是 SQLite Database Browser 2.0 b1
But it is this “SQLite Database Browser 2.0 b1”.
虽然我们还没有数据 但是这里你可以看到 这就是我们的字段 ID UNIX datestamp 还有 keyword 和 value
Anyway we don’t have any data but you can see that, okay, here are our columns “ID”, “UNIX”, “datestamp” and keyword “value”.
所以数据库就创建好了 现在我们需要填充数据库
So the database is created, now we need to populate database.
Just something else to mention,
一旦你创建好数据库后 就再也不用创建它了
if once you’ve done, this table create, you never need to do it again.
不要再次创建 否则肯定会报错
So don’t do it again, otherwise I’m pretty sure it throws an error.
Can try ahead, just try to do it one more time.
看 跟我说的一样 出现报错信息“stuffToPlot 已经存在”
Yeah exactly a little throw you this operational error, this stuffToPlot already exist.
So you don’t want to create the table again.
So now we want to enter data into a database.
我们需要定义另一个函数 叫做 dataEntry
So we’ll define another function for this and we’ll call it “dataEntry”.
And here it’s just another execute query bascially
那么 c.execute 我们还是需要用操作指针
So “c.execute” and again we’re just referencing conduct cursor.
不好意思 来电话了
Sorry about that. I got a phone call, anyway…
c.execute 好的没事了 c.execute 基本上就是
“c.execute”, yeah we’re just ok, then just referencing bascially
告诉 Python 我们要执行什么命令
just telling python we’re about to execute stuff
现在我们想往数据库里添些数据 其实很简单
So now we’re going to want to put stuff into the database and it’s really quite simply.
The command for this is “INSERT INTO”,
然后你需要写你想往哪个数据表添数据 因为数据库……
and then you need to say what table like where this is going, because a database is just like…
数据库会存储很多张表 表里又会有很多字段
it’s like a database holds a bunch of table and the tables hold on a bunch of columns with
texts and stuff and entries bascially.
所以一旦你执行了 c.execute
So once you have already “c.execute”,
它指向的数据库是 tutorial.db
that already refers to “tutorial.db”.
但还得指明想要执行操作的表 不然程序是不会知道的
But until you tell it what table to put stuff into, it really doesn’t know.
所以还是得告诉它表的名称 这里就是 stuffToPlot
So you have to tell it to put it into whatever table, so it’s “stuffToPlot”.
And then you specify we’re going to put some values in here.
And just for the purposes of showing how to put stuff into a database,
the first time I’m going to show you this way
and what a hard coded query looks like.
But then generally you’re going to need to use some sort of dynamic query,
so I’ll show you how to do that later.
因为问题在于 在Python里 必须使用引号
Because again problem with python is you’ve got those quotes
and everything is… so everything is encased in quotes.
好了 我等会告诉你们怎么使用变量
So anyway I’ll show you guys in a second how to do variables.
所以 这里1是我们的第一个键
So anyways, so one will be our first key,
实际上 让我把剩下的语句复制过来 这样你们就不用等了
actually you know, let me type this stuff out so you guys don’t have to wait for this.
好的 我们已经录入一些值了
Okay so now we’ve got some values to insert into here.
I’ll just go through what they are at least.
这些都是自增的 1 2 3 这是 UNIX 时间戳
This is all these auto increment, one, two, three. This is a UNIX timestamp.
这是 UNIX 时间戳所对应的日期
This is the date time to match that UNIX timestamp.
这是关键词 这里是我们分配的 value 值
This is the keyword and this is the quote unquote value that we’re assigning.
一旦你们将所有数据都插入了表 然后插入一个数据库
Now once you have inserted all the data, then you want to insert into a database.
绝对要记着用 commit 方法
You absolutely must use the commit.
你很可能会忘 那样肯定就要出问题的
You are going to probably forget to do this and it’s going to be a problem.
But at least once or twice.
希望你们能记着加上 conn.commit 命令
But hopefully you’ll remember to add “connection commit” to that.
Because badcially that just like it’s like almost like you entered the data into the file,
now you need to save it into the file.
就是这样 加上 conn.commit
So anyway there’s a connection commit.
然后我们保存 运行
So what we’ll do is save it, we run this.
我们不需要再创建表 已经建过了 所以我们直接调用 dataEntry
And we don’t need to create a table, it already exists. But we will just, we’ll call “dataEntry”.
应该执行完了 我们打开文件
And it probably went, let’s open it up.
这就是我们的 tutorial 数据库 我们要用 SQLite database viewer 打开它
And here’s our tutorial database, we’re going to open it with SQLite database viewer.
浏览一下数据 确实有 ID 1 2 3
Browse data, sure enough we actually do have ID one, two, three,
UNIX 日期戳 datestamp 关键词 keyword 和 value 值
UNIX, datestamp, keyword and value.
So we enter data into the database.
基本上 这就是怎么往数据库录入数据了
That’s the basics of how to enter stuff into a database.
This is hard coded,
so chances are you’re going to want to use dynamic variables of some kind.
So in the next video I’m gonna show you guys how to use
如何用动态变量将数据录入数据库 你就不用再写硬编码了
how to make it a dynamic entry into the database. You don’t have to hard code this,
you can actually make a program that’s doing something and entering stuff into database.
So until next one.