文档章节

mysql常用操作汇总

python测试开发人工智能安全
 python测试开发人工智能安全
发布于 2016/06/24 17:02
字数 1949
阅读 189
收藏 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测试开发人工智能安全
粉丝 183
博文 80
码字总数 363712
作品 0
邵阳
QA/测试工程师
私信 提问
MySQL命令操作(Linux平台)

Linux shell 批量创建数据库/表 Shell 脚本如下: # create database and tableHOST='localhost'PORT='3306'USER='root'PWD=''DBNAME='top123'TABLENAME='gametop800' mysql_login=''mysqlcr......

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

Linux shell 批量创建数据库/表 Shell 脚本如下: # create database and tableHOST='localhost'PORT='3306'USER='root'PWD=''DBNAME='top123'TABLENAME='gametop800' mysql_login=''mysqlcr......

晨曦之光
2012/03/02
211
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
看图轻松理解数据结构与算法系列(基于数组的栈)

前言 推出一个新系列,《看图轻松理解数据结构和算法》,主要使用图片来描述常见的数据结构和算法,轻松阅读并理解掌握。本系列包括各种堆、各种队列、各种列表、各种树、各种图、各种排序等...

超人汪小建
08/04
0
0

没有更多内容

加载失败,请刷新页面

加载更多

大数据教程(6.1)hadoop生态圈介绍及就业前景

1. HADOOP背景介绍 1.1、什么是HADOOP 1.HADOOP是apache旗下的一套开源软件平台 2.HADOOP提供的功能:利用服务器集群,根据用户的自定义业务逻辑,对海量数据进行分布式处理 3.HADOOP的核心组...

em_aaron
2分钟前
0
0
hadoop垃圾回收站

在生产生,hdfs回收站必须是开启的,一般设置为7天。 fs.trash.interval 为垃圾回收站保留时间,如果为0则禁用回收站功能。 fs.trash.checkpoint.interval 回收站检查点时间,一般设置为小于...

hnairdb
昨天
0
0
腾讯与Github的魔幻会面背后的故事…

10月22日,腾讯开源管理办公室有幸邀请到Github新晋CEO Nat Friedman,前来鹅厂参观交流。目前腾讯已经有近70个项目在Github上开源,共获得17w stars,世界排名11位。Github是腾讯开源的主阵...

腾讯开源
昨天
1
0
单例模式

单例模式(Singleton pattern)属于创建型设计模式。 保证一个类仅有一个实例,并提供一个访问它的全局访问点。 通常我们可以让一个全局变量使得一个对象被访问,但它不能防止你实例化多个对...

NinjaFrog
昨天
1
0
TypeScript基础入门之装饰器(三)

转载 TypeScript基础入门之装饰器(三) 继续上篇文章[TypeScript基础入门之装饰器(二)] 访问器装饰器 Accessor Decorator在访问器声明之前声明。 访问器装饰器应用于访问器的属性描述符,可用...

durban
昨天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部