mysql常用操作汇总

于 2016-06-24 17:02:00 发布
阅读量74 收藏
点赞数
CC 4.0 BY-SA版权
原文链接：https://my.oschina.net/u/1433482/blog/700520
本文详细介绍使用Python和Java连接并操作MySQL数据库的方法。包括连接配置、错误处理、执行SQL语句及结果集处理等内容。

2019独角兽企业重金招聘Python工程师标准>>> hot3.png

本文已经迁移至：

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()

 

 

转载于:https://my.oschina.net/u/1433482/blog/700520

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值