ODBC export question

AFL Programming
#1

I am well aware of the odbc and odbca plugins, which seem to primarily be about retrieving data stored in a database for use in AB. I would like to do the opposite, store data from AB in a database for access by other programs. I understand that this can be done via exporting csv files and then importing them into MariaDB or mySQL, but, as usual, I am looking for the elegant solution. Any ideas?

Thanks in advance,

 John
1 Like
#2

@BBands what kind of data do you plan to export?

Using the odbcExecuteSQL( "sql statement" ) you can write data to your database (using the AFL plugin).

From the documentation:

This function takes string parameter containing valid SQL statement such as INSERT, UPDATE, DELETE, etc., and as such allows write operations on the database. It is not intended to be used with SELECT statements or other statements (stored procedure calls) returning recordsets as it returns only true/false on success/failure.

I used it to export all the quotes of one of my local databases to MS-SQL, and this was a straightforward task.
Using it for other purposes maybe require a more elaborate workflow.

#3

mySQL and MariaDB have LOAD DATA INFILE command which is very 'elegant' way to automate imports from CSV and also a lot faster than running queries over ODBC:
https://dev.mysql.com/doc/refman/8.0/en/load-data.html

3 Likes
#4

This is what I was referring to when I cited a more elaborate workflow.

It is possible to combine all the available options you have, like writing/exporting your data in a flat file (maybe also using a "Batch" to do it in different steps, allowing you to include optional external scripts/tools) and then, check for specific tables existence and/or to create/purge them as needed via odbcExecuteSQL and finally, as suggested by @Tomasz, create a LOAD DATA INFILE statement and execute it again via ODBC directly from one of your your formulas.

1 Like
#5

Tomasz and Beppe,

Thanks for the comments. That is exactly what I have been doing. I am in the process of streamlining/automating my daily workflow and looking around for better, more elegant/efficient ways to get it all done.

Tomasz, thanks for AB, a fine tool.
AB community, thanks for all the help.

Best,

 John
#6

I would like give a bit back to the community, so here is a Python script to import files exported by AB to a MySQL database.

Enjoy,

John

# John Bollinger 28 March 2019
# Copyright Bollinger Capital Management 2019
# MIT License

import os
try:
    import MySQLdb
    print "MySQL driver loaded"
except ImportError:
    print "You need the MySQLdb module."
    sys.exit(-1)

# Enter your MySQL connect variables here
host_ = '127.0.0.1'
user_ = 'your_user_name'
password_ = 'your_passsword'
db_ = 'your_database'

# Make a db connection
try:
    conn = MySQLdb.connect(host = host_,
                           user = user_,
                           passwd = password_,
                           db = db_,
                           autocommit=True)
    print('Connected to DB: {}'.format(host_))
except MySQLdb.Error, err:
    print "Can't make the MySQL connection"
    print "Error %d: %s" % (err.args[0], err.args[1])
    sys.exit(1)

# load the data into the db
exportDir = "C:/path/to/your/files/"
for fileName in os.listdir(exportDir):
    file = exportDir + fileName
    ticker = fileName[:-4]
    curs = conn.cursor()
    sql = "delete from price where symbol = '" + ticker + "';"
    curs.execute(sql)
    sql = "load data local "
    sql += "infile '" + file
    sql += "' into table price "
    sql += "fields terminated by ',' "
    sql += "LINES TERMINATED BY '\\r\\n' "
    sql += "ignore 1 lines "
    sql += "(symbol, @date, open, high, low, close, volume) "
    sql += "set date = STR_TO_DATE(@date, '%m/%d/%Y');"
    curs.execute(sql)
    print(ticker + ' Done')
conn.close()
# That's all folks!
10 Likes
#7

Thanks John. Great to have you as part of the Amibroker community.

1 Like
#8

It didn't seem to make sense to give the import code without the export code, so here it is. Full disclosure, adapted from code found here. (Is that Buy = 0: really needed?)

Good trading,

John

// John Bollinger 29 March 2019
// Copyright Bollinger Capital Management 2019
// MIT License

// export directory
directory = "C:\\Users\\Public\\Portfolio\\exports\\";
// get the symbol
symbol = Name();
// extension to use
extension = ".txt";
// open export file
fh = fopen( directory + symbol + extension, "w" );
if ( fh ) // did we get a file handle?
{
    Date_ = DateTime(); // get a list of dates
    fputs( "Symbol,Date,Open,High,Low,Close,Volume\n", fh ); // write header
    for ( i = 0; i < BarCount; i++ ) // write the data records
    {
        fputs( symbol + "," , fh ); // symbol
        fputs( DateTimeToStr( Date_[ i ] ) + ",", fh ); // date
        fputs( StrFormat( "%g,%g,%g,%g,%g\n", open[ i ], high[ i ], low[ i ], close[ i ], volume[ i ] ), fh ); // quotes
    }
    fclose( fh ); // close the file
}
Buy = 0; // line required by SCAN option
// That's all Folks!
2 Likes
#9

Yes, if you run scan, you need Buy=0; as otherwise Analysis window would complain about missing Buy/Sell statements.