1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
|
package Catmandu::Importer::DBI;
use Catmandu::Sane;
use DBI;
use Moo;
use MooX::Aliases;
use namespace::clean;
our $VERSION = '0.0702';
with 'Catmandu::Importer';
has data_source => (is => 'ro', required => 1, alias => 'dsn');
has username => (is => 'ro', alias => 'user');
has password => (is => 'ro', alias => 'pass');
has query => (is => 'ro', required => 1);
has dbh =>
(is => 'ro', init_arg => undef, lazy => 1, builder => '_build_dbh',);
has sth =>
(is => 'ro', init_arg => undef, lazy => 1, builder => '_build_sth',);
sub _build_dbh {
my $self = $_[0];
DBI->connect(
$self->dsn,
$self->user,
$self->password,
{
AutoCommit => 1,
RaiseError => 1,
mysql_auto_reconnect => 1,
mysql_enable_utf8 => 1,
pg_utf8_strings => 1,
sqlite_use_immediate_transaction => 1,
sqlite_unicode => 1,
}
);
}
sub _build_sth {
my $self = $_[0];
my $sth = $self->dbh->prepare($self->query);
$sth->execute;
$sth;
}
sub generator {
my ($self) = @_;
return sub {
$self->sth->fetchrow_hashref();
}
}
sub DESTROY {
my ($self) = @_;
$self->sth->finish;
$self->dbh->disconnect;
}
=head1 NAME
Catmandu::Importer::DBI - Catmandu module to import data from any DBI source
=head1 LIMITATIONS
Text columns are assumed to be utf-8.
=head1 SYNOPSIS
# From the command line
$ catmandu convert DBI --dsn dbi:mysql:foobar --user foo --password bar --query "select * from table"
# From Perl code
use Catmandu;
my %attrs = (
dsn => 'dbi:mysql:foobar' ,
user => 'foo' ,
password => 'bar' ,
query => 'select * from table'
);
my $importer = Catmandu->importer('DBI',%attrs);
# Optional set extra parameters on the database handle
# $importer->dbh->{LongReadLen} = 1024 * 64;
$importer->each(sub {
my $row_hash = shift;
...
});
=head1 DESCRIPTION
This L<Catmandu::Importer> can be used to access data stored in a relational database.
Given a database handle and a SQL query an export of hits will be exported.
=head1 CONFIGURATION
=over
=item dsn
Required. The connection parameters to the database. See L<DBI> for more information.
Examples:
dbi:mysql:foobar <= a local mysql database 'foobar'
dbi:Pg:dbname=foobar;host=myserver.org;port=5432 <= a remote PostGres database
dbi:SQLite:mydb.sqlite <= a local SQLLite file based database mydb.sqlite
dbi:Oracle:host=myserver.org;sid=data01 <= a remote Oracle database
Drivers for each database need to be available on your computer. Install then with:
cpanm DBD::mysql
cpanm DBD::Pg
cpanm DBD::SQLite
cpanm DBD::Oracle
=item user
Optional. A user name to connect to the database
=item password
Optional. A password for connecting to the database
=item query
Required. An SQL query to be executed against the datbase.
=back
=head1 SEE ALSO
L<Catmandu>, L<Catmandu::Importer> , L<Catmandu::Store::DBI>
=cut
1;
|
