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
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.
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.
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
you’re gonna need to import time.
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.
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,
you’re gonna say the ID is ID plus equals 1.
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.
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.
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
on the unix timestamp just in case you’re not familiar.
只需引入time 输出time.time()
This is just import time. Print time.time().
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.
So something that’s universally recognized across all codes and stuff is unix time.
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.
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
现在你需要知道时间戳是多少 用“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.
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.
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.
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.
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.
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.
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.
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.
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.
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.