Raku By Example

use DBIish;

my $host='148.84.25.171';
my $password='xxxxxxxx';

my $dbh = DBIish.connect('mysql', :$host, :port(3306),
        :database<test>, :user<aldwx>, :$password);

my $sth = $dbh.do(q:to/STATEMENT/);
    DROP TABLE IF EXISTS nom
    STATEMENT

$sth = $dbh.do(q:to/STATEMENT/);
    CREATE TABLE nom (
        name        varchar(4),
        description varchar(30),
        quantity    int,
        price       numeric(5,2)
    )
    STATEMENT

$sth = $dbh.do(q:to/STATEMENT/);
    INSERT INTO nom (name, description, quantity, price)
    VALUES ( 'BUBH', 'Hot beef burrito', 1, 4.95 )
    STATEMENT

$sth = $dbh.prepare(q:to/STATEMENT/);
    INSERT INTO nom (name, description, quantity, price)
    VALUES ( ?, ?, ?, ? )
    STATEMENT

$sth.execute('TAFM', 'Mild fish taco', 1, 4.85);
$sth.execute('BEOM', 'Medium size orange juice', 2, 1.20);

$sth = $dbh.prepare(q:to/STATEMENT/);
    SELECT name, description, quantity, price, quantity*price AS amount
    FROM nom
    STATEMENT

$sth.execute();

my @rows = $sth.allrows();
say @rows.elems; # 3
$sth.finish;
$dbh.dispose;

## 拼接 sql

my @days =  Date.new('2017-10-01') .. Date.new('2017-11-15');

sub prepare_sql($table, @insert_fields, @update_fields) {
    # 准备 sql 语句模版
    my @on_duplicate_key;
    my @placeholder     ;

    (@on_duplicate_key.append($_ ~ '=' ~ 'VALUES(' ~ $_ ~ ')') for @update_fields);
    (@placeholder.append('%s') for @insert_fields);

    my $insert_columns = '(' ~ @insert_fields.join(',') ~ ')';
    my $values = 'values(' ~ @placeholder.join(',') ~ ')';
    my $on_duplicate_key_update = @on_duplicate_key.join(',');

    return  "insert into %s %s %s ON DUPLICATE KEY UPDATE %s".printf($table, $insert_columns, $values, $on_duplicate_key_update);
}



my @insert_fields = ('app_key','day','open_count','update_at'); # 插入的字段
my @update_fields = ('open_count', 'update_at');                # 需要更新的字段
my $sql           = prepare_sql('aldstat_trend_analysis', @insert_fields, @update_fields);

say $sql;
Examples
