MySql MariaDB Percona

http://dev.mysql.com/doc/connector-python/en/index.html

http://geert.vanderkelen.org/installing-coy-using-pip/

pip install mysql-connector-python <- fails, but the following work:

pip install --allow-external mysql-connector-python mysql-connector-python

import sys

import platform

print "* Python version: {} ".format(sys.version)

print "* Platform: {} ".format(platform.platform())

#from pprint import pprint as pp

#pp(sys.path)

try:

import mysql.connector

from mysql.connector import version

except ImportError as e:

sys.stderr.write("Error: failed to import mysql.connecgtor module ({})".format(e))

exit(1)

print "* mysql.connector version: {} url:{}".format(".".join(str(x) for x in version.VERSION[:3]), "http://dev.mysql.com/downloads/connector/python/")

try:

_host='localhost'

_user='root'

_pwd='root'

_db='information_schema'

_port=8889

con = mysql.connector.connect(port=_port, host=_host, user=_user, password=_pwd, database=_db);

cur = con.cursor()

cur.execute("SELECT VERSION()")

ver = cur.fetchone()

print "Database version : %s " % ver

except mysql.connector.Error, e:

print "=====Error:======== "

print e

#print "Error %d: %s" % (e.args[0],e.args[1])

sys.exit(1)

#finally:

# if con:

# con.close()

import mysql.connector

con = mysql.connector.connect(host='localhost', user='root', password='root', port=8889);

>>> dir (mysql.connector)

['BINARY', 'Binary', 'CNX_POOL_ARGS', 'CONNECTION_POOL_LOCK', 'CharacterSet', 'ClientFlag', 'Connect', 'DATETIME', 'DataError', 'DatabaseError', 'Date', 'DateFromTicks', 'Error', 'FieldFlag', 'FieldType', 'IntegrityError', 'InterfaceError', 'InternalError', 'MySQLConnection', 'MySQLConnectionPool', 'NUMBER', 'NotSupportedError', 'OperationalError', 'PoolError', 'ProgrammingError', 'ROWID', 'RefreshOption', 'STRING', 'Time', 'TimeFromTicks', 'Timestamp', 'TimestampFromTicks', 'Warning', '_CONNECTION_POOLS', '__all__', '__builtins__', '__doc__', '__file__', '__loader__', '__name__', '__package__', '__path__', '__version__', '__version_info__', 'apilevel', 'connect', 'connection', 'constants', 'conversion', 'cursor', 'custom_error_exception', 'dbapi', 'errorcode', 'errors', 'generate_pool_name', 'locales', 'network', 'paramstyle', 'pooling', 'protocol', 'threadsafety', 'utils', 'version']

>>> dir(con)

['__class__', '__delattr__', '__dict__', '__doc__', '__format__', '__getattribute__', '__hash__', '__init__', '__module__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__weakref__', '_autocommit', '_buffered', '_charset_id', '_client_flags', '_client_host', '_client_port', '_connection_timeout', '_converter_class', '_database', '_do_auth', '_do_handshake', '_execute_query', '_force_ipv6', '_get_connection', '_get_getwarnings', '_get_raise_on_warnings', '_get_self', '_get_unread_result', '_get_warnings', '_handle_binary_ok', '_handle_binary_result', '_handle_eof', '_handle_load_data_infile', '_handle_ok', '_handle_result', '_handle_server_status', '_handshake', '_have_next_result', '_host', '_in_transaction', '_info_query', '_open_connection', '_password', '_port', '_post_connection', '_prepared_statements', '_protocol', '_raise_on_warnings', '_raw', '_send_cmd', '_send_data', '_server_version', '_set_getwarnings', '_set_raise_on_warnings', '_set_unread_result', '_socket', '_sql_mode', '_ssl', '_time_zone', '_unix_socket', '_unread_result', '_use_unicode', '_user', 'autocommit', 'charset', 'close', 'cmd_change_user', 'cmd_debug', 'cmd_init_db', 'cmd_ping', 'cmd_process_info', 'cmd_process_kill', 'cmd_query', 'cmd_query_iter', 'cmd_quit', 'cmd_refresh', 'cmd_shutdown', 'cmd_statistics', 'cmd_stmt_close', 'cmd_stmt_execute', 'cmd_stmt_prepare', 'cmd_stmt_reset', 'cmd_stmt_send_long_data', 'collation', 'commit', 'config', 'connect', 'connection_id', 'converter', 'cursor', 'database', 'disconnect', 'get_autocommit', 'get_database', 'get_row', 'get_rows', 'get_server_info', 'get_server_version', 'get_sql_mode', 'get_time_zone', 'get_warnings', 'in_transaction', 'is_connected', 'isset_client_flag', 'ping', 'python_charset', 'raise_on_warnings', 'reconnect', 'rollback', 'server_host', 'server_port', 'set_autocommit', 'set_charset_collation', 'set_client_flags', 'set_converter_class', 'set_database', 'set_login', 'set_sql_mode', 'set_time_zone', 'set_unicode', 'sql_mode', 'start_transaction', 'time_zone', 'unix_socket', 'unread_result', 'user']

>>> dir(_cur)

['__class__', '__delattr__', '__dict__', '__doc__', '__format__', '__getattribute__', '__hash__', '__init__', '__iter__', '__module__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__unicode__', '__weakref__', '_binary', '_connection', '_description', '_execute_iter', '_executed', '_executed_list', '_fetch_row', '_fetch_warnings', '_handle_eof', '_handle_noresultset', '_handle_result', '_handle_resultset', '_have_unread_result', '_last_insert_id', '_lastrowid', '_nextrow', '_process_params', '_process_params_dict', '_reset_result', '_row_to_python', '_rowcount', '_set_connection', '_stored_results', '_warning_count', '_warnings', 'arraysize', 'callproc', 'close',

'column_names', 'description', 'execute', 'executemany', 'fetchall', 'fetchmany', 'fetchone', 'fetchwarnings', 'getlastrowid', 'lastrowid', 'next', 'nextset', 'reset', 'rowcount', 'setinputsizes', 'setoutputsize', 'statement', 'stored_results', 'with_rows']

Help on MySQLCursor in module mysql.connector.cursor object:

class MySQLCursor(CursorBase)

| Default cursor for interacting with MySQL

|

| This cursor will execute statements and handle the result. It will

| not automatically fetch all rows.

|

| MySQLCursor should be inherited whenever other functionallity is

| required. An example would to change the fetch* member functions

| to return dictionaries instead of lists of values.

|

| Implements the Python Database API Specification v2.0 (PEP-249)

|

| Method resolution order:

| MySQLCursor

| CursorBase

| __builtin__.object

|

| Methods defined here:

|

| __init__(self, connection=None)

|

| __iter__(self)

| Iteration over the result set which calls self.fetchone()

| and returns the next row.

|

| __str__(self)

| __unicode__(self)

|

| callproc(self, procname, args=())

| Calls a stored procedue with the given arguments

|

| The arguments will be set during this session, meaning

| they will be called like _<procname>__arg<nr> where

| <nr> is an enumeration (+1) of the arguments.

|

| Coding Example:

| 1) Definining the Stored Routine in MySQL:

| CREATE PROCEDURE multiply(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)

| BEGIN

| SET pProd := pFac1 * pFac2;

| END

|

| 2) Executing in Python:

| args = (5,5,0) # 0 is to hold pprod

| cursor.callproc('multiply', args)

| print(cursor.fetchone())

|

| Does not return a value, but a result set will be

| available when the CALL-statement execute successfully.

| Raises exceptions when something is wrong.

|

| close(self)

| Close the cursor

|

| Returns True when successful, otherwise False.

execute(self, operation, params=None, multi=False)

| Executes the given operation

|

| Executes the given operation substituting any markers with

| the given parameters.

|

| For example, getting all rows where id is 5:

| cursor.execute("SELECT * FROM t1 WHERE id = %s", (5,))

|

| The multi argument should be set to True when executing multiple

| statements in one operation. If not set and multiple results are

| found, an InterfaceError will be raised.

|

| If warnings where generated, and connection.get_warnings is True, then

| self._warnings will be a list containing these warnings.

|

| Returns an iterator when multi is True, otherwise None.

|

| executemany(self, operation, seq_params)

| Execute the given operation multiple times

|

| The executemany() method will execute the operation iterating

| over the list of parameters in seq_params.

|

| Example: Inserting 3 new employees and their phone number

|

| data = [

| ('Jane','555-001'),

| ('Joe', '555-001'),

| ('John', '555-003')

| ]

| stmt = "INSERT INTO employees (name, phone) VALUES ('%s','%s')"

| cursor.executemany(stmt, data)

|

| INSERT statements are optimized by batching the data, that is

| using the MySQL multiple rows syntax.

|

| Results are discarded. If they are needed, consider looping over

| data using the execute() method.

|

| fetchall(self)

|

| fetchmany(self, size=None)

|

| fetchone(self)

| Returns next row of a query result set

|

| Returns a tuple or None.

|

| fetchwarnings(self)

| Returns warnings

|

| getlastrowid(self)

| Returns the value generated for an AUTO_INCREMENT column

|

| This method is kept for backward compatibility. Please use the

| property lastrowid instead.

| Returns a long value or None.

|

| next(self)

| Used for iterating over the result set. Calles self.fetchone()

| to get the next row.

|

| stored_results(self)

| Returns an iterator for stored results

|

| This method returns an iterator over results which are stored when

| callproc() is called. The iterator will provide MySQLCursorBuffered

| instances.

|

| Returns a iterator.

|

| ----------------------------------------------------------------------

| Data descriptors defined here:

|

| column_names

| Returns column names

|

| This property returns the columns names as a tuple.

|

| Returns a tuple.

|

| statement

| Returns the executed statement

| This property returns the executed statement. When multiple

| statements were executed, the current statement in the iterator

| will be returned.

|

| with_rows

| Returns whether the cursor could have rows returned

|

| This property returns True when column descriptions are available

| and possibly also rows, which will need to be fetched.

|

| Returns True or False.

|

| ----------------------------------------------------------------------

| Methods inherited from CursorBase:

|

| nextset(self)

| Not Implemented.

|

| reset(self)

| Reset the cursor to default

|

| setinputsizes(self, sizes)

| Not Implemeted.

|

| setoutputsize(self, size, column=None)

| Not Implemeted.

|

| __dict__

| dictionary for instance variables (if defined)

|

| __weakref__

| list of weak references to the object (if defined)

|

| description

| Returns description of columns in a result

|

| This property returns a list of tuples describing the columns in

| in a result set. A tuple is described as follows::

|

| (column_name,

| type,

| None,

| None,

| None,

| None,

| null_ok,

| column_flags) # Addition to PEP-249 specs

|

| Returns a list of tuples.

|

| lastrowid

| Returns the value generated for an AUTO_INCREMENT column

|

| Returns the value generated for an AUTO_INCREMENT column by

| the previous INSERT or UPDATE statement or None when there is

| no such value available.

|

| Returns a long value or None.

|

| rowcount

| Returns the number of rows produced or affected

|

| This property returns the number of rows produced by queries

| such as a SELECT, or affected rows when executing DML statements

| like INSERT or UPDATE.

|

| Note that for non-buffered cursors it is impossible to know the

| number of rows produced before having fetched them all. For those,

| the number of rows will be -1 right after execution, and

| incremented when fetching rows.

|

| Returns an integer.

SQLALCHEMY

http://docs.sqlalchemy.org/en/rel_0_9/dialects/mysql.html#module-sqlalchemy.dialects.mysql.mysqlconnecto

http://stackoverflow.com/questions/14343740/flask-sqlalchemy-or-sqlalchemy

http://geert.vanderkelen.org/connectorpython-sqlalchemy/

mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysqlconnector://root:root@localhost:8889/MACCOR'

http://blog.fizyk.net.pl/blog/sqlalchemy-speed-tests-on-mysql-connector-python-mysqldb-and-oursql-drivers.html

NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:mysqlconnector

https://stackoverflow.com/questions/22521103/using-mysql-connector-python-driver-in-sqlalchemy

using mysqldb driver gives another error

ImportError: dlopen(/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/_mysql.so, 2): Library not loaded: libmysqlclient.18.dylib

FIX which works:

sudo ln -s /usr/local/mysql/lib/libmysqlclient.18.dylib /usr/lib/libmysqlclient.18.dylib

otool -L /usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/_mysql.so

libmysqlclient.18.dylib (compatibility version 18.0.0, current version 18.0.0)

/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1197.1.1)

OK, may be the issue in line above is what libmysqlclient has not path

/usr/local/mysql-5.6.16-osx10.7-x86_64/lib/libmysqlclient.18.dylib

sudo install_name_tool -change libmysqlclient.18.dylib /Applications/MAMP/Library/lib/libmysqlclient.16.dylib

/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/_mysql.so

otool -L /usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/_mysql.so/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/_mysql.so: /Applications/MAMP/Library/lib/libmysqlclient.16.dylib (compatibility version 18.0.0, current version 18.0.0) /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1197.1.1)

/Library/Python/2.7/site-packages/_mysql.so

Code example

http://alexharvey.eu/database/mysql/on-working-with-mysql-and-python/

http://zetcode.com/db/mysqlpython/

XAMPP

/Applications/XAMPP/xamppfiles/manager-osx.app START/STOP

$ pwd

/Applications/MAMP/Library/bin

mysql -u root -p // password: root

show databases;

+------------------------+

| Database |

+------------------------+

| information_schema |

| mysql |

| performance_schema |

+------------------------+

$ ls mysql*

mysql mysql_upgrade mysqld_multi

mysql_client_test mysql_waitpid mysqld_safe

mysql_config mysql_zap mysqldump

mysql_convert_table_format mysqlaccess mysqldumpslow

mysql_find_rows mysqlaccess.conf mysqlhotcopy

mysql_fix_extensions mysqladmin mysqlimport

mysql_plugin mysqlbinlog mysqlshow

mysql_secure_installation mysqlbug mysqlslap

mysql_setpermission mysqlcheck mysqltest

mysql_tzinfo_to_sql mysqld

Michaels-MacBook-Pro-4:bin mlubinsky$ ./mysql --help

./mysql Ver 14.14 Distrib 5.5.33, for osx10.6 (i386) using EditLine wrapper

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Usage: ./mysql [OPTIONS] [database]

-?, --help Display this help and exit.

-I, --help Synonym for -?

--auto-rehash Enable automatic rehashing. One doesn't need to use

'rehash' to get table and field completion, but startup

and reconnecting may take a longer time. Disable with

--disable-auto-rehash.

(Defaults to on; use --skip-auto-rehash to disable.)

-A, --no-auto-rehash

No automatic rehashing. One has to use 'rehash' to get

table and field completion. This gives a quicker start of

mysql and disables rehashing on reconnect.

--auto-vertical-output

Automatically switch to vertical output mode if the

result is wider than the terminal width.

-B, --batch Don't use history file. Disable interactive behavior.

(Enables --silent.)

--character-sets-dir=name

Directory for character set files.

--column-type-info Display column type information.

-c, --comments Preserve comments. Send comments to the server. The

default is --skip-comments (discard comments), enable

with --comments.

-C, --compress Use compression in server/client protocol.

-#, --debug[=#] This is a non-debug version. Catch this and exit.

--debug-check Check memory and open file usage at exit.

-T, --debug-info Print some debug info at exit.

-D, --database=name Database to use.

--default-character-set=name

Set the default character set.

--delimiter=name Delimiter to be used.

--enable-cleartext-plugin

Enable/disable the clear text authentication plugin.

-e, --execute=name Execute command and quit. (Disables --force and history

file.)

-E, --vertical Print the output of a query (rows) vertically.

-f, --force Continue even if we get an SQL error.

-G, --named-commands

Enable named commands. Named commands mean this program's

internal commands; see mysql> help . When enabled, the

named commands can be used from any line of the query,

otherwise only from the first line, before an enter.

Disable with --disable-named-commands. This option is

disabled by default.

-i, --ignore-spaces Ignore space after function names.

--init-command=name SQL Command to execute when connecting to MySQL server.

Will automatically be re-executed when reconnecting.

--local-infile Enable/disable LOAD DATA LOCAL INFILE.

-b, --no-beep Turn off beep on error.

-h, --host=name Connect to host.

-H, --html Produce HTML output.

-X, --xml Produce XML output.

--line-numbers Write line numbers for errors.

(Defaults to on; use --skip-line-numbers to disable.)

-L, --skip-line-numbers

Don't write line number for errors.

-n, --unbuffered Flush buffer after each query.

--column-names Write column names in results.

(Defaults to on; use --skip-column-names to disable.)

-N, --skip-column-names

Don't write column names in results.

--sigint-ignore Ignore SIGINT (CTRL-C).

-o, --one-database Ignore statements except those that occur while the

default database is the one named at the command line.

--pager[=name] Pager to use to display results. If you don't supply an

option, the default pager is taken from your ENV variable

PAGER. Valid pagers are less, more, cat [> filename],

etc. See interactive help (\h) also. This option does not

work in batch mode. Disable with --disable-pager. This

option is disabled by default.

-p, --password[=name]

Password to use when connecting to server. If password is

not given it's asked from the tty.

-P, --port=# Port number to use for connection or 0 for default to, in

order of preference, my.cnf, $MYSQL_TCP_PORT,

/etc/services, built-in default (3306).

--prompt=name Set the mysql prompt to this value.

--protocol=name The protocol to use for connection (tcp, socket, pipe,

memory).

-q, --quick Don't cache result, print it row by row. This may slow

down the server if the output is suspended. Doesn't use

history file.

-r, --raw Write fields without conversion. Used with --batch.

--reconnect Reconnect if the connection is lost. Disable with

--disable-reconnect. This option is enabled by default.

(Defaults to on; use --skip-reconnect to disable.)

-s, --silent Be more silent. Print results with a tab as separator,

each row on new line.

-S, --socket=name The socket file to use for connection.

-t, --table Output in table format.

--tee=name Append everything into outfile. See interactive help (\h)

also. Does not work in batch mode. Disable with

--disable-tee. This option is disabled by default.

-u, --user=name User for login if not current user.

-U, --safe-updates Only allow UPDATE and DELETE that uses keys.

-U, --i-am-a-dummy Synonym for option --safe-updates, -U.

-v, --verbose Write more. (-v -v -v gives the table output format).

-V, --version Output version information and exit.

-w, --wait Wait and retry if connection is down.

--connect-timeout=# Number of seconds before connection timeout.

--max-allowed-packet=#

The maximum packet length to send to or receive from

server.

--net-buffer-length=#

The buffer size for TCP/IP and socket communication.

--select-limit=# Automatic limit for SELECT when using --safe-updates.

--max-join-size=# Automatic limit for rows in a join when using

--safe-updates.

--secure-auth Refuse client connecting to server if it uses old

(pre-4.1.1) protocol.

--server-arg=name Send embedded server this as a parameter.

--show-warnings Show warnings after every statement.

--plugin-dir=name Directory for client-side plugins.

--default-auth=name Default authentication client-side plugin to use.

Default options are read from the following files in the given order:

/etc/my.cnf /etc/mysql/my.cnf /Applications/MAMP/conf/my.cnf ~/.my.cnf

<input type=number step=0.01 />

create database MACCOR;

use maccor;

use maccor;

CREATE TABLE MACCOR_STATE (station VARCHAR(5), channel SMALLINT, state VARCHAR(15), station_type VARCHAR(10), v SMALLINT, a SMALLINT, voltage FLOAT, capacity FLOAT );

ALTER TABLE MACCOR_STATE ADD PRIMARY KEY (station, channel);

drop table M001;

create table M001 (

CHANNEL int PRIMARY KEY,

TYPE varchar (10),

STATE varchar(10),

VOLTAGE float,

CAPACITY FLOAT

);

insert into M001 (CHANNEL, TYPE, STATE, VOLTAGE, CAPACITY) values (1, 'CELL_IOS_1', 'ACTIVE', 1.0, 10.0);

crosstab in MySQL

http://stackoverflow.com/questions/10569142/crosstab-in-mysql/11262856#11262856

select TYPE, max(if(STATE = 'ACTIVE', cnt, 0)) as ACTIVE, max(if(STATE = 'AVAILABLE', cnt, 0)) as AVAILABLE

from (select TYPE, STATE, COUNT(*) as cnt FROM M001 GROUP BY TYPE, STATE) as GGG GROUP BY TYPE

select state,

max(if(type = 'Cell_iOS', cnt, 0)) as CELL_iOS,

max(if(type = 'Pack_Mac', cnt, 0)) as Pac_Mac,

max(if(type = 'Cell_Mac', cnt, 0)) as Cell_Mac

from (select type, state, COUNT(*) as cnt FROM MACCOR_STATE

GROUP BY type, state

#union .. attempt to fill unexisting states with 0

#select ' ', state, 0 from dual ( select distinct type)

) as GGG

GROUP BY state

SELECT * FROM M001;

>cat routers.py

from flask import Flask, request, jsonify

from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)

db = SQLAlchemy(app)

#this works

app.config['SQLALCHEMY_DATABASE_URI'] = #'mysql://root:root@localhost/MACCOR'

'mysql+mysqlconnector://root:root@localhost/MACCOR'

class Maccor(db.Model):

__tablename__ = "M001"

CHANNEL = db.Column(db.Integer, primary_key = True)

TYPE = db.Column(db.String(10))

STATE = db.Column(db.String(10))

VOLTAGE = db.Column(db.Float(10))

CAPACITY = db.Column(db.Float(10))

@app.route('/M001/', methods=['GET'])

def M001():

if request.method =='GET':

results = Maccor.query.limit(10).offset(0).all()

json_results = []

for result in results:

print "In LOOP"

d = {

'CHANNEL' : result.CHANNEL,

'TYPE' : result.TYPE,

'STATE' : result.STATE,

'VOLTAGE' : result.VOLTAGE,

'CAPACITY': result.CAPACITY

}

json_results.append(d)

return jsonify(items=json_results)

@app.route('/')

@app.route('/index')

def index():

return "Hello, World!"

if __name__ == '__main__':

app.run(debug=True)