使用pymssql以及STEEMSQL的一点经验

2 months ago

昨天写了个STEEMSQL相关的帖子

主要介绍了如何安装pymssql以及用中文关键字查询数据
因为我对MSSQL以及pymssql都不熟悉,所以使用过程中遇到了很多问题,限于篇幅,昨天没有过多讲述,今天整理一下,希望学习pymssql或者想使用STEEMSQL的朋友少走一些弯路。

查询所有数据库名 / Query all database names

昨天我们帖子中说了
SteemSQL 官网地址: http://steemsql.com/
但是很遗憾上边的链接信息不全,没有Database的信息

其实这不是大问题,因为数据库信息是可以自行获取的,查询语句如下:
SELECT Name FROM Master..SysDatabases ORDER BY Name

示例代码

import pymssql
conn = pymssql.connect(host ="sql.steemsql.com",user="steemit",password="steemit", charset="utf8")
cur = conn.cursor()
cur.execute("SELECT Name FROM Master..SysDatabases ORDER BY Name")
rows = cur.fetchall()
for row in rows: print(row[0])

结果如下:

DBSteem
master
tempdb

好了,DBSteem就是我们要的数据库啦。

(测试了一下,不选择数据库,居然也可以执行正常的查询,比如查询Comments表下的数据,莫非有默认设置?)

查询数据库中所有数据表 / Query all data tables in the database

我们知道了DBSteem是我们要用到的数据库,但是这个库下边都有哪些表呢?

查询语句如下:
SELECT Name FROM DatabaseName..SysObjects Where XType='U' ORDER BY Name

  • XType='U':表示所有用户表;
  • XType='S':表示所有系统表;

试着查一下DBSteem下的用户表
cur.execute("Select Name From DBSteem..SysObjects Where XType='U' order By Name")
(查询当前数据库可以省略路径DBSteem..)

结果如下:

Accounts
Blocks
Comments
Tokens
Transactions
TxAccountCreates
TxAccountRecovers
TxAccountUpdates
TxAccountWitnessProxies
TxAccountWitnessVotes
TxClaimRewardBalances
TxComments
TxCommentsOptions
TxConverts
TxCustoms
TxDelegateVestingShares
TxDeleteComments
TxEscrowApproves
TxEscrowDisputes
TxEscrowReleases
TxEscrowTransfers
TxFeeds
TxLimitOrders
TxPows
TxTransfers
TxVotes
TxWithdraws
TxWithdrawVestingRoutes
TxWitnessUpdates
VOAuthorRewards
VOCurationRewards
VOFillConvertRequest
VOFillOrders
VOFillVestingWithdraws
VOInterests
VOShutdownWitnesses

这样我们就知道哪些表可用啦。

查询数据表中的字段 / Query fields in a data table

知道数据库了,知道数据表了,我们还要知道表中都有哪些字段,才能方便我们查询,不是吗?

查询语句如下:
SELECT Name FROM DatabaseName..SysColumns WHERE id=Object_Id('TableName')

来试试Comments表
cur.execute("SELECT Name FROM DBSteem..SysColumns WHERE id=Object_Id('Comments')")
(查询当前数据库下的表可以省略路径DBSteem..)

结果如下:

abs_rshares
active
active_votes
allow_curation_rewards
allow_replies
allow_votes
author
author_reputation
author_rewards
beneficiaries
body
body_language
body_length
cashout_time
category
children
children_abs_rshares
created
curator_payout_value
depth
dirty
ID
json_metadata

last_payout
last_update
max_accepted_payout
max_cashout_time
mode
net_rshares
net_votes
parent_author
parent_permlink
pending_payout_value
percent_steem_dollars
permlink
promoted
reblogged_by
replies
reward_weight

root_comment
root_title
title
total_payout_value
total_pending_payout_value
total_vote_weight
TS
url
vote_rshares

查询数据表中的字段详细信息 / Query fields details in a data table

有了上述基础,我们可以进一步查询字段的详细信息
TxComments表为例:
cur.execute("SELECT SysColumns.Name,SysTypes.Name,SysColumns.IsNullable,SysColumns.Length FROM SysColumns, SysTypes WHERE SysColumns.XUserType = SysTypes.XUserType AND SysColumns.Id = object_id('TxComments')")

结果如下:

ID              int             0       4
tx_id           int             0       4
author          varchar         0       50
permlink        varchar         0       512
parent_author   varchar         0       50
parent_permlink varchar         0       512
title           nvarchar        0       -1
body            nvarchar        0       -1
json_metadata   varchar         0       -1
timestamp       datetime        0       8

结论 / Conclusions

我们探讨了如何通过SQL语句

  • 查询数据库名 / Query all database names
  • 查询数据库中所有数据表 / Query all data tables in the database
  • 查询数据表中的字段 / Query fields in a data table
  • 查询数据表中的字段详细信息 / Query fields details in a data table

通过了解这些信息,便于我们更好的实现我们的查询程序。
图形界面的程序,应该都会内置这些功能,大家不想深入了解的话,直接使用图形界面程序最方便了。


以上内容仅供参考,如有错漏欢迎指正。

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  trending

Thanks :)

有空了我也参照你这文章研究下

can i find somewhere the english version?

·

Posted it. check out my reply it is just google translate but I think it gets the job done.

hello my dear friend i like computer thanks alot please visit my page and upvote me i need help you

English Translation (Google Translate):

Yesterday wrote a STEEMSQL related posts

The first time you use STEEMSQL to query the valley name data
Mainly describes how to install pymssql and use Chinese keywords to query data
Because I am not familiar with MSSQL and pymssql, so the use of the process encountered a lot of problems, limited space, yesterday did not tell too much, today finishing, want to learn pymssql or want to use STEEMSQL friends take some detours.

Query all database names / Query all database names
Yesterday we said in the post
SteemSQL official website address: http://steemsql.com/
But unfortunately the link above the information is incomplete, no Database information

In fact, this is not a big problem, because the database information can be self-access, the query is as follows:
SELECT Name FROM Master..SysDatabases ORDER BY Name

Sample code

The results are as follows:

DBSteem
Master
Tempdb
Well, DBSteem is the database we want.

(Test a bit, do not choose the database, actually can also perform the normal query, such as query the parameters under the Comments table, does it have a default setting?)

Query all data tables in the database
We know that DBSteem is the database we want to use, but what are the tables below the library?

The query is as follows:
SELECT Name FROM DatabaseName..SysObjects Where XType = 'U' ORDER BY Name

XType = 'U': represents all user tables;
XType = 'S': represents all system tables;
Try to check the user table under DBSteem
Cur.execute ("Select Name From DBSteem..SysObjects Where XType = 'U' order By Name")
(Query the current database can omit the path DBSteem ..)

The results are as follows:
Accounts
Blocks
Comments
Tokens
Transactions
TxAccountCreates
TxAccountRecovers
TxAccountUpdates
TxAccountWitnessProxies
TxAccountWitnessVotes
TxClaimRewardBalances
TxComments
TxCommentsOptions
TxConverts
TxCustoms
TxDelegateVestingShares
TxDeleteComments
TxEscrowApproves
TxEscrowDisputes
TxEscrowReleases
TxEscrowTransfers
TxFeeds
TxLimitOrders
TxPows
TxTransfers
TxVotes
TxWithdraws
TxWithdrawVestingRoutes
TxWitnessUpdates
VOAuthorRewards
VOCurationRewards
VOFillConvertRequest
VOFillOrders
VOFillVestingWithdraws
VOInterests
VOShutdownWitnesses

So we know which tables are available.

Query the fields in the data table / Query fields in a data table
Know the database, and know the data table, we have to know what the table field, in order to facilitate our inquiries, is not it?

The query is as follows:
SELECT Name FROM DatabaseName..SysColumns WHERE id = Object_Id ('TableName')

Try the Test Table
Cur.execute ("SELECT Name FROM DBSteem..SysColumns WHERE id = Object_Id ('Comments')")
(Query the current database under the table can be omitted path DBSteem ..)

The results are as follows:

Abs_rshares
Active
Active_votes
Allow_curation_rewards
Allow_replies
Allow_votes
Author
Author_reputation
Author_rewards
Beneficiaries
Body
Body_language
Body_length
Cashout_time
Category
Children
Children_abs_rshares
Created
Curator_payout_value
Depth
Dirty
ID
Json_metadata

last_payout
last_update
max_accepted_payout
max_cashout_time
mode
net_rshares
net_votes
parent_author
parent_permlink
pending_payout_value
percent_steem_dollars
permlink
promoted
reblogged_by
replies
reward_weight

root_comment
root_title
title
total_payout_value
total_pending_payout_value
total_vote_weight
TS
url
vote_rshares

Query field in the data table details / Query fields details in a data table
With the above basis, we can further query the details of the field
Take the TxComments table as an example:
Cur.execute ("SELECT SysColumns.Name, SysTypes.Name, SysColumns.IsNullable, SysColumns.Length FROM SysColumns, SysTypes WHERE SysColumns.XUserType = SysTypes.XUserType AND SysColumns.Id = object_id ('TxComments')")

The results are as follows:

ID int 0 4
Tx_id int 0 4
Author varchar 0 50
Permlink varchar 0 512
Parent_author varchar 0 50
Parent_permlink varchar 0 512
Title nvarchar 0 -1
Body nvarchar 0 -1
Json_metadata varchar 0 -1
Timestamp datetime 0 8
Conclusions
We explored how to pass SQL statements

Query the database name / Query all database names
Query all data tables in the database
Query the fields in the data table / Query fields in a data table
Query field in the data table details / Query fields details in a data table
By understanding this information, we can better implement our query process.
Graphical interface procedures, should be built-in these features, we do not want to understand the words, the direct use of graphical interface program the most convenient.

The above content is for reference only, if any errors welcome correction.

(

我不是這方面人才,只好默默支持😂

·

😄我也不是人才,初学者而已

·
·

已比我強了~枉我當年还是电子工程本科...完全看不懂...

I agree with everything you say here, very good post.I would like to know why if you actually think all this happens why have you no confidence in Steem?You transfer everything you make in SBD to an exchange to dump as soon as you make it here.You are not in full power up for the long haul and huge gains you speak of, but you believe this all blows up to mega numbers from where it is now?

区块信息是不是就是在block这个表里啊。那是不是可以通过这个知道指定用户 某段时间的所有操作

·

我还没仔细研究
不过直接通过Block查操作可能效率不高

通过TxXXXXX这些表查对应的操作会比较高效

신청합니다 !

You Just Keep Amazing Me With Your Ability To Use Multiple Programming Languages And Passing Your Knowledge To The Steemit Community, Thanks Alot @oflyhigh :)

谢谢分享^^

muchas gracias!!

Thanks !! Learning more every day... half of it I still don't understand, but I'm sure I'll figure it out eventually... lol :)

还好用SQLDeveloper没有这样烦恼但是谢谢因为收集了做资料。:-)

EEverything about Chineeze is fascinating

Damn I thought this was a new vaporwave artist... :P

Congratulations @oflyhigh!
Your post was mentioned in the hit parade in the following category:

  • Pending payout - Ranked 7 with $ 311,06