Example of Winpython and easyPhp DevServer running together

In [1]:

    

def mysql_config() :
  "include the profile definition and no database"
  return {
  'user': 'root',
  'password': '',
  'host': '127.0.0.1',
  'database': '',
  'raise_on_warnings': False
  }


def create_mysql_tables(table_dictionnary, connexion):
    "this will create the table defined in a dictionnary"
    from mysql.connector import errorcode
    cursor = connexion.cursor()
    for name, ddl in table_dictionnary.items():
        try:
            print("Creating table {}: ".format(name), end='')
            cursor.execute(ddl)
        except mysql.connector.Error as err:
            if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
                print("already exists.")
            else:
                print(err.msg)
        else:
            print("OK")

def execute_mysql_queries(query_list, connexion):
    "this will execute a list of queries"
    from mysql.connector import errorcode
    cursor = connexion.cursor()
    # Note 'multi=True' when calling cursor.execute()
    for result in cursor.execute(' ; '.join(query_list), multi=True):
       if result.with_rows:
           print("Statement '{}' has following rows:".format(
             result.statement))
           print(result.fetchall())
       else:
           print("Affected row(s) by query '{}' was {}".format(
             result.statement, result.rowcount))
    
    cursor.close()

import mysql.connector
class NumpyMySQLConverter(mysql.connector.conversion.MySQLConverter):
    """ A mysql.connector Converter that handles Numpy types 
        usage :
               conn = mysql.connector.connect(**config)
               conn.set_converter_class(NumpyMySQLConverter)"""
    #see http://stackoverflow.com/questions/17053435/mysql-connector-python-insert-python-variable-to-mysql-table
    def _float32_to_mysql(self, value):
        return float(value)

    def _float64_to_mysql(self, value):
        return float(value)

    def _int32_to_mysql(self, value):
        return int(value)

    def _int64_to_mysql(self, value):
        return int(value)


    

In [2]:

    

import mysql.connector
from mysql.connector import errorcode
#http://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html
config = mysql_config()

my_precious_database_name="frodon"

TABLES = {}
TABLES['product'] = (
    "CREATE TABLE `product` ("
    "  `item_no` varchar(11) NOT NULL  ,"
    "  `item_name`  varchar(77)  NOT NULL,"
    "  `item_net_weight` FLOAT NOT NULL,"
    "  PRIMARY KEY (`item_no`)"
    ")  ")

TABLES['department'] = (
    "CREATE TABLE `department` ("
    "  `dept_no` char(4) NOT NULL,"
    "  `dept_name` varchar(40) NOT NULL,"
    "  PRIMARY KEY (`dept_no`), UNIQUE KEY `dept_name` (`dept_name`)"
    ") ")

TABLES['machine'] = (
    "CREATE TABLE `machine` ("
    "  `machine_no` char(4) NOT NULL,"
    "  `machine_name` varchar(40) NOT NULL,"
     " `machinedept_no` char(4) NOT NULL,"
   "  PRIMARY KEY (`machine_no`), UNIQUE KEY `machine_name` (`machine_name`), "
    "  CONSTRAINT `machine_ibfk_1` FOREIGN KEY (`machinedept_no`) "
    "     REFERENCES `department` (`dept_no`) ON DELETE CASCADE"    ")  ")

cnx = mysql.connector.connect(**config  )
cur = cnx.cursor()
qry="CREATE DATABASE  IF NOT EXISTS      " + my_precious_database_name


cur.execute(qry)
cur.close()
cnx.close

config['database']=my_precious_database_name
cnx = mysql.connector.connect(**config )
cur = cnx.cursor()
qry = "show tables"
cur.execute(qry)
for res in cur.fetchall() :
       print(res)
cur.close()

create_mysql_tables(TABLES, cnx)
stmts = ["show tables",
         "delete from product",
         "insert into product values ('eau1' , 'bouteille d''1 litre d''eau', 1),('baguette2' , 'une baguette de 200gr', 0.2)",
         "SELECT  * from product"]
execute_mysql_queries(stmts, cnx) 
cnx.close()


    

    




('department',)
('machine',)
('product',)
Creating table product: already exists.
Creating table machine: already exists.
Creating table department: already exists.
Statement 'show tables' has following rows:
[('department',), ('machine',), ('product',)]
Affected row(s) by query 'delete from product' was 2
Affected row(s) by query 'insert into product values ('eau1' , 'bouteille d''1 litre d''eau', 1),('baguette2' , 'une baguette de 200gr', 0.2)' was 2
Statement 'SELECT  * from product' has following rows:
[('eau1', "bouteille d'1 litre d'eau", 1.0), ('baguette2', 'une baguette de 200gr', 0.2)]

In [3]:

    

#usage par mysql 
import mysql.connector
from mysql.connector import errorcode
#http://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html
config = mysql_config()
config['database']=my_precious_database_name
cnx = mysql.connector.connect(**config)

#using pandas
import pandas.io.sql as psql
psql.frame_query('show tables', con=cnx)


    

    
Out[3]:






  

    

      

      
Tables_in_frodon
    
  
  

    

      
0
      
 department
    
    

      
1
      
    machine
    
    

      
2
      
    product
    
  

In [ ]: