python,java,go,perl,php,c等驱动连接数据库

python,java,go,perl,php,c等驱动连接数据库

最近接到一个需求,使用python,java,go,perl,php,c等语言连接数据库,并做一些相应的SQL动作.
唉,开搞了!

1.测试环境

环境	描述
系统环境	ubuntu20.04
数据库	mysql8.0.25
python	3.9.5
java	16.0.2
go	16.2
gcc	9.3.0
perl	v5.30.0
php	7.4

2.python

2.1安装

参考：https://www.python.org/downloads/

直接|下载

安装:

xz -d Python-3.8.11.tar.xz
tar xf Python-3.8.11.tar 
cd Python-3.8.11
./configure
make && make install

• 在运行程序前，检查自己系统是否已经安装以下软件！

yum install pip \
pip3 install mysql-connector \
pip3 install pymysql \
pip3 install --upgrade pip \
pip install  cryptography \
pip3 install DBUtils==1.3 \

2.2完整代码

import pymysql
from DBUtils.PooledDB import PooledDB


def getConnStr():
    host = "172.17.0.2"
    port = 3306
    user = "greatdb"
    password = "greatdb"
    database = "test"
    conn = PooledDB(
        pymysql,
        host=host,
        user=user,
        port=port,
        password=password,
        database=database
    )
    return conn


def execSqlSingle(conn_db, str_sql):
    con = conn_db.connection()
    cur = con.cursor()
    try:
        cur.execute(str_sql)
        result = cur.fetchall()
        print(result)
        con.commit()
    except Exception as e:
        con.rollback()
        print("SQL FAIL: ==>> {},result: ==>> {}".format(str_sql, e))
    con.close()
    cur.close()


if __name__ == '__main__':
    conn = getConnStr()
    execSqlSingle(conn, "create table t_python(id int);")
    execSqlSingle(conn, "insert into t_python values (1),(2),(3),(4),(5);")
    execSqlSingle(conn, "delete from t_python where id =1;")
    execSqlSingle(conn, "select * from t_python;")

运行结果:

python3 ConnDB.py
((2,), (3,), (4,), (5,))

3.java

3.1 安装

安装略过
配置环境变量:

export JAVA_HOME=/usr/local/jdk-14.0.2
export PATH=$PATH:$JAVA_HOME/bin:$JAVA_HOME/jre/bin
export CLASSPATH=.:$JAVA_HOME/lib

直接下载

3.2代码

ConnDB.java

package ConnDB;

import java.sql.*;
import java.sql.DriverManager;

public class ConnDB {
    public static class Sqlnode {

        private static final String URL =
                "jdbc:mysql://172.17.0.2:3306/test";
        private static final String NAME = "greatdb";
        private static final String PASSWORD = "greatdb";

        public Connection run() {
            {
                try {
                    Class.forName("com.mysql.cj.jdbc.Driver");
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }
            }
            Connection conn1 = null;
            {
                try {
                    conn1 = DriverManager.getConnection(URL, NAME, PASSWORD);
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            return conn1;
        }
    }

    public static void main(String[] args) throws SQLException{
        Sqlnode conn = new Sqlnode();
        Statement st = conn.run().createStatement();

        String create = "create table t(id int);";
        st.executeUpdate(create);
        st.executeUpdate("insert into t values (1),(2),(3),(4),(5);");
        st.executeUpdate("update t set id =100 where id =1;");
        st.executeUpdate("delete from t where id >90;");
        try{
            ResultSet rs = st.executeQuery("select * from t;");
            while(rs.next()){
                System.out.println("查询结果:" +
                        rs.getString("id"));

            }
            System.out.println("执行成功");
        }catch (SQLException e){
            e.printStackTrace();
        }

    }
}

编译执行:

javac ConnDB.java
java ConnDB.java
查询结果:2
查询结果:3
查询结果:4
查询结果:5
执行成功

4.go

4.1 安装

参考:https://golang.org/dl/

wget https://studygolang.com/dl/golang/go1.15.6.linux-amd64.tar.gz
gunzip go1.17.linux-amd64.tar.gz 
mv go1.17.linux-amd64.tar  /usr/local/
sudo mv go1.17.linux-amd64.tar  /usr/local/
tar xf go1.17.linux-amd64.tar 
cd /usr/local/go
go get github.com/go-sql-driver/mysql
go env -w GOPROXY=https://goproxy.io,direct
go install  github.com/go-sql-driver/mysql

环境变量:

export GOROOT=/usr/local/go
export GOPATH=/opt/go
export PATH=$PATH:$GOROOT/bin:$GOPATH

4.2 代码

connDB.go

package main
import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)
func main(){
	db,err := sql.Open("mysql","greatdb:greatdb@(172.17.0.2:3306)/test")
	defer db.Close()
	err = db.Ping()
	if err!=nil{
	fmt.Println("数据库连接失败!")
	return
	}
	// create
	sql:="create table t_go(id int);"
	db.Exec(sql)
	insert:="insert into t_go values (1),(2),(3),(4),(5);"
	db.Exec(insert)
	update:="update t_go set id=10 where id=1;"
	db.Exec(update)
	dele:= "delete from t_go where id =2;"
	db.Exec(dele)
	rows,err:=db.Query("select * from t_go;")
	
	for rows.Next(){
		var id  int
		rows.Scan(&id)
		fmt.Print(id,"\n")
	}

}

编译执行

go build connDB.go 
go run connDB.go
10
3
4
5

5.c语言

如果是ubuntu系统,则执行apt install gcc,g++即可.
直接下载

上代码

#include <mysql.h>
#include <stdio.h>

int main() {
    MYSQL *conn;
    MYSQL_RES *res;
    MYSQL m_sqlCon;
    MYSQL_ROW column;

   char *server = "172.17.0.2";
   char *user = "greatdb";
   char *password = "greatdb";
   char *database = "test";

    conn = mysql_init(&m_sqlCon);

   /* Connect to database */
   if (!mysql_real_connect(conn, server,
          user, password, database, 0, NULL, 0)) {
      fprintf(stderr, "%s\n", mysql_error(conn));
      exit(1);
   }

   // set names
   mysql_query(conn,"set names GB2312");
   mysql_query(conn,"create table t_c(id int)");
	// insert
    if (mysql_query(conn,"insert into t_c values (1),(2),(3),(4),(5)")){
  	printf("false");
  }else{
	  printf("insert OK  ...\n");
   }
	// update
	
 	 if (mysql_query(conn,"update t_c set id=10 where id=1")){
  	printf("false");
  }else{
	  printf("update OK ...\n");
   }
   // delete
		 if (mysql_query(conn,"delete from t_c where id =2")){
  	printf("false");
  }else{
	  printf("delete OK ...\n");
   }
	
   res = mysql_store_result(conn);

   if (!res) {
   	printf("couldn't get result");
	return false;
   }
   // column number
   int j = mysql_num_fields(res);
   char *str_field[32];
   for (int i=0;i<j;i++){
   	str_field[i] = mysql_fetch_field(res)->name;
   }
   // print column
   for(int i=0;i<j;i++){
   	printf("%10s\t",str_field[i]);
	
   }
   printf("\n");
   while(column = mysql_fetch_row(res)){
   	printf("%10s\t %10s\n",column[0],column[1]);
   }

    mysql_close(conn);
}

编译执行

gcc connApi.c -o connect_api -I /usr/include/mysql -L /usr/lib/mysql -lmysqlclient
./connect_api 
insert OK  ...
update OK ...
delete OK ...

greatdb查询结果:
GreatDB Cluster[test]> select * from t_c;
+------+
| id   |
+------+
|   10 |
|    3 |
|    4 |
|    5 |
+------+
4 rows in set (0.00 sec)

6.php

如果是ubuntu系统,则安装apache2,如果 是centos系统,将apache2换为httpd即可.

6.1 安装

sudo apt install apache2
sudo apt install libapache2-mod-php7.4
sudo apt-get install php
sudo apt-get install libapache2-mod-php
sudo systemctl restart apache2 # apache2 占用主机80端口,如果本机装有nginx请考虑换端口或关掉nginx
# 验证
jiaona@jiaona-OptiPlex-7080:/var/www/html$ php -v
PHP 7.4.3 (cli) (built: Jul  5 2021 15:13:35) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.3, Copyright (c), by Zend Technologies

修改apache2配置文件: 在文件末尾将php文件也加进去

jiaona@jiaona-OptiPlex-7080:/var/www/html$ tail -1 /etc/apache2/apache2.conf
AddType application/x-httpd-php .php .htm .html

修改php文件,加入以下几行:

jiaona@jiaona-OptiPlex-7080:/var/www/html$ tail -20 /etc/php/7.4/apache2/php.ini
;ffi.preload=
;extension_dir =".\ext" 

register_globals= On  

short_open_tag =On 

cgi.force_redirect= 0 

fastcgi.impersonate= 1

cgi.rfc2616_headers= 1

extension=php_mbstring.dll

extension=php_mysql.dll

extension=php_mysqli.dll

extension=php_pdo_mysql.dl

重启

6.2 上代码

shell>cd /var/www/html
shell>vim phpConn.php

<?php
$conn = mysqli_connect("172.17.0.2","greatdb","greatdb");

if (!$conn)
{
	die("Couldn't not connect:".mysqli_error());
}
// create table

if (mysqli_query($conn,"create table test.t_php(id int)") == TRUE) {
	printf("create ok...\n");
}
if (mysqli_query($conn,"insert into test.t_php values (1),(2),(3),(4),(5)") == TRUE ) {
	print("insert ok...\n");
}
if (mysqli_query($conn,"update test.t_php set id=20 where id=2)") == TRUE ) {
	printf("update ok...\n");
}
if (mysqli_query($conn,"delete from test.t_php where id=1") == TRUE ) {
	printf("delete ok...\n");
}
if ($row = mysqli_query($conn,"select id from test.t_php")) {
	printf("select returned %d rows.\n",mysqli_num_rows($row));

	mysqli_free_result($row);
}
mysqli_close($conn)
?>

编译执行

shell> php phpConn.php
create ok...
insert ok...
delete ok...
select returned 4 rows.
回到greatdb查看返回的数据:
GreatDB Cluster[test]> select * from t_php;
+------+
| id   |
+------+
|    2 |
|    3 |
|    4 |
|    5 |
+------+
4 rows in set (0.01 sec)

7. perl

7.1 安装

首先安装perl的依赖

perl -MCPAN -e shell
install DBD::mysql
install DBI

上代码

#!/usr/bin/perl

use strict;
use DBI;
my $host="172.17.0.2";
my $user="greatdb";
my $driver="mysql";
my $database = "test";
my $dsn = "DBI:$driver:database=$database:$host";
my $password= "greatdb";

## conn db

my $conn = DBI->connect($dsn,$user,$password) or die $DBI::errstr;
my $a = $conn->prepare("create table test.t_perl(id int)");
$a->execute() or die $DBI::errstr;
$a->finish();

my $id="100";
my $sth = $conn->prepare("insert into test.t_perl(id) values (?),(2),(3),(4),(5)");
$sth->execute($id) or die $DBI::errstr;
if ($sth)
{
	print "insert ok ..."
}
$sth->finish();


my $sth_update = $conn->prepare("update test.t_perl set id =10 where id =100");
$sth_update->execute() or die $DBI::errstr;
print "affected rows : " +$sth_update->rows;
$sth_update->finish();

# select 
my $select = $conn->prepare("select * from test.t_perl");
$select->execute();
while (my @row = $select->fetchrow_array())
{
	print join('\t',@row)."\n";
}
$select->finish();
my $sth_delete = $conn->prepare("delete from test.t_perl where id = 10");
$sth_delete->execute() or die $DBI::errstr;
print "affected rows : "+ $sth_delete->rows;

$sth_delete->finish();

$conn->disconnect();

编译执行

jiaona@jiaona-OptiPlex-7080:~/driver_conn_db$ ./perlConn.perl 
insert ok ...110
2
3
4
5
回到gretdb,查看执行结果
jiaona@jiaona-OptiPlex-7080:~/driver_conn_db$$greatsql -ugreatdb -pgreatdb -h172.17.0.2 -P3306
greatsql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10661
Server version: 8.0.25-15-greatdbcluster5.0.7-GA GreatDB Cluster, Release GA, Revision 7b86a29eb19

Copyright (c) 2009-2021 BEIJING GREAT OPENSOURCE SOFTWARE TECHNOLOGY CO.,LTD. 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

GreatDB Cluster[(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
GreatDB Cluster[test]> select * from t_perl;
+------+
| id   |
+------+
|    2 |
|    3 |
|    4 |
|    5 |
+------+
4 rows in set (0.00 sec)