Connecting to MySQL Using Python

Reading Time: 5 minutes

In this article, we will be discussing how to connect to MySQL using Python. Python is one of the most productive and widely used programming languages in use today. Its simple and elegant syntax makes it ideal for new programmers, while experienced programmers enjoy the extensive list of available modules and functionalities. 

MySQL is a robust open-source SQL-based, relational database management system that is used in many software programs and web servers. This article aims to show how to connect to use Python to connect to MySQL and perform some basic tasks. 

Continue reading “Connecting to MySQL Using Python”

How to Create and Use MySQL Views

Reading Time: 6 minutes

What is a MySQL View?

A MySQL view is simply an ordinary database object that can save SQL query writers a lot of time when used correctly. A view is a stored query that a user can reference just like a table. Many times users will find themselves using the same base query over and over to solve multiple problems. Views are a way of quickly saving that query and referencing it later.  

Continue reading “How to Create and Use MySQL Views”

How To Employ Basic Elasticsearch Commands

Reading Time: 5 minutes

What is Elasticsearch?

Elasticsearch is a distributed, full-text, open-source search engine. It provides multi-tenant capabilities in analyzing aggregate data types from sources like Logstash or Kibana. This application stores and indexes information, which can then be queried for specific data. It returns useful details about a particular program, log analysis, application performance data, or other information. 

Installation Order

To install the Elastic Stack, deploy these applications in the following order.

  1. Elasticsearch (install instructions)
  2. Kibana (install)
  3. Logstash (install)
  4. Beats (install instructions)
  5. APM Server (install instructions)
  6. Elasticsearch Hadoop (install instructions)

Installation 

In order to install Elasticsearch, see our kb article for more in depth instructions. Here are the basic installation steps.

  1. Download and unpack the Elasticsearch official distribution.
  2. Next, run bin/elasticsearch on Linux or macOS. Run bin\elasticsearch.bat on Windows.
  3. Then, curl -X GET http://localhost:9200/.
  4. Start more servers

What is Indexing?

Indexing is simply the process of adding data into Elasticsearch. Elasticsearch stores and retrieves this data in Apache Lucene indexes. We will not be discussing Lucene specifically in this article because we need to delve deeper into that application to truly understand the role Elasticsearch plays. This article is for primarily for new users to employ basic GET and PUT requests in Lucene.

Requests

Put

We send PUT requests when we know, or want to specify an ID of the data type. We can use POST if we want Elasticsearch to generate an ID for that item on its own. An Example of a simple POST command would look like this.

curl -XPOST 'localhost:9200/logs/test_app' -H 'Content-Type: application/json' -d'
{
"timestamp": "2020-08-20 09:10:11",
"message": "Test user is logged in",
"user_id": 2,
"admin": false
}
'

And the output looks like this:

{"_index":"logs","_type":"test_app","_id":"e8rHCnQBSXUbYazxinrq","_version":1,"result":"created","_shards":{"total":2,"successful":1,"failed":0},"_seq_no":1,"_primary_term":1}

Output

We can see in the output provided above that id was generated by Elasticsearch., We can also note the “version” and “created” fields as well. This implies that the test_app file was created using our POST command which did not exist before. Let’s review how we can index something using a PUT request.

curl -X PUT 'localhost:9200/app/users/4' -H 'Content-Type: application/json' -d '
{
  "id": 2,
  "username": "Dean",
  "last_login": "2020-08-20 09:10:11"
}
'

Using this command, we get this output.

{"_index":"app","_type":"users","_id":"4","_version":2,"result":"updated","_shards":{"total":2,"successful":1,"failed":0},"_seq_no":1,"_primary_term":1}

With this entry, we indexed the user Dean under /app/users/ directory. 

All the data that was entered is sent as a JSON object. If we ask how this works without any data structure, the answer is succinct. Elasticsearch usually works as a NoSQL database, thus structure is not needed. Next, we will check how we can perform queries using Elasticsearch.

What is Querying?

Querying is simply using a crafted query search to locate specific information within a given location. Using a query, we can separate questions in elasticsearch into two main categories. 

  • Leaf Queries – Leaf queries are looking for specific values in certain fields. We can run Leaf Query independently and some are termed match, term, and range queries.  
  • Compound Queries – Compound queries are combinations of leaf queries and compound queries. Compound queries combine multiple queries into a single one we use to get what we’re seeking more easily. 

Query Types

A complete classification of Elasticsearch queries looks like this:

  • Leaf Queries
    • Full text
    • Term queries
    • Geo queries
    • Span Queries
    • Joining Queries
    • Specialized queries
  • Compound Queries
    • Bool
    • Boosting
    • Dis max
    • Constant score
    • Function score

We do not have to know all these query types to run some basic commands with elasticsearch as many of these are advanced and require more than basic knowledge of the program. We list them here to get a better overall picture of elasticsearch’s capabilities. Let’s review some basic query commands that can be used with Elasticsearch. 

Note:
To query something with the Elasticsearch, we must index it first!

Examples

Since we indexed our app using the previous commands, let’s try to query it now. A simple command would look like this.

curl -XGET 'localhost:9200/app/users/4?pretty'

And this would be the output:

{
  "_index" : "app",
  "_type" : "users",
  "_id" : "4",
  "_version" : 1,
  "_seq_no" : 0,
  "_primary_term" : 1,
  "found" : true,
  "_source" : {
    "id" : 2,
    "username" : "Dean",
    "last_login" : "2020-08-20 09:10:11"
  }
}

Using this command, we queried all the users under “app”. It would return basic information such as username, id, time of last login and so on. Every field with the underscore in front of it is the meta field of our query. Under _source, we can see the original document that was indexed. The simplest way to query Elasticsearch is to use a “URI Search”. We provide a single search term or word and Elasticsearch will comb through every field of every document in our cluster. For example, we can use this query:

curl -XGET 'localhost:9200/_search?q=dean'

And Elasticsearch will find every field that contains the word “Dean” in it. On our end, the output query will look like this.

{"took":60,"timed_out":false,"_shards":{"total":1,"successful":1,"skipped":0,"failed":0},"hits":{"total":{"value":1,"relation":"eq"},"max_score":0.2876821,"hits":[{"_index":"app","_type":"users","_id":"4","_score":0.2876821,"_source":
{
  "id": 2,
  "username": "Dean",
  "last_login": "2020-08-20 09:10:11"
}
}]}}

Output

As we can see, the output produces both search terms, and the result of the search. Let’s do a quick breakdown of this information.

took = number of seconds that query took to complete

timed_out = pretty straightforward; false means that query didn’t time out, true means that it did time out

shards = number of documents that Elasticsearch went through and how many of them were searched successfully, how many were skipped, and how many failed

hits = how many fields actually met the query criteria, along with the meta information

In the example above, we searched for specific fields within all of our indexed information noted in Elasticsearch. We can narrow this query down further by providing the path to a limited subset of documents. This way, we can cut down on the query time as well. So, instead of: 

curl -XGET 'localhost:9200/_search?q=dean'

We will use:

curl -XGET 'localhost:9200/app/users/_search?q=dean'

The output would be the same, but in this case “took” (search timeframe) was only 3 seconds as opposed to the 60 seconds that we observed in the previous query. This is handy when we know the specific directory where some information is stored, but are not sure which document holds the information that we need. 

Now that we know the very basics of indexing and querying with Elasticsearch, let’s move on to the deletion of indexed documents.

What is Deleting?

In the same way we used previous commands, we can use the Delete function to remove data stored in our cluster. The difference is, we use DELETION via an HTTP request. 

curl -XDELETE 'localhost:9200/app/users/4?pretty'

Our output will look like this:

[root@host ~]# curl -XDELETE 'localhost:9200/app/users/4?pretty'
{
  "_index" : "app",
  "_type" : "users",
  "_id" : "4",
  "_version" : 1,
  "result" : "not_found",
  "_shards" : {
    "total" : 2,
    "successful" : 1,
    "failed" : 0
  },
  "_seq_no" : 2,
  "_primary_term" : 1
}

In order to delete a single index, following command can be used.

curl -XDELETE 'localhost:9200/logs?pretty'

If we want to delete an entire document that has been indexed, we can use this command.

curl -XDELETE 'localhost:9200/path/to/document'

The response that we get in both cases will look like this.

{
 "acknowledged" : true
}

Conclusion

In this article, we covered some of the basic Elasticsearch commands, but barely scratched the surface of all its capabilities. Multiple query variations can be employed to search through our information, but we would need multiple articles to cover all of them in depth. We hope that this article provides some of the basics of Elasticsearch as it is only written as an introduction to this powerful and versatile search engine.

Join Us!

Contact us today at 1.800.580.4985 to speak to a knowledgeable Solutions Provider who can get you the info you need on any one of our product lines, to assist you in making an informed decision right away.

Too busy to talk? Click HERE to open a quick chat with us to find out more. Would you like the information in an email you can review at your leisure? Email us today to get solid advice on which product in our line up would best suit your needs.

We look forward to hearing from you!

How to Install and Configure Elasticsearch

Reading Time: 8 minutes

What is Elasticsearch?

elasticsearch logo

Elasticsearch is a distributed, open-source, full-text search engine which provides multi-tenant capabilities for analyzing multiple data types. It stores and indexes data shipped from sources like Logstash or Kibana. Elasticsearch can then be queried for specific data to return useful information about a particular application, log analysis, application performance data, or other information. 

Continue reading “How to Install and Configure Elasticsearch”

How to Install Chocolatey on Windows

Reading Time: 4 minutes


Continue reading “How to Install Chocolatey on Windows”

Searching Through a Database with PhpMyAdmin

Reading Time: 3 minutes

Using PhpMyAdmin to search for records and information in your database can quickly help you get the information you need without having to run advanced search queries. This tutorial assumes you have already logged in to PhpMyAdmin, and shows how to search by keyword and for a range of records.

Continue reading “Searching Through a Database with PhpMyAdmin”

How to Install and Configure Nano on CentOS 8

Reading Time: 10 minutes

Introduction

Most Linux distributions ship with a command-line based text editor, usually Vi/Vim or Nano. While both are excellent choices, Vim has a steeper learning curve and can be confusing for beginners. Nano, on the other hand, will feel much more familiar to anyone who has used notepad or other simple text editors in a desktop or other graphical user interface. This is not to say that Nano is not as feature rich as Vim; it is simply more accessible.

Continue reading “How to Install and Configure Nano on CentOS 8”

Becoming Familiar with Databases in PhpMyAdmin

Reading Time: 2 minutes

This tutorial assumes you’ve already logged in to PhpMyAdmin. Now let’s familiarize ourselves with databases and PhpMyAdmin.

Continue reading “Becoming Familiar with Databases in PhpMyAdmin”

MySQL Performance: MySQL/MariaDB Indexes

Reading Time: 6 minutes

Data in a MySQL/MariaDB database is stored in tables. A simple way of thinking about indexes is to imagine an extensive spreadsheet. This type of system is not always conducive to quick searching; that’s where an index becomes essential. If there is no index, then the database engine has to start at row one and browse through all the rows looking for the corresponding values. If this is a small table, then it is no big deal, but in larger tables and applications where there can be tables with millions and even billions of rows, it becomes problematic. As you can imagine, searching through those rows one by one will be time-consuming, even on the latest hardware. The solution is to create an INDEX (or more than one) for your data.

Continue reading “MySQL Performance: MySQL/MariaDB Indexes”

Copying a Database Table with PhpMyAdmin

Reading Time: 2 minutes

Copying a table is quick and easy to do within PhpMyAdmin. Whether you are trying to make a backup copy before changes are made, archiving data, moving data to a new site or application, or even just working on restructuring your database, PhpMyAdmin can help you get the job done in just a few clicks.

This guide assumes that you have already logged in to PhpMyAdmin.We will go over how to copy a table and go over a few of the options PhpMyAdmin gives us.

Continue reading “Copying a Database Table with PhpMyAdmin”