{"id":49835,"date":"2020-04-28T06:00:36","date_gmt":"2020-04-28T10:00:36","guid":{"rendered":"https:\/\/www.shamusyoung.com\/twentysidedtale\/?p=49835"},"modified":"2020-04-29T03:55:03","modified_gmt":"2020-04-29T07:55:03","slug":"scraping-part-2-full-control","status":"publish","type":"post","link":"https:\/\/www.shamusyoung.com\/twentysidedtale\/?p=49835","title":{"rendered":"Scraping Part 2: Full Control"},"content":{"rendered":"

So there are thousands of webpages that have information we want. When faced with this problem, ancient civilizations used to go to these pages using Internet Explorer 6 and copy the data into Notepad. We don’t know what they did with it after that, because they got eaten by Woolly Mammoths or conquered by Mongols or whatever. I’m not a historian so I might be slightly off with my timeline, but you get the basic idea: The past was hard<\/em>.<\/p>\n

But now we have these newfangled web scrapers that can surf the web for you and harvest whatever data you like. The problem is that putting the data into Notepad isn’t terribly helpful. Great, now you have an enormous text file of random facts. Are you going to sit down and read it manually? Probably not. So what do we do? Write another<\/b> program to read that<\/b> file? You need to turn this text into data sooner or later, and to do that we need to put it into a database.<\/p>\n

<\/p>\n

Honestly, I’m not totally sure what I’m trying to accomplish with this project. All I know is that I want to put this data into a format where I can manipulate it and look for patternsAnd actually, I’m just trying to do something plausibly useful in C#.<\/span>. To do this, I need to stick the information into a database. <\/p>\n

Databases are Cool<\/h3>\n

I couldn't find a stock photo of an abstract concept of a database, so here's a random woman leaning against the windows of the server room in a way that looks cool but is probably against some stupid rule.<\/div>
I couldn't find a stock photo of an abstract concept of a database, so here's a random woman leaning against the windows of the server room in a way that looks cool but is probably against some stupid rule.<\/div><\/p>\n

A database will let you do a query like:<\/p>\n

“In alphabetical order, list the title of all PC games that came out between 2006 and 2010 that scored more than 50 on Metacritic.”<\/p>\n

In fact, that exact question would look like this:<\/p>\n

SELECT title FROM `games` WHERE YEAR(release_date) >=2006 and \r\n  YEAR (release_date) <= 2010 and score_critic>50 and \r\n  platform=\"PC\" ORDER BY title\r\n<\/pre>\n
(Currently, 777 games in my database meet this criteria.)<\/p>\n
It’s not quite plain English, but I still find the language of SQL to be amazingly readable.<\/p>\n
I’ve talked about an API before. “Application programming interface”. Basically, it’s a way for different systems to talk to each other. Normally I’d have to hunt down an API for this, but since we’re working in C# I evidently already have it, it’s effortless to use, and it’s really well-documented. <\/p>\n
I look around nervously. Is this okay? Am I cheating somehow? What’s the catch hereActually, the catch is that I don’t get access to the low-level power of C++, but on a project like this that’s not even a disadvantage.<\/span>? In the last entry someone asked why I didn’t use Python for this. The obvious answer is that I’m not fluent in Python, which would slow me down. The less obvious answer is that I can’t imagine how it can get any easier than this. <\/p>\n
Anyway. Back to using an API…<\/p>\n
Let’s say you’re on Twitter even though it’s terrible<\/a>. Let’s also say you want to reply to some random tweet<\/a>. You want to reply with something witty and incisive. So you hit the reply button and type “no your wrong dumass lol” into the text box. Then you hit the “Send” and smile with satisfaction, knowing that you’ve once again enriched the world with your boundless wisdom.<\/p>\n
But let’s say you’re using an API so a program can spread your potent wisdom-nuggets far and wide. I don’t know what the real Twitter API looks like and I couldn’t possibly care enough to look it up, but here’s a mock-up:<\/p>\n
\r\n\/\/I don't know why I hardcoded this to reply to a single tweet.\r\n\/\/Maybe I should re-think this design.\r\nint reply_to_id = 1227288980443426818;\r\nstring message = \"no your wrong dumass lol\";\r\nTweetSend (reply_to_id, message);<\/pre>\n
Whatever. Something like that. Maybe if you wanted to send a tweet that wasn’t a reply you could set the reply message to 0.<\/p>\n
\r\nTweetSend (0, \"why do I have so few followers when these rich sexy famous ppl get so many this system is so rigged lol\");\r\n<\/pre>\n
Obviously a real bot would need to do complicated stuff like logging in<\/b> before it could initiate wisdom-sharing mode, but you hopefully get the idea. The controls that are normally available to users are turned into bits of code that your program can call.<\/p>\n
What I find fascinating is that the SQL API doesn’t work this way. If you want a list of all PS4 games in my database, you’d type this into the SQL console:<\/p>\n
SELECT title FROM `games` WHERE platform='PS4'<\/pre>\n
But if you’re a program, then you would accomplish that by… doing the exact same thing. The syntax of SQL is so ridiculously complex that…<\/p>\n
Actually, it would be easier if I just showed you:<\/p>\n
SELECT\r\n\r\n[ALL | DISTINCT | DISTINCTROW ]\r\n\r\n[HIGH_PRIORITY]\r\n\r\n[STRAIGHT_JOIN]\r\n\r\n[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]\r\n\r\n[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]\r\n\r\nselect_expr [, select_expr] ...\r\n\r\n[into_option]\r\n\r\n[FROM table_references\r\n\r\n[PARTITION partition_list]]\r\n\r\n[WHERE where_condition]\r\n\r\n[GROUP BY {col_name | expr | position}\r\n\r\n[ASC | DESC], ... [WITH ROLLUP]]\r\n\r\n[HAVING where_condition]\r\n\r\n[ORDER BY {col_name | expr | position}\r\n\r\n[ASC | DESC], ...]\r\n\r\n[LIMIT {[offset,] row_count | row_count OFFSET offset}]\r\n\r\n[PROCEDURE procedure_name(argument_list)]\r\n\r\n[into_option]\r\n\r\n[FOR UPDATE | LOCK IN SHARE MODE]\r\n\r\n\r\ninto_option: {\r\n\r\nINTO OUTFILE 'file_name'\r\n\r\n[CHARACTER SET charset_name]\r\n\r\nexport_options\r\n\r\n| INTO DUMPFILE 'file_name'\r\n\r\n| INTO var_name [, var_name] ...\r\n\r\n}<\/pre>\n
Warning: Don’t actually try to read all of that. It’s not what it says that matters, it’s how much space it takes up. That huge block of impenetrable options is the documentation for the SQL SELECT statement. Just SELECT! On top of that are countless other commands you can use, each with their own complex syntax and list of options.<\/p>\n
Each line above represents an option for obtaining, filtering, sorting, comparing, and presenting information. Trying to turn all of that into a proper API would be a nightmare. And documenting that API would be another, extra nightmare.<\/p>\n
So rather than create this endless collection of functions to represent all the possible things you can do with SQL, the entire API just shrugs and allows you to send it blocks of text like a user. It’ll even return the same error codes if you make the same mistakes.<\/p>\n
This is completely hilarious to me. <\/p>\n
Perhaps an Analogy Will Help<\/h3>\n
Ugh. I'm glad I never had to work in a place like this. The GERMS, the noise, the lack of privacy, and limited personal space would make me feel like cattle.<\/div>
Ugh. I'm glad I never had to work in a place like this. The GERMS, the noise, the lack of privacy, and limited personal space would make me feel like cattle.<\/div><\/p>\n
Imagine this scene:<\/p>\n
INT – Generic Tech Company Office – FRIDAY AFTERNOON<\/p>\n
According to the clock on the wall, it’s late afternoon. The colorless, windowless office is mostly empty. A young coder sits at his desk, wearing a wrinkled shirt and a leftover 70s tie that’s hanging at half-mast. He’s propped up on one elbow and typing with one hand, searching for images of sunshine and beaches. He keeps slamming into the office’s automated content blocking system.<\/p>\n
Suddenly, THE BOSS strides out of her office. She’s wearing an expensive suit with sunglasses perched on her forehead. She’s got a head of meticulously cared-for Karen hair and face incapable of smiling. She pulls the keys to her sports car out of her handbag and walks past Coder without a word.<\/p>\n
Once she’s halfway to the door, she stops and turns.<\/em><\/p>\n
BOSS:<\/h4>\n
Oh! I almost forgot… I have something I need you to do.<\/p>\n
CODER:<\/h4>\n
(Stirs from his catatonic state.) What, me?<\/p>\n
BOSS:<\/h4>\n
What was your name again?<\/p>\n
(CODER opens his mouth to answer, but she dismisses him with a hand-wave.)<\/em><\/p>\n
BOSS:<\/h4>\n
Whatever. That’s not important right now. We need you to make an interface for a self-driving car.<\/p>\n
CODER:<\/h4>\n
(Looks at calendar.) Uh… really? How long do we have?<\/p>\n
BOSS:<\/h4>\n
Until Monday, so you’ve got the whole weekend if you need it. (She swings down her sunglasses and pivots towards the door.)<\/p>\n
CODER:<\/h4>\n
Wait! I mean… (flounders for a few seconds) What are the specs? What does the user need to be able to do?<\/p>\n
BOSS:<\/h4>\n
(She sighs and reluctantly turns back to face him.) I think that should be obvious. They just need to be able to tell the car where to go. If they want to go to the spa, it’ll take them to the spa. Or the beach. Or home. Whatever.<\/p>\n
CODER:<\/h4>\n
Yeah, but what level of granularity do they need? Do they need to be able to specify specific routes? Choose between scenic or fast routes? Do they need to control the speed? Climate control? The radio? The horn? Lights?<\/p>\n
BOSS:<\/h4>\n
Full control.<\/p>\n
CODER:<\/h4>\n
Full control? Like, what do you mean?<\/p>\n
BOSS:<\/h4>\n
(Firmly.) They should be able to direct the car to do anything that they could do themselves.<\/p>\n
She spins on her heel and heads for the exit. A blinding beam of golden sunlight enters the room as she shoves open the door, which gradually fades again as it eases closed. There’s a dull thud as it hits home, and now it somehow seems even darker in here than before she left. <\/p>\n
Coder stares shell-shocked at his screen, motionless. The office is now silent aside from the ticking of the clock.<\/p>\n
We hold on this angle for an EXCESSIVELY LONG TIME as the camera very gradually dollys in. Finally we stop just as his face fills the view. Finally, he smiles slightly and begins nodding his head.<\/p>\n
SLAM CUT TO:<\/p>\n
INT – Generic Tech Company Office – MONDAY MORNING<\/p>\n
Boss is looking at the phone app that Coder created over the weekend. It’s the image of a driver’s view of a car interior, evidently drawn freehand in MS Paint. It shows the wheel, pedals, gear shift, console controls, etc. All the little controls are animated when you swipe \/ tap on them. This is connected to the car on a 1:1 basis, so you control the “self driving” car by manipulating this picture of the driver’s seat.<\/p>\n
Furious, Boss storms out of her office and over to Coder’s desk. His chair is empty, and his PC is running a slideshow of beach selfies. There’s a sticky note on his monitor that says “FULL CONTROL”.<\/em><\/p>\n
– Fin<\/p><\/div>\n
I suppose I made the SQL API sound bad with this analogy. I actually quite like it. I just find it hilarious that the “interface” is simply direct access to the human controls. It does indeed give the programmer “full control”, but this isn’t how an API is normally designed.<\/p>\n
But here it works. In fact, I can’t imagine how this system could be improved. Most of the APIs I’ve used have fallen into one of two broad categories:<\/p>\n
    \n
  1. Low-level systems like rendering, sound, and device input. An API like this will tend to be sort of inscrutable in its design and very exacting about what you can do and how your input needs to be formatted. The API is generally a small number of highly technical functions. They’re built around a vanilla C paradigm where you need to share direct memory pointers with the software.<\/li>\n
  2. High-level stuff like the user interface. The API has many, many functions to remember, but they’re all pretty obvious in their usage. These APIs tend to adhere to a very strict OOP<\/a> design.<\/li>\n<\/ol>\n
    But SQL is something else entirely. It’s possible that there are a lot of APIs like this out there, but I’ve never run into them because of my quasi-gamedev background. The SQL query language is really complex and powerful, so the programmer is simply given the freedom to use that language to communicate with the database. <\/p>\n
    So now we need to pull some data out of our database, so the first thing we need to do is-<\/p>\n
    Am I Forgetting Something?<\/h3>\n
    Hang On, I seem to have skipped a step here. We can’t take anything OUT of the database until we put something INTO the database. And we can’t put anything into the database until we scrape for the data.<\/p>\n
    I’m afraid I’ve led you astray in this entry. Next time we’re going to back up and harvest our data from the web. Hopefully we can do this without getting caught or banned by the sites we’re scraping. We’re definitely getting close to Grey Hat<\/a> territory here, particularly if my bot is ill-behaved.<\/p>\n
    I promise the next entry will be less flagrantly disorganized.<\/p>\n","protected":false},"excerpt":{"rendered":"
    So there are thousands of webpages that have information we want. When faced with this problem, ancient civilizations used to go to these pages using Internet Explorer 6 and copy the data into Notepad. We don’t know what they did with it after that, because they got eaten by Woolly Mammoths or conquered by Mongols […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66],"tags":[],"class_list":["post-49835","post","type-post","status-publish","format-standard","hentry","category-programming"],"_links":{"self":[{"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=\/wp\/v2\/posts\/49835","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=49835"}],"version-history":[{"count":30,"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=\/wp\/v2\/posts\/49835\/revisions"}],"predecessor-version":[{"id":49865,"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=\/wp\/v2\/posts\/49835\/revisions\/49865"}],"wp:attachment":[{"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=49835"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=49835"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=49835"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}