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

用Python使用数据库 2 – 动态插入一个数据库+时间戳 – 译学馆
未登录,请登录后再发表信息
最新评论 (0)
播放视频

用Python使用数据库 2 - 动态插入一个数据库+时间戳

SQL Database with Python Part 2 - Dynamically Inserting into a Database + timestamps

大家好 欢迎回到python教程 上次我们学习了
Hello and welcome back to the Python tutorial where we were learning about using
如何用python操作数据库
SQL database with Python.
上次我们就是讲到这里:向表中插入数据
This is where we left off. Insert data into this table and we
我们做的很好 查看这个表可以看到一切正常
did everything right. We could view the table and everything was hunky-dory.
但是很快我们就发现了 数据库大多都很大
But real quickly we realized that most databases are huge.
如果我们都是像这样手动输入的话 就太可怕了
If we have to enter all this data in like by hand like this. That’s horrible.
显而易见 应该有办法
So obviously it’s got to be some way
用变量来代替这些内容
that you can replace this stuff with variables.
每次调用这个函数都需要输入一些东西
And call this function every time you know you’ve got stuff to input.
但是如果你能花些时间看一下这个
But if you’ve taken a moment and looked at this you realize
就会发现整个查询语录都是在引号里面
this entire query is encased in quotes.
怎么才能在这放一个变量呢?
Now you’re going to…how you put a variable in there.
我来告诉你
Well I’ll tell you how.
首先 我们要做的是删掉这些东西
First what we’re gonna want to do is we’re gonna want to delete all this..
conn.commit可以留下 我们需要用到它
Well we might as well leave conn.commit because we need conn.commit.
你不会想要删除它的
You never want to make it go away.
接下来我们需要一些变量
So the next thing that you want to do is we’re gonna need some variables.
我们需要一个变量来表示ID
So we’re gonna need a variable for ID.
一个变量表示unix时间 一个变量表示date stamp
We’re gonna need a variable for the unix time. We’re gonna need a variable for date stamp.
还需要变量分别表示keyword和value
We’re gonna need one for keyword and one for value.
以上就是我们所需的变量
So various things that we’re gonna need for that.
首先我们需要
Our…first we’re gonna need
unix时间和timestamp 所以在最上面
the unix time and then timestamp, of course. So up at the top
你需要引入time库
you’re gonna need to import time.
还需要引入datetime库
And you’re also gonna need to import datetime.
在这我们可能还会需要其他变量
And then down here with the…maybe above this where we need to find some of other variables.
我们给idfordb赋值为4
We’re just gonna say idfordb = 4.
一般情况下 我们会写像ID+=1这样的语句
Normally you would have something that’s like ID plus equals 1.
实际情况下 每次这些变量被创建时
Something like that. In the actual function like every time the list of variables is created,
ID都会增加1
you’re gonna say the ID is ID plus equals 1.
但现在我们就直接固定ID的值了
But for now we’ll just hard code ID.
给keyword赋值为“Python Sentiment”
Keyword…We’re gonna say the keyword is ‘Python Sentiment’.
正如我们之前所说的一样
So it…you know…agrees with what we’ve already said.
value我们也直接固定为7
And then the value. We’re also gonna hard-code equals 7.
通常情况下你要通过函数获得该值 但现在我们就直接指定了
Normally you’d have some sort of function that’s deriving value but for now we’ll just say what the value is.
现在我们来到这
So I believe that’s here where I we’ve got.
1 2 3 4 5 1 2 3 4 5
1, 2, 3, 4, 5. 1, 2, 3, 4, 5.
现在我们要获取数据项了
Yep okay. So now we need to find data entry.
我们要如何操作数据项呢
So how we’re goona do data entry?
还有一件事 我们需要获得时间 我会告诉你们怎么操作时间
Actually one more thing. We’ve got time and I’ll show you how to do time.
如果你要使用unix的时间戳的话,你需要引入time 我们已经做过了
Time basically…if you want unix timestamp, you need to import time which we’ve done up here.
通过time.time()得到unix时间戳
And the unix timestamp will be time.time(). Like that.
如果你想要把它转换为日期时间戳
If you want to convert that into a date timestamp.
代码会有些不同 为了避免你不熟悉该操作
The code is a little bit different and actually let me just show you Python
我会展示如何用python操作unix时间戳
on the unix timestamp just in case you’re not familiar.
只需引入time 输出time.time()
This is just import time. Print time.time().
可以看到unix时间戳
And see there’s our unix timestamp. Now you look at that
从这什么也看不出来 可能你知道这是本视频录制的时间
you’re not gonna be able to derive anything from it besides maybe you know it’s around the date of this video’s posting.
但是你无法获知它的真正含义 所以你需要对它进行转换
But you don’t really know what that means. So you need to convert that into something.
还有一件事 为了避免你不知道什么是unix时间
Oh and just one more thing. Just in case you don’t really know what unix time is.
对于时间戳的表示
When it comes to like timestamps there’s just part…there’s like
可以有许多不同方式
a bunch of different ways that you can organize it. You could say
你可以像美国人一样使用月/日/年
month/day/year like the Americans. You could say
也可以像其他地方一样使用日/月/年
day/month/year like the rest of the world.
你也可以用月/日/年/时/分
You could say…you could also have like month/day/year
也可以是月/日/年/时/分/秒
hour/minute. You got month/day/year/hour/minute/second.
或时/日/年/月等等 你可以使用任意类型的时间表示方式
hour/day/year/month or hour…you get the point so you can have all kinds of different datestamp. So
所以说日期戳的表示并没有统一的标准
A datestamp isn’t like a universally recognized.
计算机无法理解日期戳的真正含义
It doesn’t understand like what that datestamp means.
所以所有的这些表示方法统一用unix时间来代替
So something that’s universally recognized across all codes and stuff is unix time.
unix时间就是指从1970年1月1日以来的时间秒数
Unix time is just the number of seconds after january 1st 1970 I want to say.
显然 这是一种通用的方式 它们都可以用同一数字表示
And so obviously it’s just…it’s very universal…it’s the same number.
这就是为什么unix时间如此有用的原因
So…Anyway that’s why unix time is useful.
但是最终你需要日期戳 接下来我会告诉你怎么转换成日期戳
But eventually you need to have a datestamp. So I’ll show you how to convert it into a datestamp
你需要daytime库 就是这里引入的daytime
with the following. You need datetime. You’ll need this import of datetime up here.
转换成日期戳 我会在这里计算
And to do a datestamp I think we’ll actually calculate it in here.
在这个函数中 “date=”
In the function date =
你需要把它转换为字符串
and you’re going to want this to be a string of
“datetime.datetime.fromtimestamp”
datetime.datetime.fromtimestamp
现在你需要知道时间戳是多少 用“int(time.time())”获得
Now you’re gonna want to know what the timestamp is. That’s going to be int(time.time())
你可能不需要转换为整型数 它应该已经是整型数了
Now you probably don’t need an integer there. I believe it’s actually an integer already.
但是我不能确定 这个str函数我已经写了很长时间了
But…I can’t remember it’s been so long since I’ve made this string here.
我这样写可能是为了避免出现小数 但我不能确定
This is just what I use…think i’m doing this to get rid of maybe the decimal point. I’m not sure.
如果你封闭了括号
Yeah…if that’s closed off that
你就可以把它当成字符串使用
And then you want…then you use string
就是str(time) 我很确定它代表时间的字符串形式
It’s str(time). I’m pretty sure it stands for string from time.
然后在这里你可以指定
And then in here you specify
它的显示方式
how you want it to show up.
同样 你还可以转换
And you can do the same thing like you can convert like
来自网站的时间 这是很常见的
what happens a lot of times if you’re parsing like websites.
你可以看到时间以日期的形式展示 而不是unix时间
You can see like the posted on date but you don’t actually have a unix time.
你也可以执行这个函数的逆操作
You can do basically the reverse of this function.
你可以阅读该时间
And you can read it and you can say:”ok this…
并指出 它的结构是这样的
this is the organization of this.” and you can say
这是年 这是月 日 小时 分钟
it’s the year, the month, day, hours, minutes.
你就可以把它转换为unix时间
And then you can convert that into a unix timestamp
这才是普遍形式 你可以把它转换为任意形式
which is universal for you and then you invert whatever the heck you want in there.
这是题外话
That’s just an aside
你需要这样做 使用%年
But anyway to do it this way you use %year
之所以用Y 是因为我们想要得到完整的年份 比如2012或2013
You’re gonna use a uppercase Y because that’s a full year we want the 2012 or 2013.
如果用y 就只能得到13
OK? If you just use lowercase y it would just be 13. OK
所以我们用Y 然后是%月
So we’re gonna use an uppercase Y and then month…% month.
如果你用m 只能得到月的数字
I want to say if you use lowercase m. It’s just the digit of the month.
如果用M 就会显示完整的月份名
And if you use an uppercase M it’s like the month written out.
你可以查一下 横线也会显示出来
You could look it up. Oh, also this dash is going to be present.
所以这最终会显示“2012-”
OK. It’s going to say 2012 dash
然后是月 月份数字
and then the month…you know number…
再加一个“-” 然后是%日
And then we’re gonna use another dash and then %day.
空格 %时 就像时钟一样 你可以加上冒号
And then space %hours and then like a clock would do, you have a colon.
以及分钟加上冒号 %s代表分钟
Or say a minutes colon. %s for second.
必须要这样写 大小写很关键
And again you have to keep these things…the case thing matters greatly.
如果你想了解更多各种符号的用法
If you want to learn more about like what each one represents.
你可以上网查询 因为其中每一个都有不同用法
You can you look it up online. Because just one of these is definitely way different.
比如说如果你用小写的s
It might be second like if you use undercase seconds…
它就不再是秒了 而是其他东西
you know it…They don’t even…It’s not even a second anymore. It’s something else.
无论如何
Anyway. So that is…
这就是你的转换器了 我复制粘贴一下 你就可以在python解释器中看到效果了
your converter now. Let me just copy and paste this so you can see within the Python interpreter.
我要做什么呢 我要引入time和datetime
What I’m doing there. So you know we’re gonna import time and import datetime.
粘贴到这 就可以打印出日期了
And we’ll paste that in and so now we’ll print date.
这就是当前时间 我是在2013年5月20日录制的视频
Sure enough that’s our date right now. I’m making this video on the 20th of May 2013.
确切日期是12:36:39
And the time is indeed 12:36 and apparently 39 seconds.
这就是时间的操作方式 你可以以任意方式排列它
So anyways…That’s how you do that and you can just you can organize how this shows up
如果我想的话 我也可以设置也日 月 年
anyway you want. Like I could put day month year if I wanted as well.
这都没关系
So it really doesn’t matter.
说实话 我通常只用这种格式 这样我就可以知道写程序的时间了
I usually just use this format honestly so I can like whenever I’m just building a program.
这是google使用的格式
These datestamps are recognized by Google.
如果你想要制作一个图表 或者是在Google charts里放一些东西
So if you’re trying to develop a chart or something you throw into Google charts.
用这种格式就非常好 这也是我用它的原因
And it recognizes these datestamps really well. So that’s why I use them.
好了 继续 接下来我会告诉你
Anyway moving right along. Now that I’ve tell you guys how to do that.
在表中实际插入一些东西吧
Let’s get into actually how to insert stuff into a table.
c.execute 像前面一样
c.execute same thing as before.
像前面一样 “INSERT INTO”
Same thing here is before as well INSERT INTO
“stuffToPlot” 这是我们的表名
stuffToPlot–that’s our table name.
接下来你要在括号里指定具体是什么
And then what you do in these parentheses is you just specifying what this stuff is.
有ID unix datestamp
You’ve got ID. You’ve got unix. You’ve got datestamp.
keyword 以及value
You’ve got keyword and you’ve got value. All these things like…
这些就是你要指定的各字段
those are the columns that you want to represent.
也是接下来值的顺序 “VALUES”
And that’s the order of the following. The VALUES
然后是?
are going to be question mark.
???应该是有5个 对吧
? ? ? I think there’re five values right?
1 2 3 4 没错 5个值
1 2 3 4. Yes, five values.
这是5个问号 右括号
It’s five question marks. Close parenthesis.
逗号 回车
Comma. Enter.
左括号 这里先不填 然后关闭
Open parenthesis and just forget measure this. Let’s close off the entire
整个查询语句
query we’ve done that. Now…
现在你要写在这的是对应于这些问号的值
What you write in here corresponds to these question marks.
它们是按照这些问号的顺序
These questions…in the order of these question marks.
这些问号对应于
These question marks respond to
这里的各项
these right here in this order.
所以我们要按这个顺序来写 首先是idfordb
So now we need to do it in this order. So idfordb.
接下来需要unix时间 就是time.time
Then we want to have unix time. It’s just time.time.
然后需要日期 就是这个变量
Then we want to have date which is corresponding to this variable. So whenever this is called
无论何时调用 它都会打印出结果 它会执行这条语句 并返回一个字符串
it’s gonna print out this pump. It’s gonna do basically this command and give us a string of that.
然后是keyword 我们已经定义为“Python Sentiment”了
Next is keyword which we’ve defined as “Python Sentiment”already.
我们是直接赋值的 通常情况下应该用函数来为它赋值
And value which we’ve hard-coded. Normally that’s some sort of function defining this value.
然后是value 最后一件事 虽然我们已经写在这了
And we got value. And the last thing, of course, even though we’ve already got it there.
但是你每次都需要注意
You need to think about it pretty much every time.
就是conn.commit 它会把操作提交给数据库 我保存一下然后运行
conn.commit. So it commits it to database and I will save it. We run this.
执行dataEntry函数
We will do data entry.
没有这一项
Oops. That’s an entry without that.
现在我们已经把数据插入到数据库了 给你们看一下
And now we’re gonna enter it in the database. So let’s show you guys.
这是我们的数据库
Here is our database.
看一下数据 我们已经插入了数据 这是ID 这是时间戳
And we’re here browse data. Sure enough ID for we’ve got this. We’ve got this datestamp.
我们也插入了“Python Sentiment”和7.0
We’ve got Python Sentiment and 7.0 for our value.
我们已经把数据插入到数据库中了 而且是动态输入的
So now we’ve entered this stuff into a database and it was dynamically entered.
并不是硬编码 现在你可以定义各种函数了
As opposed to hard coding. So now you can have like all kinds of functions and
无论如何 它动态的
whatever. That’s generating
产生了所有变量
all of these variables dynamically.
每次都会产生新值 调用dataEntry就可以插入到数据库中
Then every time you’ve got the new generation. You call dataEntry and you enter it in the database.
这就是向SQL数据库动态插入数据
So that’s dynamic entry into an SQL database.
下个视频我会告诉你们
The next video I’m gonna show you guys how to
如何从数据库中读取数据 你可以从数据库中获得数据 并操作它
read from the database. So you can pull data from it and then do stuff.
这可能会用到matplotlib教程和python教程
And again I’ll probably have that in both the matplotlib tutorials and then also just the Python
因为我们要把数据画成图
tutorials because what we’re gonna do is plot the data.
你也可以任意操作数据 敬请期待下一节
But you can do anything you want to the data. So anyway stay tuned for the next one.

发表评论

译制信息
视频概述

本节视频介绍了如何用python向数据库中动态插入数据以及时间戳的用法。

听录译者

[B]刀子

翻译译者

[B]hugue

审核员

审核团O

视频来源

https://www.youtube.com/watch?v=KCi8wyT5Xes

相关推荐