python从数据库中获得数据到numpy

 

使用mysql.connector连接mysql数据库

  
  

   
   
   
   import
   
    mysql.connector

   
   from
   
    itertools 
   
   import
   
    chain

   
   import
   
    sys, os

   
   import
   
    numpy as np

user 
   
   =
   
    
   
   '
   
   root
   
   '
   
   
pwd  
   
   =
   
    
   
   '
   
   root
   
   '
   
   
host 
   
   =
   
    
   
   '
   
   127.0.0.1
   
   '
   
   
db   
   
   =
   
    
   
   '
   
   lending
   
   '
   
   


select_sql 
   
   =
   
    
   
   "
   
   SELECT id,age FROM mytable
   
   "
   
   
count_sql 
   
   =
   
    
   
   "
   
   SELECT count(*) FROM mytable
   
   "
   
   

cnx 
   
   =
   
    mysql.connector.connect(user
   
   =
   
   user, password
   
   =
   
   pwd, host
   
   =
   
   host, database
   
   =
   
   db)
cursor 
   
   =
   
    cnx.cursor()



   
   try
   
   :

    cursor.execute(count_sql)
    count 
   
   =
   
    0
    
   
   #
   
   显示总数
   
   

   
       
   
   for
   
    row1 
   
   in
   
    cursor:
        count 
   
   =
   
    row1[0];
        
   
   print
   
   (row1)

    test 
   
   =
   
    np.arange(count
   
   *
   
   2
   
   ).reshape(count,
   
   2
   
   )
    numrows 
   
   =
   
    cursor.execute(select_sql)
    
   
   print
   
   (
   
   "
   
   ================================
   
   "
   
   )
    index 
   
   =
   
    0;
    
   
   for
   
    row 
   
   in
   
    cursor:
        
   
   print
   
   (row)
        test[index,:] 
   
   =
   
    np.fromiter(row, dtype
   
   =
   
   (int,int), count
   
   =
   
   1
   
   )
        index 
   
   =
   
    index
   
   +
   
   1
   
   
    
   
   print
   
   (test)


   
   except
   
    mysql.connector.Error as err:
    
   
   print
   
   (
   
   "
   
   query table 'mytable' failed.
   
   "
   
   )
    
   
   print
   
   (
   
   "
   
   Error: {}
   
   "
   
   .format(err.msg))
    sys.exit()

cnx.commit()
cursor.close()
cnx.close()
  
  

Numpy常见数据类型