Database Updates
With built-in database connectivity, Maple allows engineers and scientists to quickly develop and deploy powerful applications that combine large enterprise databases with the state-of-the-art analysis and visualization tools of Maple. You can easily query, create, and update your databases in Maple, without any detailed SQL knowledge. Now in Maple 18, database connectivity has been extended to also include native support for SQLite databases. SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. For more information about SQLite, refer to http://www.sqlite.org/
In the following example, we will use Maple's Database package to import population data from the G20 countries into a SQLite database, access the database, and produce population tables and charts.
Import Population Data from a .csv File into SQLite Database
withArrayTools:
withDatabase[SQLite]:
withStringTools:
Import Data File
csv≔FileTools:-JoinPathkernelopts'datadir',SQLite,G20-Population.csv:
data:=ImportMatrixcsv:
Create in Memory Database
The Open command opens a new database connection.
db:=Open:memory:
db:=SQLite database,table( [( "main" ) = "" ] )
Create Population Table
sql:=sprintfCREATE TABLE population (%s),Joinconvertdata1,'list',,
sql:=CREATE TABLE population (Date, USA, CHN, JPN, DEU, FRA, BRA, GBR, ITA, RUS, IND, CAN, AUS, ESP, MEX, KOR, IDN, TUR, SAU, ARG, ZAF)
The Execute command executes a SQL statement using the provided database connection.
Executedb,sql
Insert Data Into Population Table
nrows:=Sizedata,1:
ncols:=Sizedata,2:
sql:=sprintfINSERT INTO population VALUES (%s),Join?$ncols,,
sql:=INSERT INTO population VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
The Prepare command prepares a SQL statement for execution.
stmt:=Preparedb,sql
stmt:=SQLite statement,INSERT INTO population VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
forifrom2tonrowsdo for j to ncols do Bindstmt,j,dataij end do; Stepstmt; Resetstmt,'clear'=trueend do:
The Finalize command finalizes the prepared statement.
Finalizestmt:
Read Data Back
sql≔SELECT ⋅ FROM population
sql:=SELECT * FROM population
stmt:=SQLite statement,SELECT * FROM population
FetchAll returns all of the rows of the prepared statement.
FetchAllstmt
Plot Data for Population in Canada from 1975 to 1990
sql:=SELECT date, CAN FROM population WHERE date >= '1975-12-31' AND date <= '1990-12-31'
stmt:=SQLite statement,SELECT date, CAN FROM population WHERE date >= '1975-12-31' AND date <= '1990-12-31'
population:=Matrix1..0,1..2:
row:=1:
whileStepstmt=RESULT_ROWdo populationrow,1:=sscanfFetchstmt,0,%d-%d-%d1; populationrow,2:=Fetchstmt,1;row:=row+1end do:
plots:-pointplotpopulation, color=Red, title=Population of Canada: 1975 − 1990, labels = Year,Population, style=line, thickness=3, size=800,400
See Also
Database
Database[SQLite][Execute]
Database[SQLite][Finalize]
Database[SQLite][FetchAll]
Database[SQLite][Open]
Database[SQLite][Prepare]
Download Help Document
