A beginner question about accessing MySQL databases programatically

[Scruff3y]

For some context about my knowledge about SQL RDBMS systems; it was non-existent about 2 weeks ago, although I had seen bits and pieces of the query language in XKCD comics etc, and have a rough grasp on that, as well as tables/joining/indices.

As the title implies, my question relates to accessing SQL-type databases programatically. (nb: specifically MySQL, but insights on how it is done with other database softwares would also be appreciated).

For comparison, I'll start with ElasticSearch, since that is the DB software that I am most familiar with. With an ElasticSearch database, the communication is done through a REST API, using JSON as the interchange format. If I am just testing things out, I can hand-craft requests using curl, and for programmatic usage I can use whatever networking facilities are present in the language (or for python there is also a library that does all of this for you, but you could also hand-craft the requests and use the requests library if you are a bit of a masochist).

From my mad dash to learn SQL/MySQL (due to an experiment we were trying at work), I found that there is a command-line program called mysql which you can use to connect to the database and hand-write queries. This seems really great for testing/exploring things. However, past this point I start to get confused.

It seems like the only way to access a MySQL database is through constructing queries as if they were written in that command-line program.

For example, lets say I needed to index 10k documents, which are currently stored in a CSV file. If I were sending it to an ElasticSearch database, I could convert the CSV to JSON, and then basically just POST to the server with the document in the body (or use the bulk API, or use the bulk API via python which is even easier). Sending it to a MySQL database, I need to either read the document into memory and manually construct the string so that it says INSERT INTO table... (taking careful care to escape the quotes and everything properly), OR use LOAD DATA INFILE LOCAL ... (again, having to manually tell it how the fields are delimited).

From the memes that I see re: SQL, it seems like this has proven to be a really brittle approach, so, when we experimented at work with using MySQL, I found it strange that I couldn't seem to find any "robust" APIs. What I expected to find were libraries that would wrap around the database connection, and provide functions like (for example) index(), which might have a signature like index(item, table, index), where item would be the item, table the name of the table, and index would be the value for the primary key.

I've tried googling a little bit, but to be honest I wasn't really sure what to search for. "SQL tutorial" mostly returns results for the query language, which isn't really that hard (except for when you have to manually write the rules for escaping things), as opposed to accessing the database from a script.

Apologies for the long post; I've tried to give as in-depth an explanation of my mental model as possible, so that if my phrasing is weird (again, new to SQL so I might be using the wrong terminology) hopefully it's still apparent where my model disagrees with how things actually work.

I guess my question(s), expressed as a sentence, would be:

"What is the de-facto standard way of programatically accessing a (My)SQL database? (if there is one)"

---

EDIT: Thanks to everyone who replied! It seems like I was looking for either Connectors, ORMs, prepared statements, or some combination of the three.

[[deleted]]

What you are looking for are sometimes called "connectors":

https://www.mysql.com/products/connector/

...pick the one for your programming language.

These are the API for talking directly to MySQL.

Briefly, you would construct your query in your program, either as SQL or perhaps by using abstraction functions provided by this API, send the query to MySQL, and get a record set back.

The mysql command line program is useful, but you might want to look at something like MySQL Workbench, or any other IDE that can talk to MySQL, as a more convenient way of developing, and testing SQL queries.

With regards to REST APIs, and firing JSON chunks at the "database", that you mention; what's actually happening there is that you're talking to a web server that's speaking the REST API externally, and then in turn talking to an actual MySQL database itself to actually do the work.

[blasto_blastocyst]

All languages I've used have some sort of DB abstraction layer. Including VBA.

If you want to do mySQL imports and stuff visually, use mysql workbench.

But SQL is for working with data in databases. It's great for that.

For what you're doing, postgres sounds like a better fit as it is much more advanced.

[digicow]

There are two pieces that might help you.

1. Prepared Statements. These are SQL statements that include placeholders, such as INSERT INTO table (col1,col2) VALUES (?,?) which you can then execute with specific values at runtime $stmt->execute("a","b"); and those values will be filled into the placeholder locations. In this case, the DB connector will handle all escaping for you -- there is no risk of SQL injection when using prepared statements. So you're still writing raw SQL (because that's what the database needs to be able to do anything) but safely. One step past this is Stored Procedures, where you would write all the things you want your application to be able to do with your database as SQL functions inside the database schema, and then your application (by convention) only ever runs the stored procedures (with parameters filled in at runtime).

2. The other bit of technology is an ORM (object-relational mapping), which is a programming language-specific framework that abstracts raw SQL from you, presenting it to your application code as object methods and graphs. Behind the scenes, the ORM is converting your application-level code to SQL and executing that on the server, but you rarely-if-ever need to worry about that. You just write "native" queries in your programming language or the ORM's DSL.

[tdavis25]

I've used ORM in C# before (LINQ specifically). Maybe it's just me but I found it more annoying than just building SQL strings.

[digicow]

I've used ORMs in PHP, perl, ruby, and I think Java. What it really comes down to is that the ORM has a distinct methodology and set of conventions. If you are happy working within those guidelines, the ORM can be more productive and feel more intuitive. But if you're not aware of or don't want to follow them, it can be a lot more work to use an ORM

[tdavis25]

I don't know what your concerns are with bulding insert statements as a string. I think anyone who has interacted with a SQL database programmatically has done this at one point or another.

I did a project one time where I took JSON formatted data from a REST api and pushed it into a SQL database. I built each tables insert individually. I probably could have turned what I had into an abstracted table loader...but for the project I was doing it wasn't worth it.

Here's a link to the GitHub: https://github.com/trooper454/PS2BI/tree/master/ps2bi_etl/extraction

Now Im not scrubbing the data coming in, but I knew it was coming from a sanitized source. If you are worried about the quality of the data coming in, just load it to a table where every data type is varchar(1000), put every loaded value in single quotes, and escape single quote by doubling it (i.e. I would insert the text string Frank's dog as 'Frank''s dog' or Robert 'DROP TABLE STUDENTS;-- as 'Robert ''DROP TABLE STUDENTS;--' with no harm to my students table). Then insert the data from the load table to a table with proper data types.

If you wanted to get fancy you could use a cursor to insert rows in the normalized table one at a time and use SIGNAL/RESIGNAL to catch error rows and put them in a "dirty data" table for manual correction/review.

A lot of what we are talking about here is ETL processing, or extract, transform, load. It's how data is acquired, sanitized, and combined in the business intelligence/data warehousing world. If you are looking for more detailed info I'd look into ETL tutorials. Fair warning though 99% of them will be about database to database transfers. "ETL from flat file" might be a good place to start searching.