Java与Python连接mysql比较

Java连接Mysql

添加mysql-jdbc 依赖
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>${mysql.driver.version}</version>
        <scope>runtime</scope>
    </dependency>

/**
 * Java 连接 mysql 例子
 * 1,加载驱动
 * 2，创建链接
 * 3，创建Statement 执行语句
 * 4,得到返回结果
 */
public class Test {
    private static Connection connectoin;
    private static Statement st;
    public static void main(String[] args) throws SQLException {
        getConnection();
        getStatement();
        select();
        insert();
        select();
        update();
        select();
        delete();
        select();
        close();
    }
    private static void delete() throws SQLException {
        String sql = "delete from test where id='2'";
        if(st.executeUpdate(sql)>0){
            System.out.println("删除---------");
        };
    }
    private static void insert() throws SQLException {
        String sql = "INSERT INTO `test` (`id`, `name`) VALUES ('2', 'lisi')";
        if(st.executeUpdate(sql)>0){
            System.out.println("添加----------");
        };
    }
    private static void update() throws SQLException {
        //默认自动提交  true
        //false 设置手动提交
        connectoin.setAutoCommit(false);
        String sql = "UPDATE test SET `name`='lisi1' WHERE `id`='2'";
        if(st.executeUpdate(sql)>0){
            System.out.println("修改----------");
        };
        //提交
        //connectoin.commit();
        //回滚
        connectoin.rollback();
    }
    private static void select() throws SQLException {
        String sql = "select * from test";
        //两种写法
        //st.execute(sql);ResultSet rt = st.getResultSet();
        ResultSet rt = st.executeQuery(sql);
        while(rt.next()){
            System.out.println("id="+rt.getString("id")+"  name="+rt.getString("name"));
            //如果是下标访问从1开始
            //System.out.println("id="+rt.getString(1)+"  name="+rt.getString(2));
        }

    }
    /**
     * 加载驱动，创建连接
     */
    private static void getConnection(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://192.168.18.152:3306/test?"
                    + "user=root&password=123456&useUnicode=true&characterEncoding=UTF8";
            connectoin = DriverManager.getConnection(url);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建执行sql类
     */
    private static void getStatement(){
        try {
            st = connectoin.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 关闭连接
     */
    private static void close(){
        try {
        if (st!=null)
                st.close();
        if(connectoin!=null)
            connectoin.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

Python连接Mysql

安装 pymysql 包
pip insall pymysql

# -*- coding-utf8 -*-
import pymysql
from pymysql import IntegrityError, DatabaseError

config = {
          'host':'192.168.18.152',
          'port':3306,
          'user':'root',
          'password':'123456',
          'db':'test',
          'charset':'utf8',
          'cursorclass':pymysql.cursors.DictCursor,
          }
# 创建连接
connection = pymysql.Connect(**config)
#得到游标
cursor = connection.cursor()
# python 默认的提交方式是手动提交 False
print(connection.get_autocommit())
try:
    insert = "insert into test (id,name) values(%s,%s)"
    i = cursor.execute(insert,('2','lisi'))
    connection.commit()
    print(i)
    print("insert =========================")
except DatabaseError as e:
    print(e)
    connection.rollback()


try:
    delete = "delete from test where id=%s"
    d = cursor.execute(delete,('2'))
    connection.commit()
    print(d)
    print("delete =========================")
except DatabaseError as e:
    print(e)
    connection.rollback()

try:
    select = "select * from test"
    s = cursor.execute(select)
    all = cursor.fetchall()
    print(s)
    print(all)
    print("select ===================")
    connection.commit()
except DatabaseError as e:
    print(e)
    connection.rollback()


connection.close()