Sign in to follow this  
Followers 0
Jfish

SQLite with command line parameters for import

17 posts in this topic

All:

I just started using SQLite and think it is awesome. I got to a point in my project where I desire an "import data" function that will pull information directly into SQLite. I have read the forum postings and figured out the process for doing this from the command line:

SQLite> testimport.db

SQLite> .separator ,

SQLite> .import importdata.csv testdata

This works. I now wish to call the SQlite executable with those parameters and automate the process (I can do it with the send function but prefer to avoid that if possible). I came across the $cmdLine array and was able to set the above parameters in Scite (not sure how to do it without Scite). However, it is not working. Here is what I did:

ShellExecute(@ScriptDir&"\sqlite3.exe",$cmdLine[1] & $cmdLine[2] & $cmdLine[3],@ScriptDir)

I am attaching the JPG of the parameters in Scite.

Any help you could offer would be greatly appreciated.

JFish

post-24738-12801571461473_thumb.jpg


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites



try this

ShellExecute(@ScriptDir&"\sqlite3.exe",$cmdLine[1] & ' ' & $cmdLine[2] & ' ' & $cmdLine[3],@ScriptDir)

Share this post


Link to post
Share on other sites

try this

ShellExecute(@ScriptDir&"\sqlite3.exe",$cmdLine[1] & ' ' & $cmdLine[2] & ' ' & $cmdLine[3],@ScriptDir)

Thanks. I tried that but got the same result as when I tried it without the spaces. The script runs to completion but there is no data in the database. Are my parameters properly formatted (they appear in the jpg on my posting).


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

sorry for my rapid stupid answer ;)

now i see what u want,,, btw, i dont look at your picture before,,, my shame...

how bout send multiple time using send,,, something like this:

winactivate("SQLlite window title")
winwaitactive("SQLlite window title")
send ($parameter1)
sleep(2000)
send ($parameter2)
sleep(2000)
send ($parameter3)
sleep(2000)

cos sometime,,, lame code is what we need :blink:

Share this post


Link to post
Share on other sites

sorry for my rapid stupid answer ;)

now i see what u want,,, btw, i dont look at your picture before,,, my shame...

how bout send multiple time using send,,, something like this:

winactivate("SQLlite window title")
winwaitactive("SQLlite window title")
send ($parameter1)
sleep(2000)
send ($parameter2)
sleep(2000)
send ($parameter3)
sleep(2000)

cos sometime,,, lame code is what we need :blink:

LOL. Yes, I already got it to work with some lame code - similar to your post. However, I was hoping to avoid using the Send function because it is not always reliable without the appropriate wait times etc. I think it would be much cleaner to be able to send multiple parameters to the SQLite exe. I can sucessfully send the database name as a parameter. It appears to ignore the next two parameters after database for some reason.

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

I now wish to call the SQlite executable with those parameters and automate the process (I can do it with the send function but prefer to avoid that if possible).

Have you considered just reading in the CSV, and inserting each line into the database from the script? You should be able to do it with a simple loop.

Share this post


Link to post
Share on other sites

Have you considered just reading in the CSV, and inserting each line into the database from the script? You should be able to do it with a simple loop.

@willchan

Yes, I did consider that. I would think it would likely be slower with large amounts of data though I have not tested that theory. I can get data in with other approaches. I know I can get the data in with your suggestion or with the "lame" sleep method approach referenced above. However, I was hoping to learn how to leverage the command line import function of SQLite. If it can't be done that way then I will resign myself to one the other approaches that I know will work.


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

Have you considered looking at the SQlite functions in the help file?

Try the _SQLite_Exec() With the Insert Into. Look at the example for that function.


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

Have you considered looking at the SQlite functions in the help file?

Try the _SQLite_Exec() With the Insert Into. Look at the example for that function.

@GEOSoft

Thank you for your reply. Yes, I did read the help file - and that example as well. Please correct me if I am wrong but that is using the SQL command to insert values into a table. In that case the values are hard coded (or they could be from somewhere within the Au3 script). I am trying to import a file with values into an existing table. I realize that I could read the file into my application in an array an use insert with the _SQLite_Exec() function. However, I am trying to figure out if I can just load the file directly with the .import function that SQLite offers from the commandline by passing parameters to the command line that include the items in my first post. Is that possible? Also, am I miscontruing your point on the help file?


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

I would think it would likely be slower with large amounts of data though I have not tested that theory.

With large amounts of data, writing each line separately would probably be slower. You could encapsulate blocks of data in "BEGIN" and "COMMIT" statements, however, and see an increase in performance.

For example:

_SQLite_Exec($sSQLiteDB, "BEGIN;")
...
_SQLite_Exec($sSQLiteDB, "INSERT INTO " & $sMyTableName & " VALUES " & $MyData[$i])
...
_SQLite_Exec($sSQLiteDB, "COMMIT;")

If you are going to be importing very large amounts of data, try splitting your data up into blocks of 50-100 lines of data per transaction block.

Edit: Since the command line requires multiple commands, I do not see a way that you can do it with the command line without using the method referred to above as the lame sleep method.

Edited by willichan

Share this post


Link to post
Share on other sites

SQLite> testimport.db

SQLite> .separator ,

SQLite> .import importdata.csv testdata

=

_SQLite_SQLiteExe($sDatabaseFilePath, ".separator ," & @CRLF & ".import '" & FileGetShortName($hImportFilePath) & "' TableName" & @CRLF, $sOutputFile)

#include <File.au3>
#include <SQLite.au3>

_CreateDatabaseFile()
_ImportTxtToSql()
_ReadArrayToSql()

Func _CreateDatabaseFile()
    Local $hFile = @ScriptDir & "\Test.Txt"
    If FileExists($hFile) Then Return
    Local $iArraysize = 1000, $aArray[$iArraysize]
    For $i = 0 To $iArraysize - 1 Step 1
        $aArray[$i] = $i & "-One," & $i & "-Two," & $i & "-Three," & $i & "-Four"
    Next
    _FileWriteFromArray($hFile, $aArray)
EndFunc   ;==>_CreateDatabaseFile

Func _ImportTxtToSql()
    Local $sDatabaseFile = @ScriptDir & "\Test.Db", $sOutputFile
    Local $hFile = @ScriptDir & "\Test.Txt"
    If FileExists($sDatabaseFile) Then FileDelete($sDatabaseFile)
    _SQLite_SQLiteExe($sDatabaseFile, "CREATE TABLE test (ColOne,ColTwo,ColThree,ColFour);", $sOutputFile)
    Local $iTimmer = TimerInit()
    _SQLite_SQLiteExe($sDatabaseFile, ".mode csv" & @CRLF & ".import '" & FileGetShortName($hFile) & "' test" & @CRLF, $sOutputFile)
    ConsoleWrite(TimerDiff($iTimmer) & " :Time to import file" & @CR)
EndFunc   ;==>_ImportTxtToSql

Func _ReadArrayToSql()
    Local $sDatabaseFile = @ScriptDir & "\Test.Db", $sOutputFile
    Local $hFile = @ScriptDir & "\Test.Txt"
    If FileExists($sDatabaseFile) Then FileDelete($sDatabaseFile)
    Local $aArray
    _FileReadToArray($hFile, $aArray)
    _SQLite_Startup()
    _SQLite_Open($sDatabaseFile)
    _SQLite_Exec(-1, "CREATE TABLE test (ColOne,ColTwo,ColThree,ColFour);")
    Local $iTimmer = TimerInit()
    _SQLite_Exec(-1, "Begin")
    For $i = 1 To $aArray[0] Step 1
        _SQLite_Exec(-1, "INSERT INTO test values ('" & StringReplace($aArray[$i], ",", "','") & "');")
    Next
    _SQLite_Exec(-1, "END ;")
    ConsoleWrite(TimerDiff($iTimmer) & " :Time to read & insert array" & @CR)
    _SQLite_Close()
    _SQLite_Shutdown()
EndFunc   ;==>_ReadArrayToSql

158.833791645834 :Time to import file

334.608682332621 :Time to read & insert array


GDIPlusDispose - A modified version of GDIPlus that auto disposes of its own objects before shutdown of the Dll using the same function Syntax as the original.EzMySql UDF - Use MySql Databases with autoit with syntax similar to SQLite UDF.

Share this post


Link to post
Share on other sites

SQLite> testimport.db

SQLite> .separator ,

SQLite> .import importdata.csv testdata

=

_SQLite_SQLiteExe($sDatabaseFilePath, ".separator ," & @CRLF & ".import '" & FileGetShortName($hImportFilePath) & "' TableName" & @CRLF, $sOutputFile)

#include <File.au3>
#include <SQLite.au3>

_CreateDatabaseFile()
_ImportTxtToSql()
_ReadArrayToSql()

Func _CreateDatabaseFile()
    Local $hFile = @ScriptDir & "\Test.Txt"
    If FileExists($hFile) Then Return
    Local $iArraysize = 1000, $aArray[$iArraysize]
    For $i = 0 To $iArraysize - 1 Step 1
        $aArray[$i] = $i & "-One," & $i & "-Two," & $i & "-Three," & $i & "-Four"
    Next
    _FileWriteFromArray($hFile, $aArray)
EndFunc   ;==>_CreateDatabaseFile

Func _ImportTxtToSql()
    Local $sDatabaseFile = @ScriptDir & "\Test.Db", $sOutputFile
    Local $hFile = @ScriptDir & "\Test.Txt"
    If FileExists($sDatabaseFile) Then FileDelete($sDatabaseFile)
    _SQLite_SQLiteExe($sDatabaseFile, "CREATE TABLE test (ColOne,ColTwo,ColThree,ColFour);", $sOutputFile)
    Local $iTimmer = TimerInit()
    _SQLite_SQLiteExe($sDatabaseFile, ".mode csv" & @CRLF & ".import '" & FileGetShortName($hFile) & "' test" & @CRLF, $sOutputFile)
    ConsoleWrite(TimerDiff($iTimmer) & " :Time to import file" & @CR)
EndFunc   ;==>_ImportTxtToSql

Func _ReadArrayToSql()
    Local $sDatabaseFile = @ScriptDir & "\Test.Db", $sOutputFile
    Local $hFile = @ScriptDir & "\Test.Txt"
    If FileExists($sDatabaseFile) Then FileDelete($sDatabaseFile)
    Local $aArray
    _FileReadToArray($hFile, $aArray)
    _SQLite_Startup()
    _SQLite_Open($sDatabaseFile)
    _SQLite_Exec(-1, "CREATE TABLE test (ColOne,ColTwo,ColThree,ColFour);")
    Local $iTimmer = TimerInit()
    _SQLite_Exec(-1, "Begin")
    For $i = 1 To $aArray[0] Step 1
        _SQLite_Exec(-1, "INSERT INTO test values ('" & StringReplace($aArray[$i], ",", "','") & "');")
    Next
    _SQLite_Exec(-1, "END ;")
    ConsoleWrite(TimerDiff($iTimmer) & " :Time to read & insert array" & @CR)
    _SQLite_Close()
    _SQLite_Shutdown()
EndFunc   ;==>_ReadArrayToSql

Thank you! That is exactly what I was looking for. I am going to test it tonight.

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

Thank you! That is exactly what I was looking for. I am going to test it tonight.

Okay, I could not wait - already tested it. It works. It may be me but I could not figure out how to do that from reading the examples in the help file. I really appreciate the help.


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

Your welcome ;) , One thing to note when importing, watch out for any of the data having coma's it will throw the delimeter all out of wack. :blink:


GDIPlusDispose - A modified version of GDIPlus that auto disposes of its own objects before shutdown of the Dll using the same function Syntax as the original.EzMySql UDF - Use MySql Databases with autoit with syntax similar to SQLite UDF.

Share this post


Link to post
Share on other sites

Your welcome ;) , One thing to note when importing, watch out for any of the data having coma's it will throw the delimeter all out of wack. :blink:

That makes sense. Thanks for the tip and thanks again for taking the time to post the solution.


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

Does this require the beta?

I dont think it's working for me.

#include <File.au3>
#include <SQLite.au3>


Local $hFile = @ScriptDir & "\Test.Txt"
Local $iArraysize = 1000, $aArray[$iArraysize]
For $i = 0 To $iArraysize - 1 Step 1
    $aArray[$i] = $i & "-One," & $i & "-Two," & $i & "-Three," & $i & "-Four"
Next
_FileWriteFromArray($hFile, $aArray)

Local $sDatabaseFile = @ScriptDir & "\Test.Db", $sOutputFile
Local $hFile = @ScriptDir & "\Test.Txt"
If FileExists($sDatabaseFile) Then FileDelete($sDatabaseFile)
_SQLite_SQLiteExe($sDatabaseFile, "CREATE TABLE test (ColOne,ColTwo,ColThree,ColFour);", $sOutputFile)
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') :(' & @MIN & ':' & @SEC & ':' & @MSEC & ') $sOutputFile = ' & $sOutputFile & '   :   Error code: ' & @error & '    Extended code: ' & @extended & @CRLF) ;### Debug Console
Local $iTimmer = TimerInit()
_SQLite_SQLiteExe($sDatabaseFile, ".mode csv" & @CRLF & ".import '" & FileGetShortName($hFile) & "' test" & @CRLF, $sOutputFile)
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') :(' & @MIN & ':' & @SEC & ':' & @MSEC & ') $sOutputFile = ' & $sOutputFile & '   :   Error code: ' & @error & '    Extended code: ' & @extended & @CRLF) ;### Debug Console
ConsoleWrite(TimerDiff($iTimmer) & " :Time to import file" & @CR)


_SQLite_Startup()
_SQLite_Open($sDatabaseFile)



Local $aResult, $iRows, $iColumns, $iRval

$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM test;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
    _SQLite_Display2DResult($aResult)
Else
    MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf



Exit

I am getting

@@ Debug(45) :(12:33:443) $sOutputFile = : Error code: 1 Extended code: 0

@@ Debug(48) :(12:33:443) $sOutputFile = : Error code: 1 Extended code: 0

0.327353336865404 :Time to import file

! SQLite.au3 Error

--> Function: _SQLite_Query

--> Query: SELECT * FROM test;

--> Error: no such table: test

+>02:12:34 AutoIT3.exe ended.rc:0

>Exit code: 0 Time: 3.390

Seems the database is never created? Edited by ParoXsitiC

Share this post


Link to post
Share on other sites

Place _SQlite_Startup() before the import code. This is necessary with the current release, but will be corrected in the next one (when is another question entirely).


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0