Archive for the ‘Database’ Category

MySQL with C++ and Python

March 26th, 2015 No comments

Backup here:

1. C++

 * # gcc test_mysql.c -o test_mysql -lmysqlclient -pedantic -Wall -Wextra
#include <stdio.h>
#include <mysql/mysql.h>

int main()
    const char *host = "";
    const char *user = "root";
    const char *pass = "111111";
    const char *dbname = "mysql";
    MYSQL mysql;
    MYSQL_RES *mysql_res;
    MYSQL_FIELD *mysql_field;
    MYSQL_ROW mysql_row;
    unsigned int num_fields;
    unsigned long num_rows;
    int i, rc;

    /* connect */
    if (!mysql_real_connect(&mysql, host, user, pass, dbname, 0, NULL, 0)) {
        fprintf(stderr, "ERROR: %s\n", mysql_error(&mysql));
        return -1;
    /* query */
    rc = mysql_query(&mysql, "SELECT * FROM user");
    if (rc) {
        fprintf(stderr, "ERROR: %s\n", mysql_error(&mysql));
        return -1;
    /* display */
    mysql_res = mysql_store_result(&mysql);
    if (!mysql_res) {
        fprintf(stderr, "ERROR: %s\n", mysql_error(&mysql));
        return -1;
    num_fields = mysql_num_fields(mysql_res);
    printf("fields: %u\n", num_fields);
    num_rows = (unsigned long)mysql_num_rows(mysql_res);
    printf("rows: %lu\n", num_rows);
    if (num_rows != 0) {
        num_fields = (num_fields <= 9) ? num_fields : 9;
        for (i = 0; i < (int)num_fields; i++) {
            mysql_field = mysql_fetch_field_direct(mysql_res, i);
            printf("%s\t", mysql_field->name);
        while ((mysql_row = mysql_fetch_row(mysql_res)) != NULL) {
            for (i = 0; i < (int)num_fields; i++) {
                printf("%s\t",  mysql_row[i]);
    return 0;

2. Python

# sudo apt-get install python-mysql.connector
# sudo apt-get install python3-mysql.connector
from __future__ import print_function
import mysql.connector

cur = None
con = None

    con = mysql.connector.connect(host='', user='root', password='111111', database='mysql')
    cur = con.cursor()
    cur.execute("SELECT * FROM user")
    num_fields = len(cur.column_names)
    print('fields: %d' % num_fields)
    if num_fields > 9:
        num_fields = 9
    rows = cur.fetchall()  # fetchall() before get rowcount
    print('rows: %d' % cur.rowcount)
    for i in range(num_fields):
        print('%s\t' % (cur.column_names[i]), end='')
    for row in rows:
        for i in range(num_fields):
            print('%s\t' % (row[i]), end='')
except mysql.connector.Error as e:
    if cur:
    if con:
Categories: Database Tags:

Feature Matrix of NoSQL Databases

January 21st, 2015 No comments

Feature matrix of NoSQL databases, listed in Appendix of Seven Databases in Seven Weeks:

  MongoDB CouchDB Riak Redis PostgreSQL Neo4j HBase
Genre Document Document Key-value Key-value Relational Graph Columnar
Version 2.0 1.1 1.0 2.4 9.1 1.7 0.90.3
Datatypes Typed Typed Blob Semi-typed Predefined and typed Untyped Predefined and typed
Data Relations None None Ad hoc (Links) None Predefined Ad hoc (Edges) None
Standard Object JSON JSON Text String Table Hash Columns
Written in Language C++ Erlang Erlang C/C++ C Java Java
Interface Protocol Custom over TCP HTTP HTTP, protobuf Simple text over TCP Custom over TCP HTTP Thrift, HTTP
HTTP/REST Simple Yes Yes No No Yes Yes
Ad Hoc Query Commands, mapreduce Temporary views Weak support, Lucene Commands SQL Graph walking, Cypher, search Weak
Mapreduce JavaScript JavaScript JavaScript, Erlang No No No (in the distributed sense) Hadoop
Scalable Datacenter Datacenter (via BigCouch) Datacenter Cluster (via master-slave) Cluster (via add-ons) Cluster (via HA) Datacenter
Durability Write-ahead journaling, Safe mode Crash-only Durable write quorum Append-only log ACID ACID Write-ahead logging
  MongoDB CouchDB Riak Redis PostgreSQL Neo4j HBase
Secondary Indexes Yes Yes Yes No Yes Yes (via Lucene) No
Versioning No Yes Yes No No No Yes
Bulk Load mongoimport Bulk Doc API No No COPY command No No
Very Large Files GridFS Attachments Lewak (deprecated) No BLOBs No No
Requires Compaction No File rewrite No Snapshot No No No
Replication Master-slave (via replica sets) Master-master Peer-based, master-master Master-slave Master-slave Master-slave (in Enterprise Edition) Master-slave
Sharding Yes Yes (with filters in BigCouch) Yes Add-ons (e.g., client) Add-ons (e.g., PL/Proxy) No Yes via HDFS
Concurrency Write lock Lock-free MVCC Vector-clocks None Table/row writer lock Write lock Consistent per row
Transactions No No No Multi operation queues ACID ACID Yes (when enabled)
Triggers No Update validation or Changes API Pre/post-commits No Yes Transaction event handlers No
Security Users Users None Passwords Users/groups None Kerberos via Hadoop security
Multitenancy Yes Yes No No Yes No No
Main Differentiator Easily query Big Data Durable and embeddable clusters Highly available Very, very fast Best of OSS RDBMS model Flexible graph Very large-scale, Hadoop infrasturcture
Weaknesses Embed-ability Query-ability Query-ability Complex data Distributed availability BLOBs or terabyte scale Flexible growth, query-ability
Categories: Database Tags: , , , ,