The first time you use STEEMSQL to query the valley name data.

 

First thanks to @arcange for providing SteemSQL services, SteemSQL is a public MS-SQL database containing STEEM block chain data.

First thanks @arcange for providing SteemSQL services, SteemSQL is a public MS-SQL database with all the blockchain data in it.

In fact, SteemSQL on-line at the beginning I immediately tried, but because the first edition only contains some of the chain of the transaction data, there are many inconvenience to use, so then SteemData on the line, I transferred the battlefield, although I never contact Over MongoDB. After that, SteemData has been able to meet my needs, so it is too lazy to fight back, but I still quietly concerned about this project, watching it after more than 10 times to upgrade getting better and better, watching a lot of STEEMIT friends began to use this database.

Today @ jubi and I said that the use of SteemSQL query Chinese title encountered some difficulties, with WHERE title like '%%' as the query conditions, query the relevant data, but in English can query, I promised to help him see The Saying that he recently held two sessions in the Chinese district Gu Gu name activities, popular hot, and the first is the second phase of the theme of the event. It is estimated that too hot, the number of participants, simply rely on artificial look and finishing some hard, so he thought to use the program to sort the post automation Everybody is growing up, it's very good. Speaking of SteemSQL recently, many of the Chinese friends are in use, such as @ ace108, and @joythewanderer, they also had some guidance stickers, we are interested can be doubled.

And pull away, get down to business. 

Install pymssql

I am too lazy to go to the next desktop software, or directly with Python it
Python can use pyodbc and pymssql Han, I chose is pymssql

Directly install the command directly from the command line
Pip3 install pymssql

Ga, reported a lot of mistakes, the other Han do not know, I focus on about three

Running setup.py bdist_wheel for pymssql ... error
setup.py: Not using bundled FreeTDS
_mssql.c:266:22: fatal error: sqlfront.h: No such file or directory 

Go to the official website check a bit
http://pymssql.org/en/stable/building_and_developing.html
There is such a sentence

FreeTDS >= 0.91 including development files. Please research  your OS usual software distribution channels, e.g, freetds-dev or  freetds-devel packages.

Lack of what make up Han
sudo apt-get install freetds-dev
pip3 install pymssql 

success!

carried out
SteemSQL official website address: http://steemsql.com/
But unfortunately the link above the information is incomplete, no Database information
Perhaps even after the link, you can directly check out, but I posted in the SteemSQL post to find the connection information

• Server: sql.steemsql.com
• User: steemit
• Password: steemit
• Database name: DBSteem

import pymssql
conn = pymssql.connect(host ="sql.steemsql.com",database ="DBSteem",user="steemit",password="steemit")
In Python with this directly connected, very good.

Try to check it out
cur = conn.cursor()
cur.execute("SELECT title FROM Comments WHERE author= 'oflyhigh' and title like'%Blockchain%'")

Because I just wrote an article yesterday,
Use PHP to query STEEM blockchain

Why can not find it?

Then change the keyword
cur.execute("SELECT title FROM Comments WHERE author= 'oflyhigh' and title like '%PHP%'")

('How to install Apache, MYSQL, PHP5 and run a Wordpress website on BananaPi'), ('dragged on for a long time is finally done, upgrade PHP 5.4 to PHP 5.6'), ('use PHP query STEEM area Chains / Using PHP to query the STEEM blockchain ',)

Check out the article contains my latest post

Investigation
So where is the problem?
My first intuition is coding

Then I changed the link
conn = pymssql.connect(host ="sql.steemsql.com",database ="DBSteem",user="steemit",password="steemit", charset="utf8")
The result is still the same

And then to the FreeTDS configuration file with character set settings
sudo find / -name freetds.conf
sudo vi /etc/freetds/freetds.conf
sudo vi /usr/share/freetds/freetds.conf
Add together:
client charset = UTF-8
The result is still the same

Although I do not think so, but also in the Python file added
#-*- coding: utf-8 -*-
The result is still the same

From the results can be detected from the normal view, the data should be stored according to UTF-8, where the problem lies? Three board ax run out, I find a new tricks.

Study half a day, found a new thing, that is, before the contents of the query to add a N
See details
https://docs.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql

Prefix Unicode character string constants with the letter N.  Without the N prefix, the string is converted to the default code page  of the database. This default code page may not recognize certain characters.

Change my query to:
cur.execute("SELECT title FROM Comments WHERE author= 'oflyhigh' and title like N'%Blockchain%'")

The results are as follows:

I have also ever written, 18 years ago to write the poem "flowers", learn laoyao, we also put the chain
Use PHP to query STEEM blockchain
Cherish the feather / STEEM block chain faithfully record your operation / get the real operator of the co-operation account
YY A chat tool based on STEEM block chain
STEEM block chain faithfully record your words and actions

Finally feasible

And then check the Google name paste

Now in the back to check the Google to participate in the second event of the post, the theme for the first time

cur.execute("SELECT title,created FROM Comments WHERE title like N'%第一次%' and created > '2017/08/01' order by created")
rows = cur.fetchall()
for row in rows: print("{}\t{}".format(row[0], row[1]))

The results are as follows:

Magic Clay What did you do with clay for the first time? 2017-08-01 14:13:45
The first time to earn the proceeds from steemit! The first time I get the 'real' money from steemit! 2017-08-02 06:11:21
The first dialogue with you, my first conversation with y'all. 2017-08-03 18:32:51
For the first time in steemit cn Thanks for steemit let me touch Bitcoin 2017-08-04 17:09:18
The first human anatomy class thriller event 2017-08-07 11:30:48
The first time - leave the country Well, "Valley brother named" activities 2017-08-09 07:10:30
Similar to the United States and Xinjiang, the first time in the 13th National Games Winter Games Meet the beauty of Xinjiang, first time in China national winter games 2017-08-09 08:31:15
My first encounter with steemit / my first encounter with steemit | Google named 2017-08-09 10:15:33
My life of the first N kinds of 2017-08-09 10:31:48
The first time to know the world 2017-08-09 10:52:12
The first time - the British song "Google named" activities Hiking South Downs Way England 2017-08-09 11:00:00
I'm in Steemit's first post 2017-08-09 13:07:39
The first time to leave the familiar place out of the battles 2017-08-09 14:12:33
Achieve the first time in life 2017-08-09 14:26:54
First time first time 2017-08-09 17:11:42
Life for the first time - not only ups and downs, more joys and sorrows 2017-08-09 18:12:18
First time - The First Time (Chinese content only but feel free to comment in English if you understsand) - (by @ ace108) 2017-08-10 00:34:03
For the first time, look at her mother 's wrinkles | "Valley brother named" activities 2017-08-10 01:11:24
Love the first time - FIR Exploration 2017-08-10 01:22:15
How to treat your life for the first time? 2017-08-10 01:24:48
?? ?? Teahouse Road - please sit, a cup of tea, say something; topic today - for the first time (cross-domain collaboration: X teahouse named Columbia Valley) ?? LAODR Tea House ?? 170810 2017-08-10 02 : 06: 03
# Essay 2 first time | Valley brother named 2017-08-10 03:55:33
The first time in the star hotel to eat 2017-08-10 04:09:12
The first first, but last love, the last love
The first taste of life. My First Time - Ups and Downs in Life 2017-08-10 05:10:51
The first time - I and my friends 2017-08-10 05:14:57
The first time to get the money written on steemit 2017-08-10 05:44:24
Lead, today is my first drink, can you The The 2017-08-10 06:52:42
The ancient wine cups of the Chinese history for the first time to go to the museum. Ancient wine cups of the Chinese history 2017-08-10 11:40:54
The first time to rape to play, planted the tree in the soul 2017-08-10 13:56:00
My first and long leave from my hometown // first time to leave the town of Wells 2017-08-10 18:45:03
The first time i made my rose steamed buns for the first time to do roses bread! 2017-08-10 21:50:18
First time - live in college dormitory experience / Gu Ge name activities | My university life, my first time living in a university hostel 2017-08-10 23:03:06
First time - in the first family trip - Taichung (within the family travel experience) | The first time - My first family trip Tai Chung 2017-08-11 01:16:42
Many first time for the first time | "Valley brother named" 2017-08-11 01:30:00
The first time "experience" to do the feeling of parents / The First Time for Being a "Parent" | Google named 2017-08-11 04:10:27
The first time to the Chinese area of

the scene and the Chinese area how to develop 2017-08-11 06:23:21
The first registration domain name 2017-08-11 08:18:54
For the first time, for the first time in scientific research, "The first time:" The Tate Time for Scientific Research 2017-08-11 09:15:57
The first time to the beach - "Wenliang Gongjian let" the North Wales Beach town Llandudno 2017-08-11 09:28:42
The second issue of "Valley brother named" activities: the first time to leave the country well 2017-08-11 09:36:03
My first virtual life 2017-08-11 10:22:18
Two of the first time in the day 2017-08-11 10:37:51

Finally did not live up to the trust of @jubi friends

supplement
@jubi with another way
WHERE contains (title, 'first')
You can find some data
I searched for it, contains a query based on full-text index
But the results may be affected by systematic word segmentation

Of course, it may be that I will not use the reason

Also add the first time the keyword entry, focusing on participation
In addition, in case of winning it, ha ha ha

@raviraj4you