文档章节

mysql常用操作汇总

python测试开发人工智能安全
 python测试开发人工智能安全
发布于 2016/06/24 17:02
字数 1949
阅读 195
收藏 2

本文已经迁移至:

https://bitbucket.org/xurongzhong/testing/wiki/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C%E6%B1%87%E6%80%BB

 

 

 

高可用mysql 的python代码:

 

https://launchpad.net/mysql-replicant-python

 

Mysql API

 

mysql python api

Mysql 在python有很多连接库。目前用户最多的要数:https://pypi.python.org/pypi/MySQL-python。它还有一个精炼版本:https://github.com/farcepest/moist。但是近期更新比较慢,不支持python3。尤其oracle的官方的mysql-connector-python连接器逐渐成熟之后,参见:http://dev.mysql.com/doc/connector-python/en/。

初期mysql-connector-python处理中文等不够完美,但是逐渐完善,并增加了c语言实现版本,性能大幅度提高。对python3支持也比较好。但是经常通过pip install的方式无法安装,此时就需要克隆https://github.com/mysql/mysql-connector-python.git,用python setup.py install的方式安装。

import mysql.connector

try:
  conn_params = {
    "database": "cookbook",
    "host": "localhost",
    "user": "cbuser",
    "password": "cbpass",
    "charset": "utf8",
  }
  conn = mysql.connector.connect(**conn_params)
  print("Connected")
except:
  print("Cannot connect to server")
else:
  conn.close()
  print("Disconnected")

常见选项:

  conn_params = {
    "database": "cookbook",
    "host": "localhost",
    "port": 3307,
    "unix_socket": "/var/tmp/mysql.sock",    
    "user": "cbuser",
    "password": "cbpass",
  }

mysql java api

基于jdbc,使用MySQL Connector/J。

// Connect.java: connect to the MySQL server

import java.sql.*;

public class Connect
{
  public static void main (String[] args)
  {
    Connection conn = null;
    String url = "jdbc:mysql://localhost/cookbook";
    String userName = "cbuser";
    String password = "cbpass";

    try
    {
      Class.forName ("com.mysql.jdbc.Driver").newInstance ();
      conn = DriverManager.getConnection (url, userName, password);
      System.out.println ("Connected");
    }
    catch (Exception e)
    {
      System.err.println ("Cannot connect to server");
      System.exit (1);
    }
    if (conn != null)
    {
      try
      {
        conn.close ();
        System.out.println ("Disconnected");
      }
      catch (Exception e) { /* ignore close errors */ }
    }
  }
}

可以省略主机,默认为localhost,比如jdbc:mysql:///。
其他方式:String url = "jdbc:mysql://localhost/cookbook?user=cbuser&password=cbpass";
Connector/J 不支持Unix domain socket,指定端口:String url = "jdbc:mysql://127.0.0.1:3307/cookbook";

错误处理

错误发生时,mysql提供:

  • 错误号
  • 错误信息
  • SQLSTATE,5个字符的错误码,基于 ANSI和ODBC标准。

另外查询和ERROR日志也有辅助作用。

python

#!/usr/bin/python
# error.py: demonstrate MySQL error handling

import mysql.connector
import sys

#@ _FRAG_
conn_params = {
  "database": "cookbook",
  "host": "localhost",
  "user": "baduser",
  "password": "badpass"
}

try:
  conn = mysql.connector.connect(**conn_params)
  print("Connected")
except mysql.connector.Error as e:
  print("Cannot connect to server")
  print("Error code: %s" % e.errno)
  print("Error message: %s" % e.msg)
  print("Error SQLSTATE: %s" % e.sqlstate)
  for  item in sys.exc_info():
    print item
  
#@ _FRAG_
else:
  conn.close()
  print("Disconnected")

执行会发现e.errno、e.msg、e.sqlstate等比sys.exc_info()要更可读,不过注意不是python的所有异常都有这些字段。

Java中简单的异常处理如下

try
{
/* ... some database operation ... */
}
catch (Exception e)
{
e.printStackTrace ();
}

不过这样可读性不太好。

import java.sql.*;

public class Error
{
  public static void main (String[] args)
  {
    Connection conn = null;
    String url = "jdbc:mysql://localhost/cookbook";
    String userName = "baduser";
    String password = "badpass";

    try
    {
      Class.forName ("com.mysql.jdbc.Driver").newInstance ();
      conn = DriverManager.getConnection (url, userName, password);
      System.out.println ("Connected");
      tryQuery (conn);    // issue a query
    }
    catch (Exception e)
    {
      System.err.println ("Cannot connect to server");
      System.err.println (e);
      if (e instanceof SQLException)  // JDBC-specific exception?
      {
        // e must be cast from Exception to SQLException to
        // access the SQLException-specific methods
        printException ((SQLException) e);
      }
    }
    finally
    {
      if (conn != null)
      {
        try
        {
          conn.close ();
          System.out.println ("Disconnected");
        }
        catch (SQLException e)
        {
          printException (e);
        }
      }
    }
  }

  public static void tryQuery (Connection conn)
  {
    try
    {
      // issue a simple query
      Statement s = conn.createStatement ();
      s.execute ("USE cookbook");
      s.close ();

      // print any accumulated warnings
      SQLWarning w = conn.getWarnings ();
      while (w != null)
      {
        System.err.println ("SQLWarning: " + w.getMessage ());
        System.err.println ("SQLState: " + w.getSQLState ());
        System.err.println ("Vendor code: " + w.getErrorCode ());
        w = w.getNextWarning ();
      }
    }
    catch (SQLException e)
    {
      printException (e);
    }
  }

  public static void printException (SQLException e)
  {
    // print general message, plus any database-specific message
    System.err.println ("SQLException: " + e.getMessage ());
    System.err.println ("SQLState: " + e.getSQLState ());
    System.err.println ("Vendor code: " + e.getErrorCode ());
  }
}

封装库

为了方便,我们对数据库连接进行了简单的封装:

cookbook.py

import mysql.connector

conn_params = {
  "database": "cookbook",
  "host": "localhost",
  "user": "cbuser",
  "password": "cbpass",
}

# Establish a connection to the cookbook database, returning a connection
# object.  Raise an exception if the connection cannot be established.

def connect():
  return mysql.connector.connect(**conn_params)

使用:harness.py

import mysql.connector
import cookbook

try:
  conn = cookbook.connect()
  print("Connected")
except mysql.connector.Error as e:
  print("Cannot connect to server")
  print("Error code: %s" % e.errno)
  print("Error message: %s" % e.msg)
else:
  conn.close()
  print("Disconnected")
package com.kitebird.mcb;

import java.sql.*;
import java.util.*;  

public class Cookbook
{

  public static Connection connect () throws Exception
  {
    String url = "jdbc:mysql://localhost/cookbook";
    String user = "cbuser";
    String password = "cbpass";

    Class.forName ("com.mysql.jdbc.Driver").newInstance ();
    return (DriverManager.getConnection (url, user, password));
  }

  public static Connection propsConnect () throws Exception
  {
    String propsFile = "Cookbook.properties";
    Properties props = new Properties ();
    String host = "";
    String database = "";
    String user = "";
    String password = "";

    props.load (Cookbook.class.getResourceAsStream (propsFile));
    host = props.getProperty ("host", "localhost");
    database = props.getProperty ("database", "cookbook");
    user = props.getProperty ("user", "");
    password = props.getProperty ("password", "");

    String url = "jdbc:mysql://" + host + "/" + database;
    Class.forName ("com.mysql.jdbc.Driver").newInstance ();
    return (DriverManager.getConnection (url, user, password));
  }


  public static String getErrorMessage (Exception e)
  {
    StringBuffer s = new StringBuffer ();
    if (e instanceof SQLException)  // JDBC-specific exception?
    {
      // print general message, plus any database-specific message
      s.append ("Error message: " + e.getMessage () + "\n");
      s.append ("Error code: " + ((SQLException) e).getErrorCode () + "\n");
    }
    else
    {
      s.append (e + "\n");
    }
    return (s.toString ());
  }


  public static void printErrorMessage (Exception e)
  {
    System.err.println (Cookbook.getErrorMessage (e));
  }
}

使用:

import java.sql.*;
import com.kitebird.mcb.Cookbook;


public class Harness
{
  public static void main (String[] args)
  {
    Connection conn = null;
    try
    {
      conn = Cookbook.connect ();
      System.out.println ("Connected");
    }
    catch (Exception e)
    {
      Cookbook.printErrorMessage (e);
      System.exit (1);
    }
    finally
    {
      if (conn != null)
      {
        try
        {
          conn.close ();
          System.out.println ("Disconnected");
        }
        catch (Exception e)
        {
          String err = Cookbook.getErrorMessage (e);
          System.out.println (err);
        }
      }
    }
  }
}

 

执行语句并获取结果

SQL语句有些是获取信息,有些是改变信息。

1,改变信息,不返回信息:INSERT , DELETE ,  UPDATE等
2,不改变信息,不返回信息:比如USE
3,返回信息:SELECT , SHOW , EXPLAIN或DESCRIBE

python操作mysql的演示:stmt.py:

#!/usr/bin/python
# stmt.py: demonstrate statement processing in Python
# (without placeholders)

import sys
import mysql.connector
import cookbook

try:
  conn = cookbook.connect()
except mysql.connector.Error as e:
  print("Cannot connect to server")
  print("Error code: %s" % e.errno)
  print("Error message: %s" % e.msg)
  sys.exit(1)

print("Fetch rows with fetchone")
try:
#@ _FETCHONE_
  cursor = conn.cursor()
  cursor.execute("SELECT id, name, cats FROM profile")
  while True:
    row = cursor.fetchone()
    if row is None:
      break
    print("id: %s, name: %s, cats: %s" % (row[0], row[1], row[2]))
  print("Number of rows returned: %d" % cursor.rowcount)
  cursor.close()
#@ _FETCHONE_
except mysql.connector.Error as e:
  print("Oops, the statement failed")
  print("Error: %s" % e)

# Note: Following loop would be shorter if written like this:
#  for row in cursor:
# But don't do that because surrounding text in book discusses how to
# use rows as a direct-access array following the fetch operation.

print("Fetch rows with fetchall")
try:
#@ _FETCHALL_
  cursor = conn.cursor()
  cursor.execute("SELECT id, name, cats FROM profile")
  rows = cursor.fetchall()
  for row in rows:
    print("id: %s, name: %s, cats: %s" % (row[0], row[1], row[2]))
  print("Number of rows returned: %d" % cursor.rowcount)
  cursor.close()
#@ _FETCHALL_
except mysql.connector.Error as e:
  print("Oops, the statement failed")
  print("Error: %s" % e)

print("Fetch rows using cursor as iterator")
try:
#@ _CURSOR_ITERATOR_
  cursor = conn.cursor()
  cursor.execute("SELECT id, name, cats FROM profile")
  for (id, name, cats) in cursor:
    print("id: %s, name: %s, cats: %s" % (id, name, cats))
  print("Number of rows returned: %d" % cursor.rowcount)
  cursor.close()
#@ _CURSOR_ITERATOR_
except mysql.connector.Error as e:
  print("Oops, the statement failed")
  print("Error: %s" % e)

print("Execute UPDATE statement (no placeholders)")
try:
#@ _DO_1_
  cursor = conn.cursor()
  cursor.execute("UPDATE profile SET cats = cats+1 WHERE name = 'Sybil'")
  print("Number of rows updated: %d" % cursor.rowcount)
  cursor.close()
  conn.commit()
#@ _DO_1_
except mysql.connector.Error as e:
  print("Oops, the statement failed")
  print("Error: %s" % e)

conn.close()

执行结果:

$ python stmt.py 
Fetch rows with fetchone
id: 1, name: Sybil, cats: 1
id: 2, name: Nancy, cats: 3
id: 3, name: Ralph, cats: 4
id: 4, name: Lothair, cats: 5
id: 5, name: Henry, cats: 1
id: 6, name: Aaron, cats: 1
id: 7, name: Joanna, cats: 0
id: 8, name: Stephen, cats: 0
Number of rows returned: 8
Fetch rows with fetchall
id: 1, name: Sybil, cats: 1
id: 2, name: Nancy, cats: 3
id: 3, name: Ralph, cats: 4
id: 4, name: Lothair, cats: 5
id: 5, name: Henry, cats: 1
id: 6, name: Aaron, cats: 1
id: 7, name: Joanna, cats: 0
id: 8, name: Stephen, cats: 0
Number of rows returned: 8
Fetch rows using cursor as iterator
id: 1, name: Sybil, cats: 1
id: 2, name: Nancy, cats: 3
id: 3, name: Ralph, cats: 4
id: 4, name: Lothair, cats: 5
id: 5, name: Henry, cats: 1
id: 6, name: Aaron, cats: 1
id: 7, name: Joanna, cats: 0
id: 8, name: Stephen, cats: 0
Number of rows returned: 8
Execute UPDATE statement (no placeholders)
Number of rows updated: 1

注意:除了fetchall方法之后,其他都只能往前,不能往后。不过使用fetchall注意不要把内存塞满了。

语句中的特殊字符和NULL值

引号、反斜杠及NULL,另外还有防止SQL注入攻击。可以使用占位符或引用函数。比如:

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De'Mont','1973-01-12','blue','eggroll',4);

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De''Mont','1973-01-12','blue','eggroll',4);

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De\'Mont','1973-01-12','blue','eggroll',4);

在没有开启ANSI_QUOTES SQL的情况下:
INSERT INTO profile (name,birth,color,foods,cats)
VALUES("De'Mont",'1973-01-12','blue','eggroll',4);

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De''Mont','1973-01-12',NULL,'eggroll',4);


INSERT INTO profile (name,birth,color,foods,cats)
VALUES(?,?,?,?,?)
INSERT INTO profile (name,birth,color,foods,cats)
VALUES(%s,%s,%s,%s,%s)

Python种用%s表示占位符,原始%用%%表示。

#!/usr/bin/python

import mysql.connector
import cookbook

conn = cookbook.connect()

try:
#@ _FETCHLOOP_
  cursor = conn.cursor()
  cursor.execute("SELECT name, birth, foods FROM profile")
  for row in cursor:
    row = list(row)  # convert nonmutable tuple to mutable list
    for i, value in enumerate(row):
      if value is None:  # is the column value NULL?
        row[i] = "NULL"
    print("name: %s, birth: %s, foods: %s" % (row[0], row[1], row[2]))
  cursor.close()
#@ _FETCHLOOP_
except mysql.connector.Error as e:
  print("Oops, the statement failed")
  print("Error: %s" % e)

conn.close()

更加推荐的格式如下:

#!/usr/bin/python

import mysql.connector
import cookbook

conn = cookbook.connect()

try:
#@ _FETCHLOOP_
  cursor = conn.cursor()
  cursor.execute("SELECT name, birth, foods FROM profile")
  for row in cursor:
    row = list(row)  # convert nonmutable tuple to mutable list
    for i, value in enumerate(row):
      if value is None:  # is the column value NULL?
        row[i] = "NULL"
    print("name: {0}, birth: {1}, foods: {2}".format(row[0], row[1], row[2]))
  cursor.close()
#@ _FETCHLOOP_
except mysql.connector.Error as e:
  print("Oops, the statement failed")
  print("Error: {0}".format(e))

conn.close()

 

 

© 著作权归作者所有

共有 人打赏支持
python测试开发人工智能安全
粉丝 185
博文 104
码字总数 390691
作品 0
邵阳
QA/测试工程师
私信 提问
MySQL命令操作(Linux平台)

Linux shell 批量创建数据库/表 Shell 脚本如下: 执行脚本后,结果如下: MySQL 从常用操作命令: 显示数据库 show databases; 切换数据库 use top123; 显示数据库表 show tables; 修改主键...

长平狐
2013/01/06
50
0
MySQL命令操作(Linux平台)

Linux shell 批量创建数据库/表 Shell 脚本如下: 执行脚本后,结果如下: MySQL 从常用操作命令: 显示数据库 show databases; 切换数据库 use top123; 显示数据库表 show tables; 修改主键...

晨曦之光
2012/03/02
286
0
MYSQL常用命令汇总

MYSQL常用命令汇总 mysqld.exe 和 mysql.exe 有什么区别? mysqld.exe 是MySQL后台程序(即MySQL服务器)。要想使用客户端程序,该程序必须运行,因为客户端通过连接服务器来访问数据库。 mysq...

白志华
2016/02/22
195
0
MySQL · 特性分析 · Statement Digest

背景 在对数据库进行性能调优的时候,除了参数、配置的调整以外,SQL调优也是重要的手段,同时也是收益最大的一环。 当DBA对业务库进行sql调优的时候,如何做到有的放矢,投入产出受益最大?...

阿里云RDS-数据库内核组
2015/11/03
0
0
关于一些PHP,Mysql计算,统计的一些问题

大概描述下最近的一些项目需求,其实也不复杂。但是牵涉到一些计算和统计,使整体的性能不是特别好 那是家建筑公司,下面可能有30多个建筑工地。每个月,各建筑工地要把相关的财务报表,也就...

吕秀才
2012/03/28
1K
7

没有更多内容

加载失败,请刷新页面

加载更多

EOS docker开发环境

使用eos docker镜像是部署本地EOS开发环境的最轻松愉快的方法。使用官方提供的eos docker镜像,你可以快速建立一个eos开发环境,可以迅速启动开发节点和钱包服务器、创建账户、编写智能合约....

汇智网教程
55分钟前
3
0
《唐史原来超有趣》的读后感优秀范文3700字

《唐史原来超有趣》的读后感优秀范文3700字: 作者:花若离。我今天分享的内容《唐史原来超有趣》这本书的读后感,我将这本书看了一遍之后就束之高阁了,不过里面的内容一直在在脑海中回放,...

原创小博客
今天
6
0
IC-CAD Methodology知识图谱

CAD (Computer Aided Design),计算机辅助设计,指利用计算机及其图形设备帮助设计人员进行设计工作,这个定义同样可以用来近似描述IC公司CAD工程师这个岗位的工作。 早期IC公司的CAD岗位最初...

李艳青1987
今天
6
0
CompletableFuture get方法一直阻塞或抛出TimeoutException

问题描述 最近刚刚上线的服务突然抛出大量的TimeoutException,查询后发现是使用了CompletableFuture,并且在执行future.get(5, TimeUnit.SECONDS);时抛出了TimeoutException异常,导致接口响...

xiaolyuh
今天
5
0
dubbo 搭建与使用

官网:http://dubbo.apache.org/en-us/ 一,安装监控中心(可以不安装) admin管理控制台,monitor监控中心 下载 bubbo ops 这个是新版的,需要node.js环境,我没有就用老版的了...

小兵胖胖
今天
7
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部