Export from to CSV, JSON or XML

Posted by Kyle Hankinson August 9, 2023


export to CSV, JSON or XML

You can use to export a table or view directly, or to export the results of a result set. Results can be exported to CSV, JSON or XML.

To export from a table or view, find the target in the database tree and right click on it. Choose 'Export as', then pick from either CSV, JSON or XML from one of the submenus.

Similar, when exporting from a results set either right click (or single click the arrow at the top left of resutls) and choose 'Export result set as' and pick from one of the options.

Video example:


Tags: MySQL Feature PostgreSQL Microsoft SQL Server SQLite Snowflake

MySQL table size

Posted by Kyle Hankinson January 12, 2023


Understanding the Size of MySQL Tables

When working with MySQL databases, it's important to understand the size of the tables in order to optimize performance and storage. In this article, we will discuss how to retrieve the size of all tables in a MySQL database using a simple SQL query.

To retrieve the size of all tables in a MySQL database, we can use the information_schema.TABLES table, which contains information about all tables in the database. The information_schema.TABLES table has two columns, data_length and index_length, that represent the size of the data and index for each table, respectively.

Here is an example of a query that shows the size of all tables in a MySQL database:

SELECT table_name AS 'Table Name', round(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)' FROM information_schema.TABLES GROUP BY table_name
;

Tags: MySQL

Listing tables in a MySQL database

Posted by Kyle Hankinson June 24th, 2020


Listing tables

Have you ever needed to see the tables in a MySQL database? MySQL provides a simple and easy query for listing them.

SHOW TABLES

The SHOW TABLES command is the quickest and easiest way to get the list of tables available. You can run this from the MySQL CLI or from any MySQL GUI.

Example

In the example northwind database, running SHOW TABLES will give you results similar to the following:

+--------------------------------+
| Tables_in_northwind            |
+--------------------------------+
| Alphabetical list of products  |
| Categories                     |
| Category Sales for 1997        |
| Current Product List           |
| Customer and Suppliers by City |
| CustomerCustomerDemo           |
| CustomerDemographics           |
| ...                            |
| Products by Category           |
| Quarterly Orders               |
| Region                         |
| Sales Totals by Amount         |
| Sales by Category              |
| Shippers                       |
| Summary of Sales by Quarter    |
| Summary of Sales by Year       |
| Suppliers                      |
| Territories                    |
+--------------------------------+

Tables Only?

By default when running SHOW TABLES you will receive a list of both Tables and Views. If you want to narrow down to just showing objects which are a table, you will want the following:

SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE'

Similar, if you want just views then you will want:

SHOW FULL TABLES WHERE Table_Type = 'VIEW'

Additional options

There are a few other options available with the SHOW TABLES command. You can find additional details at the official MySQL Documentation.


Tags: MySQL

MySQL with Swift and Objective-C

Posted by Kyle Hankinson June 24th, 2020


Prefix

In general its a better idea to use a webservice and to keep direct database connections out of your mobile or desktop apps. If you are still looking to connect to a MySQL (or MariaDB) database, MariaDBKit is an iOS and macOS compatible framework for making a connection.

Getting started

MariaDBKit supports Carthage. The easiest way to get started is by adding the following to a Cartfile in your projects root directory. You will want to add the following to your Cartfile.

github "hankinsoft/MariaDB"

Then run 'carthage update' from terminal:

Once completed, you now have a framework available for use in both iOS and macOS apps, which you can link to any Objective-C or Swift project. Switch to the 'Build Phases' tab of your target and either drag the framework in, or add it by using the 'Plus' button, followed by navigating to the Carthage build output directory.

Code

Now comes the fun part. The following sample code allows a connection to the MySQL database, along with running a query and enumerating the results.

import MariaDBKit

let client = MariaDBClient()

do {
    try client.connect("sqlprostudio-mysql.csyg8tkobue6.us-west-2.rds.amazonaws.com", username: "sqlprostudio-ro", password: "password123", database: "northwind")

    var error : NSError?
    let results = client.executeQuery("SHOW TABLES;", error: &error)

    if let theError = error {
        print(theError)
        return
    }

    while(results.next(&error)) {
        print(results.object(forColumnIndex: 0))
    }
}
catch {
    print(error)
}
@import MariaDBKit;

MariaDBClient * client = [[MariaDBClient alloc] init];
NSError * error = nil;

BOOL success = [client connect: @"sqlprostudio-mysql.csyg8tkobue6.us-west-2.rds.amazonaws.com"
                      username: @"sqlprostudio-ro"
                      password: @"password123"
                      database: @"northwind"
                         error: &error];

if(!success || error)
{
    NSLog(@"Failed to connect to MySQL Server.");
    return;
} // End of we had an error

MariaDBResultSet * results = [client executeQuery: @"SHOW TABLES;"
                                            error: &error];

if(error)
{
    NSLog(@"Error: %@", error.localizedDescription);
    return;
}

while([results next: &error])
{
    NSLog(@"%@", [results objectForColumnIndex: 0]);
} // End of results loop

The above code runs the query SHOW TABLES, which gives us a single column result containing the table names. While in this cases we can assume that we are receive a single column, you can also get a list of column names from a result. You might want to do this if you were running a wildcard query against a table (such as SELECT * FROM myTable). The following code example will run a query against the Employees table and give us a list of column names.

import MariaDBKit

let client = MariaDBClient()

do {
    try client.connect("sqlprostudio-mysql.csyg8tkobue6.us-west-2.rds.amazonaws.com", username: "sqlprostudio-ro", password: "password123", database: "northwind")

    var error : NSError?
    let results = client.executeQuery("SELECT * FROM Employees;", error: &error)

    if let theError = error {
        print(theError)
        return
    }

    // Output the number of columns along with the list of columns themselves.
    print(String(format: "Table has %ld columns.\r\n%@.", results.columnNames.count,
          results.columnNames.joined(separator: ",")))
}
catch {
    print(error)
}
@import MariaDBKit;

MariaDBClient * client = [[MariaDBClient alloc] init];
NSError * error = nil;

BOOL success = [client connect: @"sqlprostudio-mysql.csyg8tkobue6.us-west-2.rds.amazonaws.com"
                      username: @"sqlprostudio-ro"
                      password: @"password123"
                      database: @"northwind"
                         error: &error];

if(!success || error)
{
    NSLog(@"Failed to connect to MySQL Server.");
    return;
} // End of we had an error

MariaDBResultSet * results = [client executeQuery: @"SHOW TABLES;"
                                            error: &error];

if(error)
{
    NSLog(@"Error: %@", error.localizedDescription);
    return;
}

NSLog(@"Table has %ld columns.\r\n%@.", results.columnNames.count, [results.columnNames componentsJoinedByString: @","]);

Up Next

For the next blog post, I plan on creating a small SwiftUI wrapper around this. It will allow a user to enter a host, username and password to connect. Then once connected, a text entry where a user can enter a query and finally a results grid which will display the results of the query.


Tags: Swift Objective-C MySQL