Python如何读取MySQL数据库表数据

# -*- coding: utf-8 -*-

importpandas as pd

importpymysql

## 加上字符集参数，防止中文乱码

dbconn=pymysql.connect(

  host="**********",

  database="kimbo",

  user="kimbo_test",

  password="******",

  port=3306,

  charset='utf8'

 )

#sql语句

sqlcmd="select col_name,col_type,col_desc from itf_datadic_dtl_d limit 10"

#利用pandas 模块导入mysql数据

a=pd.read_sql(sqlcmd,dbconn)

#取前5行数据

b=a.head()

print(b)

# 读取csv数据

# pd.read_csv()

# 读取excel数据

#pd.read_excel()

# 读取txt数据

#pd.read_table()

<?PHP

$servername='localhost'

$username="root";

$password="1234";

$dbname="mydb1";

//创建链接

$conn=newmysqli($servername,$username,$password,$dbname);

if($conn->connect_error)

{

die("链接失败".$connect_error);

}

//定义语句

$sql="select * from users where username='wangdenggao'";

//执行语句

$result=$conn->query($sql);

if($result->num_rows>0)//这个检验是必须的，而且我们要知道的是$result也就是执行sql语句的返回的值是一个数组

这个数组看做一个对象，我们可以用箭头得到他的一些的属性，而且fetch_assoc()是获取数据数组中的每一行

{

while($row=$result->fetch_assoc()) {

echo"
Username".$row['username']."
Password:".$row['password'];

}

classCPdo{

 protected$_dsn="mysql:host=localhost;dbname=test";

 protected$_name="root";

 protected$_pass="";

 protected$_condition=array();

 protected$pdo;

 protected$fetchAll;

 protected$query;

 protected$result;

 protected$num;

 protected$mode;

 protected$prepare;

 protected$row;

 protected$fetchAction;

 protected$beginTransaction;

 protected$rollback;

 protected$commit;

 protected$char;

 privatestatic$get_mode;

 privatestatic$get_fetch_action;

 /**

 *pdo construct

 */

 publicfunction__construct($pconnect= false) {

  $this->_condition =array(PDO::ATTR_PERSISTENT =>$pconnect);

  $this->pdo_connect();

 }

 /**

 *pdo connect

 */

 privatefunctionpdo_connect() {

  try{

   $this->pdo =newPDO($this->_dsn,$this->_name,$this->_pass,$this->_condition);

  }

  catch(Exception$e) {

   return$this->setExceptionError($e->getMessage(),$e->getline,$e->getFile);

  }

 }

 /**

 *self sql get value action

 */

 publicfunctiongetValueBySelfCreateSql($sql,$fetchAction="assoc",$mode= null) {

  $this->fetchAction =$this->fetchAction($fetchAction);

  $this->result =$this->setAttribute($sql,$this->fetchAction,$mode);

  $this->AllValue =$this->result->fetchAll();

  return$this->AllValue;

 }

 /**

 *select condition can query

 */

 privatefunctionsetAttribute($sql,$fetchAction,$mode) {

  $this->mode = self::getMode($mode);

  $this->fetchAction = self::fetchAction($fetchAction);

  $this->pdo->setAttribute(PDO::ATTR_CASE,$this->mode);

  $this->query =$this->base_query($sql);

  $this->query->setFetchMode($this->fetchAction);

  return$this->query;

 }

 /**

 *get mode action

 */

 privatestaticfunctiongetMode($get_style){

  switch($get_style) {

   casenull:

    self::$get_mode= PDO::CASE_NATURAL;

   break;

   casetrue:

    self::$get_mode= PDO::CASE_UPPER;

   break;

   casefalse;

   self::$get_mode= PDO::CASE_LOWER;

   break;

  }

  returnself::$get_mode;

 }

 /**

 *fetch value action

 */

 privatestaticfunctionfetchAction($fetchAction) {

  switch($fetchAction) {

   case"assoc":

    self::$get_fetch_action= PDO::FETCH_ASSOC;//asso array

   break;

   case"num":

    self::$get_fetch_action= PDO::FETCH_NUM;//num array

   break;

   case"object":

    self::$get_fetch_action= PDO::FETCH_OBJ;//object array

   break;

   case"both":

    self::$get_fetch_action= PDO::FETCH_BOTH;//assoc array and num array

   break;

   default:

    self::$get_fetch_action= PDO::FETCH_ASSOC;

   break;

  }

  returnself::$get_fetch_action;

 }

 /**

 *get total num action

 */

 publicfunctionrowCount($sql) {

  $this->result =$this->base_query($sql);

  $this->num =$this->result->rowCount();

  return$this->num;

 }

 /*

 *simple query and easy query action

 */

 publicfunctionquery($table,$column="*",$condition=array(),$group="",$order="",$having="",$startSet="",$endSet="",$fetchAction="assoc",$params=  null){

  $sql="select ".$column." from `".$table."` ";

  if($condition!= null) {

   foreach($conditionas$key=>$value) {

    $where.="$key = '$value' and ";

   }

   $sql.="where $where";

   $sql.="1 = 1 ";

  }

  if($group!="") {

   $sql.="group by ".$group." ";

  }

  if($order!="") {

   $sql.=" order by ".$order." ";

  }

  if($having!="") {

   $sql.="having '$having' ";

  }

  if($startSet!=""&&$endSet!=""&&is_numeric($endSet) &&is_numeric($startSet)) {

   $sql.="limit $startSet,$endSet";

  }

  $this->result =$this->getValueBySelfCreateSql($sql,$fetchAction,$params);

  return$this->result;

 }

 /**

 *execute delete update insert and so on action

 */

 publicfunctionexec($sql) {

  $this->result =$this->pdo->exec($sql);

  $substr=substr($sql, 0 ,6);

  if($this->result) {

   return$this->successful($substr);

  }else{

   return$this->fail($substr);

  }

 }

 /**

 *prepare action

 */

 publicfunctionprepare($sql) {

  $this->prepare =$this->pdo->prepare($sql);

  $this->setChars();

  $this->prepare->execute();

  while($this->rowz =$this->prepare->fetch()) {

   return$this->row;

  }

 }

 /**

 *USE transaction

 */

 publicfunctiontransaction($sql) {

  $this->begin();

  $this->result =$this->pdo->exec($sql);

  if($this->result) {

   $this->commit();

  }else{

   $this->rollback();

  }

 }

 /**

 *start transaction

 */

 privatefunctionbegin() {

  $this->beginTransaction =$this->pdo->beginTransaction();

  return$this->beginTransaction;

 }

 /**

 *commit transaction

 */

 privatefunctioncommit() {

  $this->commit =$this->pdo->commit();

  return$this->commit;

 }

 /**

 *rollback transaction

 */

 privatefunctionrollback() {

  $this->rollback =$this->pdo->rollback();

  return$this->rollback;

 }

 /**

 *base query

 */

 privatefunctionbase_query($sql) {

  $this->setChars();

  $this->query =$this->pdo->query($sql);

  return$this->query;

 }

 /**

 *set chars

 */

 privatefunctionsetChars() {

  $this->char =$this->pdo->query("SET NAMES 'UTF8'");

  return$this->char;

 }

 /**

 *process sucessful action

 */

 privatefunctionsuccessful($params){

  return"The ".$params." action is successful";

 }

 /**

 *process fail action

 */

 privatefunctionfail($params){

  return"The ".$params." action is fail";

 }

 /**

 *process exception action

 */

 privatefunctionsetExceptionError($getMessage,$getLine,$getFile) {

  echo"Error message is ".$getMessage."<br /> The Error in ".$getLine." line <br /> This file dir on ".$getFile;

  exit();

 }

}

location ^~/tomcat/{

  proxy_pass  http://127.0.0.1:8080/;

  proxy_redirect off;

  proxy_set_header X-Real-IP $remote_addr;

  proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;

}