Hide keyboard shortcuts

Hot-keys on this page

r m x p   toggle line displays

j k   next/prev highlighted chunk

0   (zero) top of page

1   (one) first highlighted chunk

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

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

807

808

809

810

811

812

813

814

815

816

817

818

819

820

821

822

823

824

825

826

827

828

829

830

831

832

833

834

835

836

837

838

839

840

841

842

843

844

845

846

847

848

849

850

851

852

853

854

855

856

857

858

859

860

861

862

863

864

865

866

867

868

869

870

871

872

873

874

875

876

877

878

879

880

881

882

883

884

885

886

887

888

889

890

891

892

893

894

895

896

897

898

899

900

901

902

903

904

905

906

907

908

909

910

911

912

913

914

915

916

917

918

919

920

921

922

923

924

925

926

927

928

929

930

931

932

933

934

935

936

937

938

939

940

941

942

943

944

945

946

947

948

949

950

951

952

953

954

955

956

957

958

959

960

961

962

963

964

965

966

967

968

969

970

971

972

973

974

975

976

977

978

979

980

981

982

983

984

985

986

987

988

989

990

991

992

993

994

995

996

997

998

999

1000

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1012

1013

1014

1015

1016

1017

1018

1019

1020

1021

1022

1023

1024

1025

1026

1027

1028

1029

1030

1031

1032

1033

1034

1035

1036

1037

1038

1039

1040

1041

1042

1043

1044

1045

1046

1047

1048

1049

1050

1051

1052

1053

1054

1055

1056

1057

1058

1059

1060

1061

1062

1063

1064

1065

1066

1067

1068

1069

1070

1071

1072

1073

1074

1075

1076

1077

1078

1079

1080

1081

1082

1083

1084

1085

1086

1087

1088

1089

1090

1091

1092

1093

1094

1095

1096

1097

1098

1099

1100

1101

1102

1103

1104

1105

1106

1107

1108

1109

1110

1111

1112

1113

1114

1115

1116

1117

1118

1119

1120

1121

1122

1123

1124

1125

1126

1127

1128

1129

1130

1131

1132

1133

1134

1135

1136

1137

1138

1139

1140

1141

1142

1143

1144

1145

1146

1147

1148

1149

1150

1151

1152

1153

1154

1155

1156

1157

1158

1159

1160

1161

1162

1163

1164

1165

1166

1167

1168

1169

1170

1171

1172

1173

1174

1175

1176

1177

1178

1179

1180

1181

1182

1183

1184

1185

1186

1187

1188

1189

1190

1191

1192

1193

1194

1195

1196

1197

1198

1199

1200

1201

1202

1203

1204

1205

1206

1207

1208

1209

1210

1211

1212

1213

1214

1215

1216

1217

1218

1219

1220

1221

1222

1223

1224

1225

1226

1227

1228

1229

1230

1231

1232

1233

1234

1235

1236

1237

1238

1239

1240

1241

1242

1243

1244

1245

1246

1247

1248

1249

1250

1251

1252

1253

1254

1255

1256

1257

1258

1259

1260

1261

1262

1263

1264

1265

1266

1267

1268

1269

1270

1271

1272

1273

1274

1275

1276

1277

1278

1279

1280

1281

1282

1283

1284

1285

1286

1287

1288

1289

1290

1291

1292

1293

1294

1295

1296

1297

1298

1299

1300

1301

1302

1303

1304

1305

1306

1307

1308

1309

1310

1311

1312

1313

1314

1315

1316

1317

1318

1319

1320

1321

1322

1323

1324

1325

1326

1327

1328

1329

1330

1331

1332

1333

1334

1335

1336

1337

1338

1339

1340

1341

1342

1343

1344

1345

1346

1347

1348

1349

1350

1351

1352

1353

1354

1355

1356

1357

1358

1359

1360

1361

1362

1363

1364

1365

1366

1367

1368

1369

1370

1371

1372

1373

1374

1375

1376

1377

1378

1379

1380

1381

1382

1383

1384

1385

1386

1387

1388

1389

1390

1391

1392

1393

1394

1395

1396

1397

1398

1399

1400

1401

1402

1403

1404

1405

1406

1407

1408

1409

1410

1411

1412

1413

1414

1415

1416

1417

1418

1419

1420

1421

1422

1423

1424

1425

1426

1427

1428

1429

1430

1431

1432

1433

1434

1435

1436

1437

1438

1439

1440

1441

1442

1443

1444

1445

1446

1447

1448

1449

1450

1451

1452

1453

1454

1455

1456

1457

1458

1459

1460

1461

1462

1463

1464

1465

1466

1467

1468

1469

1470

1471

1472

1473

1474

1475

1476

1477

1478

1479

1480

1481

1482

1483

1484

1485

1486

1487

1488

1489

1490

1491

1492

1493

1494

1495

1496

1497

1498

1499

1500

1501

1502

1503

1504

1505

1506

1507

1508

1509

1510

1511

1512

1513

1514

1515

1516

1517

1518

1519

1520

1521

1522

1523

1524

1525

1526

1527

1528

1529

1530

1531

1532

1533

1534

1535

1536

1537

1538

1539

1540

1541

1542

1543

1544

1545

1546

1547

1548

1549

1550

1551

1552

1553

1554

1555

1556

1557

1558

1559

1560

1561

1562

1563

1564

1565

1566

1567

1568

1569

1570

1571

1572

1573

1574

1575

1576

1577

1578

1579

1580

1581

1582

1583

1584

1585

1586

1587

1588

1589

1590

1591

1592

1593

1594

1595

1596

1597

1598

1599

1600

1601

1602

1603

1604

1605

1606

1607

1608

1609

1610

1611

1612

1613

1614

1615

1616

1617

1618

1619

1620

1621

1622

1623

1624

1625

1626

1627

1628

1629

1630

1631

1632

1633

1634

1635

1636

1637

1638

1639

1640

1641

1642

1643

1644

1645

1646

1647

1648

1649

1650

1651

1652

1653

1654

1655

1656

1657

1658

1659

1660

1661

1662

1663

1664

1665

1666

1667

1668

1669

1670

1671

1672

1673

1674

1675

1676

1677

1678

1679

1680

1681

1682

1683

1684

1685

1686

1687

1688

1689

1690

1691

1692

1693

1694

1695

1696

1697

1698

1699

1700

1701

1702

1703

1704

1705

1706

1707

1708

1709

1710

1711

1712

1713

1714

1715

1716

1717

1718

1719

1720

1721

1722

1723

1724

1725

1726

1727

1728

1729

1730

1731

1732

1733

1734

1735

1736

1737

1738

1739

1740

1741

1742

1743

1744

1745

1746

1747

1748

1749

1750

1751

1752

1753

1754

1755

1756

1757

1758

1759

1760

1761

1762

1763

1764

1765

1766

1767

1768

1769

1770

1771

1772

1773

1774

1775

1776

1777

1778

1779

1780

1781

1782

1783

1784

1785

1786

1787

1788

1789

1790

1791

1792

1793

1794

1795

1796

1797

1798

1799

1800

1801

1802

1803

1804

1805

1806

1807

1808

1809

1810

1811

1812

1813

1814

1815

1816

1817

1818

1819

1820

1821

1822

1823

1824

1825

1826

1827

1828

1829

1830

1831

1832

1833

1834

1835

1836

1837

1838

1839

1840

1841

1842

1843

1844

1845

1846

1847

1848

1849

1850

1851

1852

1853

1854

1855

1856

1857

1858

1859

1860

1861

1862

1863

1864

1865

1866

1867

1868

1869

1870

1871

1872

1873

1874

1875

1876

1877

1878

1879

1880

1881

1882

1883

1884

1885

1886

1887

1888

1889

1890

1891

1892

1893

1894

1895

1896

1897

1898

1899

1900

1901

1902

1903

1904

1905

1906

1907

1908

1909

1910

1911

1912

1913

1914

1915

1916

1917

1918

1919

1920

1921

1922

1923

1924

1925

1926

1927

1928

1929

1930

1931

1932

1933

1934

1935

1936

1937

1938

1939

1940

1941

1942

1943

1944

1945

1946

1947

1948

1949

1950

1951

1952

1953

1954

1955

1956

1957

1958

1959

1960

1961

1962

1963

1964

1965

1966

1967

1968

1969

1970

1971

1972

1973

1974

1975

1976

1977

1978

1979

1980

1981

1982

1983

1984

1985

1986

1987

1988

1989

1990

1991

1992

1993

1994

1995

1996

1997

1998

1999

2000

2001

2002

2003

2004

2005

2006

2007

2008

2009

2010

2011

2012

2013

2014

2015

2016

2017

2018

2019

2020

2021

2022

2023

2024

2025

2026

2027

2028

2029

2030

2031

2032

2033

2034

2035

2036

2037

2038

2039

2040

2041

2042

2043

2044

2045

2046

2047

2048

2049

2050

2051

2052

2053

2054

2055

2056

2057

2058

2059

2060

2061

2062

2063

2064

2065

2066

2067

2068

2069

2070

2071

2072

2073

2074

2075

2076

2077

2078

2079

2080

2081

2082

2083

2084

2085

2086

2087

2088

2089

2090

2091

2092

2093

2094

2095

2096

2097

2098

2099

2100

2101

2102

2103

2104

2105

2106

2107

2108

2109

2110

2111

2112

2113

2114

2115

2116

2117

2118

2119

2120

2121

2122

2123

2124

2125

2126

2127

2128

2129

2130

2131

2132

2133

2134

2135

2136

2137

2138

2139

2140

2141

2142

2143

2144

2145

2146

2147

2148

2149

2150

2151

2152

2153

2154

2155

2156

2157

2158

2159

""" 

Relational (sqlite) Databases Module 

 

INFO: 

 

This module implements classes (SQLDatabase and SQLQuery (pythonic 

implementation for the user with little or no knowledge of sqlite)) 

that wrap the basic functionality of sqlite. 

 

Databases are constructed via a triple indexed dictionary called a 

skeleton. A skeleton should be constructed to fit the following format:: 

 

| - skeleton -- a triple-indexed dictionary 

| - outer key -- table name 

| - inner key -- column name 

| - inner inner key -- one of the following: 

| - ``primary_key`` -- boolean, whether column has been set as 

primary key 

| - ``index`` -- boolean, whether column has been set as index 

| - ``unique`` -- boolean, whether column has been set as unique 

| - ``sql`` -- one of ``'TEXT'``, ``'BOOLEAN'``, ``'INTEGER'``, 

``'REAL'``, or other user defined type 

 

An example skeleton of a database with one table, that table with one 

column:: 

 

{'table1':{'col1':{'primary_key':False, 'index':True, 'sql':'REAL'}}} 

 

SQLDatabases can also be constructed via the add, drop, and commit 

functions. The vacuum function is also useful for restoring hard disk space 

after a database has shrunk in size. 

 

A SQLQuery can be constructed by providing a query_dict, which is a 

dictionary with the following sample format:: 

 

{'table_name': 'tblname', 'display_cols': ['col1', 'col2', 'col3'], 'expression':[col, operator, value]} 

 

Finally a SQLQuery also allows the user to directly input the query 

string for a database, and also supports the '?' syntax by allowing an 

argument for a tuple of parameters to query. 

 

For full details, please see the tutorial. sage.graphs.graph_database.py 

is an example of implementing a database class in Sage using this 

interface. 

 

AUTHORS: 

 

- R. Andrew Ohana (2011-07-16): refactored and rewrote most of the code; 

merged the Generic classes into the non-Generic versions; changed the 

skeleton format to include a boolean indicating whether the column stores 

unique keys; changed the index names so as to avoid potential ambiguity 

- Emily A. Kirkman (2008-09-20): added functionality to generate plots and 

reformat output in show 

- Emily A. Kirkman and Robert L. Miller (2007-06-17): initial version 

 

""" 

# FUTURE TODOs (Ignore for now): 

# - order by clause in query strings 

# - delete from query containing joins 

# - add data by column 

# - wrap sqlalchemy 

# - create query interface (with interact) 

# - allow kwds arguments to SQLQuery (like GraphQuery) 

 

#***************************************************************************** 

# Copyright (C) 2011 R. Andrew Ohana <andrew.ohana@gmail.com> 

# Copyright (C) 2007 Emily A. Kirkman 

# Robert L. Miller 

# 

# Distributed under the terms of the GNU General Public License (GPL) 

# as published by the Free Software Foundation; either version 2 of 

# the License, or (at your option) any later version. 

# http://www.gnu.org/licenses/ 

#***************************************************************************** 

from __future__ import print_function 

 

import sqlite3 as sqlite 

import os 

import re 

from sage.misc.all import tmp_filename 

from sage.structure.sage_object import SageObject 

 

sqlite_keywords = ['ABORT','ACTION','ADD','AFTER','ALL','ALTER','ANALYZE', 

'AND','AS','ASC','ATTACH','AUTOINCREMENT','BEFORE','BEGIN','BETWEEN','BY', 

'CASCADE','CASE','CAST','CHECK','COLLATE','COLUMN','COMMIT','CONFLICT', 

'CONSTRAINT','CREATE','CROSS','CURRENT_DATE','CURRENT_TIME', 

'CURRENT_TIMESTAMP','DATABASE','DEFAULT','DEFERRABLE','DEFERRED','DELETE', 

'DESC','DETACH','DISTINCT','DROP','EACH','ELSE','END','ESCAPE','EXCEPT', 

'EXCLUSIVE','EXISTS','EXPLAIN','FAIL','FOR','FOREIGN','FROM','FULL', 

'GLOB','GROUP','HAVING','IF','IGNORE','IMMEDIATE','IN','INDEX','INDEXED', 

'INITIALLY','INNER','INSERT','INSTEAD','INTERSECT','INTO','IS','ISNULL', 

'JOIN','KEY','LEFT','LIKE','LIMIT','MATCH','NATURAL','NO','NOT','NOTNULL', 

'NULL','OF','OFFSET','ON','OR','ORDER','OUTER','PLAN','PRAGMA','PRIMARY', 

'QUERY','RAISE','REFERENCES','REGEXP','REINDEX','RELEASE','RENAME', 

'REPLACE','RESTRICT','RIGHT','ROLLBACK','ROW','SAVEPOINT','SELECT','SET', 

'TABLE','TEMP','TEMPORARY','THEN','TO','TRANSACTION','TRIGGER','UNION', 

'UNIQUE','UPDATE','USING','VACUUM','VALUES','VIEW','VIRTUAL','WHEN', 

'WHERE'] 

 

def regexp(expr, item): 

""" 

Function to define regular expressions in pysqlite. 

Returns ``True`` if parameter ``item`` matches the regular expression 

parameter ``expr``. 

Returns ``False`` otherwise (i.e.: no match). 

 

REFERENCES: 

 

- [Ha2005]_ 

 

EXAMPLES:: 

 

sage: from sage.databases.sql_db import regexp 

sage: regexp('.s.*','cs') 

True 

sage: regexp('.s.*','ccs') 

False 

sage: regexp('.s.*','cscccc') 

True 

""" 

r = re.compile(expr) 

return r.match(item) is not None 

 

def verify_type(type): 

""" 

Verify that the specified ``type`` is one of the allowed strings. 

 

EXAMPLES:: 

 

sage: from sage.databases.sql_db import verify_type 

sage: verify_type('INT') 

True 

sage: verify_type('int') 

True 

sage: verify_type('float') 

Traceback (most recent call last): 

... 

TypeError: float is not a legal type. 

 

""" 

types = ['INTEGER','INT','BOOLEAN','REAL','TEXT','BOOL','BLOB','NOTYPE'] 

if type.upper() not in types: 

raise TypeError('%s is not a legal type.'%type) 

return True 

 

def verify_column(col_dict): 

""" 

Verify that ``col_dict`` is in proper format, and return a dict with 

default values filled in. Proper format:: 

 

{'primary_key':False, 'index':False, 'unique': False, 'sql':'REAL'} 

 

EXAMPLES:: 

 

sage: from sage.databases.sql_db import verify_column 

sage: col = {'sql':'BOOLEAN'} 

sage: verify_column(col) 

{'index': False, 'primary_key': False, 'sql': 'BOOLEAN', 'unique': False} 

sage: col = {'primary_key':True, 'sql':'INTEGER'} 

sage: verify_column(col) 

{'index': True, 'primary_key': True, 'sql': 'INTEGER', 'unique': True} 

sage: verify_column({}) 

Traceback (most recent call last): 

... 

ValueError: SQL type must be declared, e.g. {'sql':'REAL'}. 

""" 

d = {} 

d['primary_key'] = col_dict.get('primary_key', False) 

d['index'] = col_dict.get('index', False) or d['primary_key'] 

d['unique'] = col_dict.get('unique', False) or d['primary_key'] 

if 'sql' not in col_dict: 

raise ValueError("SQL type must be declared, e.g. {'sql':'REAL'}.") 

if verify_type(col_dict['sql']): 

d['sql'] = col_dict['sql'] 

return d 

 

def verify_operator(operator): 

""" 

Checks that ``operator`` is one of the allowed strings. 

Legal operators include the following strings: 

 

- '=' 

- '<=' 

- '>=' 

- '<' 

- '>' 

- '<>' 

- 'like' 

- 'regexp' 

- 'is null' 

- 'is not null' 

 

EXAMPLES:: 

 

sage: from sage.databases.sql_db import verify_operator 

sage: verify_operator('<=') 

True 

sage: verify_operator('regexp') 

True 

sage: verify_operator('is null') 

True 

sage: verify_operator('not_an_operator') 

Traceback (most recent call last): 

... 

TypeError: not_an_operator is not a legal operator. 

""" 

binaries = ['=','<=','>=','like','<','>','<>','regexp'] 

unaries = ['is null','is not null'] 

if operator not in binaries and operator not in unaries: 

raise TypeError('%s is not a legal operator.'%operator) 

return True 

 

def construct_skeleton(database): 

""" 

Constructs a database skeleton from the sql data. The skeleton data 

structure is a triple indexed dictionary of the following format:: 

 

| - skeleton -- a triple-indexed dictionary 

| - outer key -- table name 

| - inner key -- column name 

| - inner inner key -- one of the following: 

| - ``primary_key`` -- boolean, whether column has been set as 

primary key 

| - ``index`` -- boolean, whether column has been set as index 

| - ``unique`` -- boolean, whether column has been set as unique 

| - ``sql`` -- one of ``'TEXT'``, ``'BOOLEAN'``, ``'INTEGER'``, 

``'REAL'``, or other user defined type 

 

An example skeleton of a database with one table, that table with one 

column:: 

 

{'table1':{'col1':{'primary_key':False, 'unique': True, 'index':True, 'sql':'REAL'}}} 

 

EXAMPLES:: 

 

sage: G = SQLDatabase(GraphDatabase().__dblocation__, False) 

sage: from sage.databases.sql_db import construct_skeleton 

sage: construct_skeleton(G).keys() 

[u'aut_grp', u'degrees', u'spectrum', u'misc', u'graph_data'] 

""" 

skeleton = {} 

cur = database.__connection__.cursor() 

exe = cur.execute("SELECT name FROM sqlite_master WHERE TYPE='table'") 

from sage.env import GRAPHS_DATA_DIR 

for table in exe.fetchall(): 

skeleton[table[0]] = {} 

exe1 = cur.execute("PRAGMA table_info(%s)"%table[0]) 

for col in exe1.fetchall(): 

if not col[2]: 

typ = u'NOTYPE' 

else: 

typ = col[2] 

skeleton[table[0]][col[1]] = {'sql':typ, \ 

'primary_key':(col[5]!=0), 'index':(col[5]!=0), 'unique': False} 

exe2 = cur.execute("PRAGMA index_list(%s)"%table[0]) 

for col in exe2.fetchall(): 

if col[1].find('sqlite') == -1: 

if database.__dblocation__ == \ 

os.path.join(GRAPHS_DATA_DIR,'graphs.db'): 

name = col[1] 

else: 

name = col[1][len(table[0])+3:] 

skeleton[table[0]][name]['index'] = True 

skeleton[table[0]][name]['unique'] = bool(col[2]) 

else: 

name = cur.execute("PRAGMA index_info(%s)"%col[1]) 

name = name.fetchone()[2] 

skeleton[table[0]][name]['unique'] = bool(col[2]) 

return skeleton 

 

p = 0 

def _create_print_table(cur, col_titles, **kwds): 

""" 

Creates a nice printable table from the cursor given with the given 

column titles. 

 

KEYWORDS: 

 

- ``max_field_size`` -- how wide each field can be 

- ``format_cols`` -- a dictionary that allows the user to specify the 

format of a column's output by supplying a function. The format of 

the dictionary is:: 

 

{'column_name':(lambda x: format_function(x))} 

 

- ``plot_cols`` -- a dictionary that allows the user to specify that a 

plot should be drawn by the object generated by a data slice. Note 

that plot kwds are permitted. The dictionary format is:: 

 

{'column_name':((lambda x: plot_function(x)),**kwds)} 

 

- ``relabel_cols`` -- a dictionary to specify a relabeling of column 

headers. The dictionary format is:: 

 

{'table_name':{'old_col_name':'new_col_name'}} 

 

- ``id_col`` -- reference to a column that can be used as an object 

identifier for each row 

 

- ``html_table`` -- boolean that if True creates an html table instead of 

a print table. Always set to True in the notebook. 

 

EXAMPLES:: 

 

sage: DB = SQLDatabase() 

sage: DB.create_table('simon',{'a1':{'sql':'bool', 'primary_key':False}, 'b2':{'sql':'int'}}) 

sage: DB.add_data('simon',[(0,0),(1,1),(1,2)]) 

sage: r = SQLQuery(DB, {'table_name':'simon', 'display_cols':['a1'], 'expression':['b2','<=', 6]}) 

sage: from sage.databases.sql_db import _create_print_table 

sage: cur = r.__database__.__connection__.cursor() 

sage: exe = cur.execute(r.__query_string__, r.__param_tuple__) 

sage: _create_print_table(cur, [des[0] for des in cur.description]) 

'a1 \n--------------------\n0 \n1 \n1 ' 

""" 

fcol_index = [] 

pcol_index = [] 

 

if 'format_cols' in kwds: 

fcol_map = [] 

for col in kwds['format_cols']: 

fcol_map.append(kwds['format_cols'][col]) 

fcol_index.append(col_titles.index(col)) 

if 'plot_cols' in kwds: 

pcol_map = [] 

for col in kwds['plot_cols']: 

pcol_map.append(kwds['plot_cols'][col]) 

pcol_index.append(col_titles.index(col)) 

 

max_field_size = kwds['max_field_size'] if 'max_field_size' in kwds \ 

else 20 

id_col_index = col_titles.index(kwds['id_col']) if 'id_col' in kwds \ 

else None 

 

if 'relabel_cols' in kwds: 

relabel_cols = kwds['relabel_cols'] 

for i in range(len(col_titles)): 

try: 

col_titles[i] = relabel_cols[col_titles[i]] 

except KeyError: 

continue 

global p 

p = 0 

def row_str(row, html): 

f = 0 

global p 

cur_str = [] 

for index in range(len(col_titles)): 

if index in pcol_index: 

if html: 

plot = pcol_map[p%len(pcol_map)](row[index]) 

plot.save('%d.png'%p, figsize=[1,1]) 

field_val = ' <td bgcolor=white align=center> ' \ 

+ '%s <br> <img src="cell://%d.png"> '%(row[index],p) \ 

+ '</td>\n' 

p += 1 

else: 

raise NotImplementedError('Cannot display plot on ' \ 

+ 'command line.') 

else: 

if index in fcol_index: 

if id_col_index is None: 

field_val = fcol_map[f](row[index]) 

else: 

field_val = fcol_map[f](row[index], row[id_col_index]) 

f += 1 

else: 

field_val = row[index] 

if html: 

field_val = ' <td bgcolor=white align=center> ' \ 

+ str(field_val) + ' </td>\n' 

else: 

field_val = str(field_val).ljust(max_field_size) 

cur_str.append(field_val) 

return ' '.join(cur_str) 

 

from sage.server.support import EMBEDDED_MODE 

if EMBEDDED_MODE or ('html_table' in kwds and kwds['html_table']): 

# Notebook Version 

ret = '<html><!--notruncate-->\n' 

ret += ' <table bgcolor=lightgrey cellpadding=0>\n' 

ret += ' <tr>\n <td bgcolor=white align=center> ' 

ret += (' </td>\n <td bgcolor=white ' \ 

+ 'align=center> ').join(col_titles) 

ret += ' </td>\n </tr>\n' 

ret += '\n'.join([' <tr>\n ' + row_str(row, True) + ' </tr>' \ 

for row in cur]) 

ret += '\n </table>\n</html>' 

else: 

# Command Prompt Version 

ret = ' '.join([col.ljust(max_field_size) for col in col_titles]) 

ret += '\n' + '-' * max_field_size * len(col_titles) + '\n' 

ret += '\n'.join([row_str(row, False) for row in cur]) 

return ret 

 

class SQLQuery(SageObject): 

def __init__(self, database, *args, **kwds): 

""" 

A query for a SQLite database. 

 

INPUT: 

 

- ``database`` -- a SQLDatabase object 

- ``query_dict`` -- a dictionary specifying the query itself. The 

format is:: 

 

{'table_name':'tblname', 'display_cols':['col1', 'col2','col3'], 'expression': [col, operator, value]} 

 

NOTE: 

Every SQL type we are using is ultimately represented as a string, 

so if you wish to save actual strings to a database, you actually 

need to do something like: '"value"'. 

 

See the documentation of ``SQLDatabase`` for an introduction to using 

SQLite in Sage. 

 

EXAMPLES:: 

 

sage: D = SQLDatabase() 

sage: D.create_table('simon',{'a1':{'sql':'bool', 'primary_key':False}, 'b2':{'sql':'int'}}) 

sage: D.add_data('simon',[(0,0),(1,2),(2,4)]) 

sage: r = SQLQuery(D, {'table_name':'simon', 'display_cols':['a1'], 'expression':['b2','<=', 3]}) 

sage: r.show() 

a1 

-------------------- 

0 

1 

""" 

if not isinstance(database, SQLDatabase): 

raise TypeError('%s is not a valid SQLDatabase'%database) 

self.__database__ = database 

total_args = len(args) + len(kwds) 

if total_args == 0: 

self.__query_string__ = '' 

self.__param_tuple__ = tuple() 

self.__query_dict__ = {} 

return 

for x in args: 

if isinstance(x,dict): 

if 'query_dict' not in kwds: 

kwds['query_dict'] = x 

elif isinstance(x, str): 

if 'query_string' not in kwds: 

kwds['query_string'] = x 

elif isinstance(x, tuple): 

if 'param_tuple' not in kwds: 

kwds['param_tuple'] = x 

if total_args > 2 or not ('query_dict' in kwds or \ 

'query_string' in kwds) or ('query_dict' in kwds and\ 

('param_tuple' in kwds or 'query_string' in kwds)): 

raise ValueError('Query must be constructed with either a ' \ 

+ 'dictionary or a string and tuple') 

 

if 'query_dict' in kwds: 

query_dict = kwds['query_dict'] 

else: 

self.__query_string__ = kwds['query_string'] 

if 'param_tuple' in kwds: 

self.__param_tuple__ = tuple((str(x) for x in kwds['param_tuple'])) 

else: 

self.__param_tuple__ = tuple() 

return 

 

if query_dict: 

skel = database.__skeleton__ 

if query_dict['table_name'] not in skel: 

raise ValueError("Database has no table" \ 

+ str(query_dict['table_name']) + ".") 

table_name = query_dict['table_name'] 

if query_dict['display_cols'] is not None: 

for column in query_dict['display_cols']: 

if column not in skel[table_name]: 

raise ValueError("Table has no column %s."%column) 

if query_dict['expression'][0] not in skel[table_name]: 

raise ValueError("Table has no column " \ 

+ str(query_dict['expression'][0]) + ".") 

 

self.__query_dict__ = query_dict 

self.__param_tuple__ = (str(query_dict['expression'][2]),) 

verify_operator(query_dict['expression'][1]) 

if query_dict['display_cols'] is None: 

self.__query_string__ = 'SELECT , FROM %s WHERE '%table_name \ 

+ '%s.%s '%(table_name, query_dict['expression'][0]) \ 

+ '%s ?'%query_dict['expression'][1] 

else: 

query_dict['display_cols'] = ['%s.%s'%(table_name, x) \ 

for x in query_dict['display_cols']] 

self.__query_string__ = 'SELECT ' \ 

+ ', '.join(query_dict['display_cols']) + ' FROM ' \ 

+ '%s WHERE %s.'%(table_name, table_name) \ 

+ '%s '%query_dict['expression'][0] \ 

+ '%s ?'%query_dict['expression'][1] 

else: 

self.__query_dict__ = {} 

self.__param_tuple__ = tuple() 

self.__query_string__ = '' 

 

def __repr__(self): 

""" 

Overrides the print output to display useful info regarding the 

query. 

 

EXAMPLES:: 

 

sage: G = GraphDatabase() 

sage: q = 'SELECT graph_id,graph6,num_vertices,num_edges FROM graph_data WHERE graph_id<=(?) AND num_vertices=(?)' 

sage: param = (22,5) 

sage: SQLQuery(G,q,param) 

Query for sql database: ...graphs.db 

Query string: SELECT graph_id,graph6,num_vertices,num_edges FROM 

graph_data WHERE graph_id<=(?) AND num_vertices=(?) 

Parameter tuple: ('22', '5') 

sage: r = 'SELECT graph6 FROM graph_data WHERE num_vertices<=3' 

sage: SQLQuery(G,r) 

Query for sql database: ...graphs.db 

Query string: SELECT graph6 FROM graph_data WHERE num_vertices<=3 

""" 

if not self.__query_string__: 

return 'Empty query on %s.'%self.__database__.__dblocation__ 

return "Query for sql database: %s"%self.__database__.__dblocation__ \ 

+ "\nQuery string: %s"%self.__query_string__ \ 

+ ("\nParameter tuple: %s"%str(self.__param_tuple__) if \ 

self.__param_tuple__ else "") 

 

def get_query_string(self): 

""" 

Returns a copy of the query string. 

 

EXAMPLES:: 

 

sage: G = GraphDatabase() 

sage: q = 'SELECT graph_id,graph6,num_vertices,num_edges FROM graph_data WHERE graph_id<=(?) AND num_vertices=(?)' 

sage: param = (22,5) 

sage: SQLQuery(G,q,param).get_query_string() 

'SELECT graph_id,graph6,num_vertices,num_edges FROM graph_data 

WHERE graph_id<=(?) AND num_vertices=(?)' 

sage: r = 'SELECT graph6 FROM graph_data WHERE num_vertices<=3' 

sage: SQLQuery(G,r).get_query_string() 

'SELECT graph6 FROM graph_data WHERE num_vertices<=3' 

""" 

from copy import copy 

return copy(self.__query_string__) 

 

def __iter__(self): 

""" 

Returns an iterator over the results of the query. 

 

EXAMPLES:: 

 

sage: G = GraphDatabase() 

sage: q = 'SELECT graph_id,graph6 FROM graph_data WHERE num_vertices=(?)' 

sage: param = (5,) 

sage: Q = SQLQuery(G,q,param) 

sage: it = Q.__iter__() 

sage: next(it) 

(18, u'D??') 

sage: next(it) 

(19, u'D?C') 

sage: skip = [next(it) for _ in range(15)] 

sage: next(it) 

(35, u'DBk') 

""" 

try: 

cur = self.__database__.__connection__.cursor() 

return cur.execute(self.__query_string__, self.__param_tuple__) 

except sqlite.OperationalError: 

raise RuntimeError('Failure to fetch query.') 

 

def query_results(self): 

""" 

Runs the query by executing the ``__query_string__``. Returns the 

results of the query in a list. 

 

EXAMPLES:: 

 

sage: G = GraphDatabase() 

sage: q = 'SELECT graph_id,graph6,num_vertices,num_edges FROM graph_data WHERE graph_id<=(?) AND num_vertices=(?)' 

sage: param = (22,5) 

sage: Q = SQLQuery(G,q,param) 

sage: Q.query_results() 

[(18, u'D??', 5, 0), (19, u'D?C', 5, 1), (20, u'D?K', 5, 2), 

(21, u'D@O', 5, 2), (22, u'D?[', 5, 3)] 

sage: R = SQLQuery(G,{'table_name':'graph_data', 'display_cols':['graph6'], 'expression':['num_vertices','=',4]}) 

sage: R.query_results() 

[(u'C?',), (u'C@',), (u'CB',), (u'CK',), (u'CF',), (u'CJ',), 

(u'CL',), (u'CN',), (u'C]',), (u'C^',), (u'C~',)] 

""" 

return list(self) 

 

def show(self, **kwds): 

""" 

Displays the result of the query in table format. 

 

KEYWORDS: 

 

- ``max_field_size`` -- how wide each field can be 

- ``format_cols`` -- a dictionary that allows the user to specify the 

format of a column's output by supplying a function. The format of 

the dictionary is:: 

 

{'column_name':(lambda x: format_function(x))} 

 

- ``plot_cols`` -- a dictionary that allows the user to specify that a 

plot should be drawn by the object generated by a data slice. Note 

that plot kwds are permitted. The dictionary format is:: 

 

{'column_name':((lambda x: plot_function(x)),**kwds)} 

 

- ``relabel_cols`` -- a dictionary to specify a relabeling of column 

headers. The dictionary format is:: 

 

{'table_name':{'old_col_name':'new_col_name'}} 

 

- ``id_col`` -- reference to a column that can be used as an object 

identifier for each row 

 

EXAMPLES:: 

 

sage: DB = SQLDatabase() 

sage: DB.create_table('simon',{'a1':{'sql':'bool', 'primary_key':False}, 'b2':{'sql':'int'}}) 

sage: DB.add_data('simon',[(0,0),(1,1),(1,2)]) 

sage: r = SQLQuery(DB, {'table_name':'simon', 'display_cols':['a1'], 'expression':['b2','<=', 6]}) 

sage: r.show() 

a1 

-------------------- 

0 

1 

1 

sage: D = GraphDatabase() 

sage: from sage.graphs.graph_database import valid_kwds, data_to_degseq 

sage: relabel = {} 

sage: for col in valid_kwds: 

....: relabel[col] = ' '.join([word.capitalize() for word in col.split('_')]) 

sage: q = GraphQuery(display_cols=['graph6','degree_sequence'], num_vertices=4) 

sage: SQLQuery.show(q, format_cols={'degree_sequence':(lambda x,y: data_to_degseq(x,y))}, relabel_cols=relabel, id_col='graph6') 

Graph6 Degree Sequence 

---------------------------------------- 

C? [0, 0, 0, 0] 

C@ [0, 0, 1, 1] 

CB [0, 1, 1, 2] 

CF [1, 1, 1, 3] 

CJ [0, 2, 2, 2] 

CK [1, 1, 1, 1] 

CL [1, 1, 2, 2] 

CN [1, 2, 2, 3] 

C] [2, 2, 2, 2] 

C^ [2, 2, 3, 3] 

C~ [3, 3, 3, 3] 

""" 

if not self.__query_string__: return self.__database__.show() 

 

try: 

cur = self.__database__.__connection__.cursor() 

cur.execute(self.__query_string__, self.__param_tuple__) 

except Exception: 

raise RuntimeError('Failure to fetch query.') 

 

print(_create_print_table(cur, [des[0] for des in cur.description], \ 

**kwds)) 

 

def __copy__(self): 

""" 

Returns a copy of itself. 

 

EXAMPLES:: 

 

sage: G = GraphDatabase() 

sage: Q = SQLQuery(G, {'table_name':'graph_data', 'display_cols':['graph_id','graph6','num_vertices'], 'expression':['num_edges','<',3]}) 

sage: R = copy(Q) 

sage: R.__query_string__ = '' 

sage: Q.__query_string__ == '' 

False 

""" 

d = SQLQuery(self.__database__) 

d.__query_dict__ = self.__query_dict__ 

d.__query_string__ = self.__query_string__ 

d.__param_tuple__ = self.__param_tuple__ 

return d 

 

def intersect(self, other, join_table=None, join_dict=None, \ 

in_place=False): 

""" 

Returns a new ``SQLQuery`` that is the intersection of ``self`` and 

``other``. ``join_table`` and ``join_dict`` can be ``None`` iff the 

two queries only search one table in the database. All display columns 

will be concatenated in order: self display cols + other display cols. 

 

INPUT: 

 

- ``other`` -- the ``SQLQuery`` to intersect with 

- ``join_table`` -- base table to join on (This table should have at 

least one column in each table to join on). 

- ``join_dict`` -- a dictionary that represents the join structure for 

the new query. (Must include a mapping for all tables, including 

those previously joined in either query). Structure is given by:: 

 

{'join_table1':('corr_base_col1', 'col1'), 'join_table2':('corr_base_col2', 'col2')} 

 

where ``join_table1`` is to be joined with ``join_table`` on 

``join_table.corr_base_col1 = join_table1.col1`` 

 

EXAMPLES:: 

 

sage: DB = SQLDatabase() 

sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}}) 

sage: DB.create_table('lucy',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}}) 

sage: DB.add_data('simon', [(0,5),(1,4)]) 

sage: DB.add_data('lucy', [(1,1),(1,4)]) 

sage: q = SQLQuery(DB, {'table_name':'lucy', 'display_cols':['b2'], 'expression':['a1','=',1]}) 

sage: r = SQLQuery(DB, {'table_name':'simon', 'display_cols':['a1'], 'expression':['b2','<=', 6]}) 

sage: s = q.intersect(r, 'simon', {'lucy':('a1','a1')}) 

sage: s.get_query_string() 

'SELECT lucy.b2,simon.a1 FROM simon INNER JOIN lucy ON 

simon.a1=lucy.a1 WHERE ( lucy.a1 = ? ) AND ( simon.b2 <= ? )' 

sage: s.query_results() 

[(1, 1), (4, 1)] 

sage: s = q.intersect(r) 

Traceback (most recent call last): 

... 

ValueError: Input queries query different tables but join 

parameters are NoneType 

sage: s.__query_string__ == q.__query_string__ 

False 

sage: q.intersect(r, 'simon', {'lucy':('a1','a1')}, True) 

sage: q.__query_string__ == s.__query_string__ 

True 

""" 

if self.__query_dict__ is None or other.__query_dict__ is None: 

raise RuntimeError('Queries must be constructed using a ' \ 

+ 'dictionary in order to be intersected.') 

if self.__database__ != other.__database__: 

raise TypeError('Queries %s and %s must be '%(self, other) \ 

+ 'attached to the same database.') 

 

if in_place: 

if not self.__query_string__: 

self.__query_string__ = other.__query_string__ 

self.__param_tuple__ = other.__param_tuple__ 

elif not other.__query_string__: return 

else: 

self._merge_queries(other, self, join_table, join_dict, 'AND') 

else: 

from copy import copy 

if not self.__query_string__: return copy(other) 

if not other.__query_string__: return copy(self) 

return self._merge_queries(other, copy(self), join_table, \ 

join_dict, 'AND') 

 

def _merge_queries(self, other, ret, join_table, join_dict, operator): 

""" 

The query ``ret`` is set to a new ``SQLQuery`` that is combines self 

and other through ``operator``. The other arguments are the same as 

``intersect`` and ``union``. 

 

EXAMPLES:: 

 

sage: DB = SQLDatabase() 

sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}}) 

sage: DB.create_table('lucy',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}}) 

sage: DB.add_data('simon', [(0,5),(1,4)]) 

sage: DB.add_data('lucy', [(1,1),(1,4)]) 

sage: q = SQLQuery(DB, {'table_name':'lucy', 'display_cols':['b2'], 'expression':['a1','=',1]}) 

sage: r = SQLQuery(DB, {'table_name':'simon', 'display_cols':['a1'], 'expression':['b2','<=', 6]}) 

sage: s = q._merge_queries(r, copy(q), 'simon', {'lucy':('a1','a1')}, 'OR') 

sage: s.get_query_string() 

'SELECT lucy.b2,simon.a1 FROM simon INNER JOIN lucy ON 

simon.a1=lucy.a1 WHERE ( lucy.a1 = ? ) OR ( simon.b2 <= ? )' 

sage: s.query_results() 

[(1, 1), (4, 1)] 

""" 

if join_table is None or join_dict is None: 

pattern = ' JOIN ' 

if re.search(pattern, self.__query_string__) \ 

or re.search(pattern, other.__query_string__): 

raise TypeError('Input queries have joins but join ' \ 

+ 'parameters are NoneType') 

s = ((self.__query_string__).upper()).split('FROM ') 

o = ((other.__query_string__).upper()).split('FROM ') 

s = s[1].split(' WHERE ') 

o = o[1].split(' WHERE ') 

if s[0] != o[0]: 

raise ValueError('Input queries query different tables but ' \ 

+ 'join parameters are NoneType') 

 

# inner join clause 

if join_dict is not None: 

joins = join_table 

for table in join_dict: 

joins += ' INNER JOIN %s ON %s.'%(table, join_table) \ 

+ '%s=%s.'%(join_dict[table][0], table) \ 

+ '%s '%join_dict[table][1] 

ret.__query_string__ = re.sub(' FROM .* WHERE ', ' FROM ' + joins \ 

+ 'WHERE ', self.__query_string__) 

 

# concatenate display cols 

disp1 = ret.__query_string__.split(' FROM') 

disp2 = other.__query_string__.split(' FROM') 

disp1.insert(1, ',%s FROM'%disp2[0].split('SELECT ')[1]) 

new_query = ''.join(disp1) 

 

# concatenate where clause 

new_query = re.sub(' WHERE ',' WHERE ( ', new_query) 

new_query += re.sub('^.* WHERE ',' ) %s ( '%operator, \ 

other.__query_string__) 

ret.__query_string__ = new_query + ' )' 

 

ret.__param_tuple__ = self.__param_tuple__ + other.__param_tuple__ 

 

return ret 

 

def union(self, other, join_table=None, join_dict=None, in_place=False): 

""" 

Returns a new ``SQLQuery`` that is the union of self and other. 

``join_table`` and ``join_dict`` can be ``None`` iff the two queries 

only search one table in the database. All display columns will be 

concatenated in order: self display cols + other display cols. 

 

INPUT: 

 

- ``other`` -- the ``SQLQuery`` to union with 

- ``join_table`` -- base table to join on (This table should have at 

least one column in each table to join on). 

- ``join_dict`` -- a dictionary that represents the join structure for 

the new query. (Must include a mapping for all tables, including 

those previously joined in either query). Structure is given by:: 

 

{'join_table1':('corr_base_col1', 'col1'), 'join_table2':('corr_base_col2', 'col2')} 

 

where ``join_table1` is to be joined with ``join_table`` on 

``join_table.corr_base_col1=join_table1.col1`` 

 

EXAMPLES:: 

 

sage: DB = SQLDatabase() 

sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}}) 

sage: DB.create_table('lucy',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}}) 

sage: DB.add_data('simon', [(0,5),(1,4)]) 

sage: DB.add_data('lucy', [(1,1),(1,4)]) 

sage: q = SQLQuery(DB, {'table_name':'lucy', 'display_cols':['b2'], 'expression':['a1','=',1]}) 

sage: r = SQLQuery(DB, {'table_name':'simon', 'display_cols':['a1'], 'expression':['b2','<=', 6]}) 

sage: s = q.union(r, 'simon', {'lucy':('a1','a1')}) 

sage: s.get_query_string() 

'SELECT lucy.b2,simon.a1 FROM simon INNER JOIN lucy ON 

simon.a1=lucy.a1 WHERE ( lucy.a1 = ? ) OR ( simon.b2 <= ? )' 

sage: s.query_results() 

[(1, 1), (4, 1)] 

""" 

if self.__query_dict__ is None or other.__query_dict__ is None: 

raise RuntimeError('Queries must be constructed using a ' \ 

+ 'dictionary in order to be unioned.') 

if self.__database__ != other.__database__: 

raise TypeError('Queries %s and %s must be '%(self, other) \ 

+ 'attached to the same database.') 

 

if in_place: 

if self.__query_string__ and other.__query_string__: 

self._merge_queries(other, self, join_table, join_dict, 'OR') 

else: 

from copy import copy 

if not self.__query_string__: return copy(self) 

if not other.__query_string__: return copy(other) 

return self._merge_queries(other, copy(self), join_table, \ 

join_dict, 'OR') 

 

class SQLDatabase(SageObject): 

def __init__(self, filename=None, read_only=None, skeleton=None): 

r""" 

A SQL Database object corresponding to a database file. 

 

INPUT: 

 

- ``filename`` -- a string 

- ``skeleton`` -- a triple-indexed dictionary:: 

 

| - outer key -- table name 

| - inner key -- column name 

| - inner inner key -- one of the following: 

| - ``primary_key`` -- boolean, whether column has been set 

as primary key 

| - ``index`` -- boolean, whether column has been set as 

index 

| - ``unique`` -- boolean, whether column has been set as 

unique 

| - ``sql`` -- one of ``'TEXT'``, ``'BOOLEAN'``, 

``'INTEGER'``, ``'REAL'``, or other user defined type 

 

TUTORIAL: 

 

The ``SQLDatabase`` class is for interactively building databases 

intended for queries. This may sound redundant, but it is important. If 

you want a database intended for quick lookup of entries in very large 

tables, much like a hash table (such as a Python dictionary), a 

``SQLDatabase`` may not be what you are looking for. The strength of 

``SQLDatabases`` is in queries, searches through the database with 

complicated criteria. 

 

For example, we create a new database for storing isomorphism classes 

of simple graphs:: 

 

sage: D = SQLDatabase() 

 

In order to generate representatives for the classes, we will import a 

function which generates all labeled graphs (noting that this is not 

the optimal way):: 

 

sage: from sage.groups.perm_gps.partn_ref.refinement_graphs import all_labeled_graphs 

 

We will need a table in the database in which to store the graphs, and 

we specify its structure with a Python dictionary, each of whose keys 

is the name of a column:: 

 

sage: table_skeleton = { 

....: 'graph6':{'sql':'TEXT', 'index':True, 'primary_key':True}, 

....: 'vertices':{'sql':'INTEGER'}, 

....: 'edges':{'sql':'INTEGER'} 

....: } 

 

Then we create the table:: 

 

sage: D.create_table('simon', table_skeleton) 

sage: D.show('simon') 

edges graph6 vertices 

------------------------------------------------------------ 

 

Now that we have the table, we will begin to populate the table with 

rows. First, add the graph on zero vertices.:: 

 

sage: G = Graph() 

sage: D.add_row('simon',(0, G.graph6_string(), 0)) 

sage: D.show('simon') 

edges graph6 vertices 

------------------------------------------------------------ 

0 ? 0 

 

Next, add the graph on one vertex.:: 

 

sage: G.add_vertex() 

0 

sage: D.add_row('simon',(0, G.graph6_string(), 1)) 

sage: D.show('simon') 

edges graph6 vertices 

------------------------------------------------------------ 

0 ? 0 

0 @ 1 

 

Say we want a database of graphs on four or less vertices:: 

 

sage: labels = {} 

sage: for i in range(2, 5): 

....: labels[i] = [] 

....: for g in all_labeled_graphs(i): 

....: g = g.canonical_label() 

....: if g not in labels[i]: 

....: labels[i].append(g) 

....: D.add_row('simon', (g.size(), g.graph6_string(), g.order())) 

sage: D.show('simon') # random 

edges graph6 vertices 

------------------------------------------------------------ 

0 ? 0 

0 @ 1 

0 A? 2 

1 A_ 2 

0 B? 3 

1 BG 3 

2 BW 3 

3 Bw 3 

0 C? 4 

1 C@ 4 

2 CB 4 

3 CF 4 

3 CJ 4 

2 CK 4 

3 CL 4 

4 CN 4 

4 C] 4 

5 C^ 4 

6 C~ 4 

 

We can then query the database -- let's ask for all the graphs on four 

vertices with three edges. We do so by creating two queries and asking 

for rows that satisfy them both:: 

 

sage: Q = SQLQuery(D, {'table_name':'simon', 'display_cols':['graph6'], 'expression':['vertices','=',4]}) 

sage: Q2 = SQLQuery(D, {'table_name':'simon', 'display_cols':['graph6'], 'expression':['edges','=',3]}) 

sage: Q = Q.intersect(Q2) 

sage: len(Q.query_results()) 

3 

sage: Q.query_results() # random 

[(u'CF', u'CF'), (u'CJ', u'CJ'), (u'CL', u'CL')] 

 

NOTE: The values of ``display_cols`` are always concatenated in 

intersections and unions. 

 

Of course, we can save the database to file:: 

 

sage: replace_with_your_own_filepath = tmp_dir() 

sage: D.save(replace_with_your_own_filepath + 'simon.db') 

 

Now the database's hard link is to this file, and not the temporary db 

file. For example, let's say we open the same file with another class 

instance. We can load the file as an immutable database:: 

 

sage: E = SQLDatabase(replace_with_your_own_filepath + 'simon.db') 

sage: E.show('simon') # random 

edges graph6 vertices 

------------------------------------------------------------ 

0 ? 0 

0 @ 1 

0 A? 2 

1 A_ 2 

0 B? 3 

1 BG 3 

2 BW 3 

3 Bw 3 

0 C? 4 

1 C@ 4 

2 CB 4 

3 CF 4 

3 CJ 4 

2 CK 4 

3 CL 4 

4 CN 4 

4 C] 4 

5 C^ 4 

6 C~ 4 

sage: E.drop_table('simon') 

Traceback (most recent call last): 

... 

RuntimeError: Cannot drop tables from a read only database. 

""" 

if filename is None: 

if read_only is None: 

read_only = False 

filename = tmp_filename() + '.db' 

elif (filename[-3:] != '.db'): 

raise ValueError('Please enter a valid database path (file name ' \ 

+ '%s does not end in .db).'%filename) 

if read_only is None: 

read_only = True 

 

self.__read_only__ = read_only 

self.ignore_warnings = False 

self.__dblocation__ = filename 

self.__connection__ = sqlite.connect(self.__dblocation__, \ 

check_same_thread=False) 

# this is to avoid the multiple thread problem with dsage: 

# pysqlite does not trust multiple threads for the same connection 

self.__connection__.create_function("regexp", 2, regexp) 

 

# construct skeleton (from provided database) 

self.__skeleton__ = construct_skeleton(self) 

 

# add bones from new skeleton to database, 

# without changing existing structure 

if skeleton is not None and not read_only: 

for table in skeleton: 

if table not in self.__skeleton__: 

self.create_table(table, skeleton[table]) 

else: 

for column in skeleton[table]: 

if column not in self.__skeleton__[table]: 

self.add_column(table, column, \ 

skeleton[table][column]) 

else: 

print('Column attributes were ignored for ' \ 

'table {}, column {} -- column is ' \ 

'already in table.'.format(table, column)) 

elif skeleton is not None: 

raise RuntimeError('Cannot update skeleton of a read only ' \ 

+ 'database.') 

 

def __repr__(self): 

""" 

Overrides the print output to display useful info regarding the 

database. 

 

EXAMPLES:: 

 

sage: replace_with_filepath = tmp_dir() + 'test.db' 

sage: SD = SQLDatabase(replace_with_filepath, False) 

sage: SD.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}}) 

sage: print(SD) 

table simon: 

column n: index: True; unique: False; primary_key: False; 

sql: INTEGER; 

""" 

s = '' 

for table in self.__skeleton__: 

s += 'table ' + table + ':\n' 

for column in self.__skeleton__[table]: 

s += ' column ' + column + ': ' 

for data in self.__skeleton__[table][column]: 

s += data + ': ' \ 

+ str(self.__skeleton__[table][column][data]) + '; ' 

s += '\n' 

return s 

 

def __copy__(self): 

""" 

Returns an instance of ``SQLDatabase`` that points to a copy database, 

and allows modification. 

 

EXAMPLES:: 

 

sage: DB = SQLDatabase() 

sage: DB.create_table('lucy',{'id':{'sql':'INTEGER', 'primary_key':True, 'index':True}, 'a1':{'sql':'bool'}, 'b2':{'sql':'int', 'primary_key':False}}) 

sage: DB.add_rows('lucy', [(0,1,1),(1,1,4),(2,0,7),(3,1,384), (4,1,978932)],['id','a1','b2']) 

sage: d = copy(DB) 

sage: d == DB 

False 

sage: d.show('lucy') 

a1 id b2 

------------------------------------------------------------ 

1 0 1 

1 1 4 

0 2 7 

1 3 384 

1 4 978932 

sage: DB.show('lucy') 

a1 id b2 

------------------------------------------------------------ 

1 0 1 

1 1 4 

0 2 7 

1 3 384 

1 4 978932 

""" 

# copy .db file 

new_loc = tmp_filename() + '.db' 

if not self.__read_only__: 

self.commit() 

os.system('cp '+ self.__dblocation__ + ' ' + new_loc) 

D = SQLDatabase(filename=new_loc, read_only=False) 

return D 

 

def save(self, filename): 

""" 

Save the database to the specified location. 

 

EXAMPLES:: 

 

sage: MonicPolys = SQLDatabase() 

sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}}) 

sage: for n in range(20): MonicPolys.add_row('simon', (n,)) 

sage: tmp = tmp_dir() # replace with your own file path 

sage: MonicPolys.save(tmp+'sage.db') 

sage: N = SQLDatabase(tmp+'sage.db') 

sage: N.show('simon') 

n 

-------------------- 

0 

1 

2 

3 

4 

5 

6 

7 

8 

9 

10 

11 

12 

13 

14 

15 

16 

17 

18 

19 

""" 

if not self.__read_only__: 

self.commit() 

os.system('cp ' + self.__dblocation__ + ' ' + filename) 

 

def get_skeleton(self, check=False): 

""" 

Returns a dictionary representing the hierarchical structure of the 

database, in the following format:: 

 

| - skeleton -- a triple-indexed dictionary 

| - outer key -- table name 

| - inner key -- column name 

| - inner inner key -- one of the following: 

| - ``primary_key`` -- boolean, whether column has been set as 

primary key 

| - ``index`` -- boolean, whether column has been set as index 

| - ``unique`` -- boolean, whether column has been set as unique 

| - ``sql`` -- one of ``'TEXT'``, ``'BOOLEAN'``, ``'INTEGER'``, 

``'REAL'``, or other user defined type 

 

For example:: 

 

{'table1':{'col1':{'primary_key':False, 'index':True, 'unique': False,'sql':'REAL'}}} 

 

INPUT: 

 

- ``check`` -- if True, checks to make sure the database's actual 

structure matches the skeleton on record. 

 

EXAMPLES:: 

 

sage: GDB = GraphDatabase() 

sage: GDB.get_skeleton() # slightly random output 

{u'aut_grp': {u'aut_grp_size': {'index': True, 

'unique': False, 

'primary_key': False, 

'sql': u'INTEGER'}, 

... 

u'num_vertices': {'index': True, 

'unique': False, 

'primary_key': False, 

'sql': u'INTEGER'}}} 

""" 

if check: 

d = construct_skeleton(self) 

if d == self.__skeleton__: 

return d 

else: 

raise RuntimeError("Skeleton structure is out of whack!") 

return self.__skeleton__ 

 

def query(self, *args, **kwds): 

""" 

Creates a ``SQLQuery`` on this database. For full class details, 

type ``SQLQuery?`` and press shift+enter. 

 

EXAMPLES:: 

 

sage: D = SQLDatabase() 

sage: D.create_table('simon', {'wolf':{'sql':'BOOLEAN'}, 'tag':{'sql':'INTEGER'}}) 

sage: q = D.query({'table_name':'simon', 'display_cols':['tag'], 'expression':['wolf','=',1]}) 

sage: q.get_query_string() 

'SELECT simon.tag FROM simon WHERE simon.wolf = ?' 

sage: q.__param_tuple__ 

('1',) 

sage: q = D.query(query_string='SELECT tag FROM simon WHERE wolf=?',param_tuple=(1,)) 

sage: q.get_query_string() 

'SELECT tag FROM simon WHERE wolf=?' 

sage: q.__param_tuple__ 

('1',) 

""" 

return SQLQuery(self, *args, **kwds) 

 

__call__ = query 

 

def show(self, table_name, **kwds): 

""" 

Show an entire table from the database. 

 

EXAMPLES:: 

 

sage: DB = SQLDatabase() 

sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}}) 

sage: DB.add_data('simon',[(0,0),(1,1),(1,2)]) 

sage: DB.show('simon') 

a1 b2 

---------------------------------------- 

0 0 

1 1 

1 2 

""" 

try: 

cur = self.__connection__.cursor() 

cur.execute('SELECT * FROM ' + table_name) 

except Exception: 

raise RuntimeError('Failure to fetch data.') 

print(_create_print_table(cur, [des[0] for des in cur.description], \ 

**kwds)) 

 

def get_cursor(self, ignore_warning=None): 

""" 

Returns a pysqlite cursor for the database connection. 

 

A cursor is an input from which you can execute sqlite commands on the 

database. 

 

Recommended for more advanced users only. 

 

EXAMPLES:: 

 

sage: DB = SQLDatabase() 

sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}}) 

sage: DB.add_row('simon',(0,1)) 

sage: DB.add_rows('simon',[(0,0),(1,1),(1,2)]) 

sage: DB.add_rows('simon',[(0,0),(4,0),(5,1)], ['b2','a1']) 

sage: cur = DB.get_cursor() 

sage: (cur.execute('select * from simon')).fetchall() 

[(0, 1), (0, 0), (1, 1), (1, 2), (0, 0), (0, 4), (1, 5)] 

""" 

if self.__read_only__: 

if ignore_warning is None: 

ignore_warning = self.ignore_warnings 

if not ignore_warning: 

import warnings 

warnings.warn('Database is read only, using the cursor can ' \ 

+ 'alter the stored data. Set self.ignore_warnings to ' \ 

+ 'True in order to mute future warnings.', RuntimeWarning) 

return self.__connection__.cursor() 

 

def get_connection(self, ignore_warning=None): 

""" 

Returns a pysqlite connection to the database. 

 

You most likely want ``get_cursor()`` instead, which is used for 

executing sqlite commands on the database. 

 

Recommended for more advanced users only. 

 

EXAMPLES:: 

 

sage: D = SQLDatabase(read_only=True) 

sage: con = D.get_connection() 

doctest:...: RuntimeWarning: Database is read only, using the connection can alter the stored data. Set self.ignore_warnings to True in order to mute future warnings. 

sage: con = D.get_connection(True) 

sage: D.ignore_warnings = True 

sage: con = D.get_connection() 

sage: t = con.execute('CREATE TABLE simon(n INTEGER, n2 INTEGER)') 

sage: for n in range(10): 

....: t = con.execute('INSERT INTO simon VALUES(%d,%d)'%(n,n^2)) 

sage: D.show('simon') 

n n2 

---------------------------------------- 

0 0 

1 1 

2 4 

3 9 

4 16 

5 25 

6 36 

7 49 

8 64 

9 81 

""" 

if self.__read_only__: 

if ignore_warning is None: 

ignore_warning = self.ignore_warnings 

if not ignore_warning: 

import warnings 

warnings.warn('Database is read only, using the connection ' \ 

+ 'can alter the stored data. Set self.ignore_warnings ' \ 

+ 'to True in order to mute future warnings.', \ 

RuntimeWarning) 

return self.__connection__ 

 

def create_table(self, table_name, table_skeleton): 

""" 

Creates a new table in the database. 

 

To create a table, a column structure must be specified. The form for 

this is a Python dict, for example:: 

 

{'col1': {'sql':'INTEGER', 'index':False, 'unique':True, 'primary_key':False}, ...} 

 

INPUT: 

 

- ``table_name`` -- a string 

- ``table_skeleton`` -- a double-indexed dictionary 

 

- outer key -- column name 

 

- inner key -- one of the following: 

 

- ``primary_key`` -- boolean, whether column has been set 

asprimary key 

- ``index`` -- boolean, whether column has been set as 

index 

- ``unique`` -- boolean, whether column has been set as 

unique 

- ``sql`` -- one of ``'TEXT'``, ``'BOOLEAN'``, 

``'INTEGER'``, ``'REAL'``, or other user defined type 

 

NOTE: 

 

Some SQL features, such as automatically incrementing primary key, 

require the full word ``'INTEGER'``, not just ``'INT'``. 

 

EXAMPLES:: 

 

sage: D = SQLDatabase() 

sage: table_skeleton = { 

....: 'graph6':{'sql':'TEXT', 'index':True, 'primary_key':True}, 

....: 'vertices':{'sql':'INTEGER'}, 

....: 'edges':{'sql':'INTEGER'} 

....: } 

sage: D.create_table('simon', table_skeleton) 

sage: D.show('simon') 

edges graph6 vertices 

------------------------------------------------------------ 

""" 

if self.__read_only__: 

raise RuntimeError('Cannot add table to a read only database.') 

if table_name in self.__skeleton__: 

raise ValueError('Database already has a table named' \ 

+ '%s.'%table_name) 

if table_name.find(' ') != -1: 

raise ValueError('Table names cannot contain spaces.') 

if table_name.upper() in sqlite_keywords: 

raise ValueError('Table names cannot be a SQLite keyword.') 

create_statement = 'CREATE TABLE ' + table_name + '(' 

statement = [] 

index_statement = '' 

for col in table_skeleton: 

if col.find('sqlite') != -1: 

raise ValueError("Column names cannot contain 'sqlite'.") 

if col.upper() in sqlite_keywords: 

raise ValueError('Column names cannot be a SQLite keyword.') 

table_skeleton[col] = verify_column(table_skeleton[col]) 

typ = table_skeleton[col]['sql'] 

if verify_type(typ): 

if typ.upper() == 'NOTYPE': 

typ = '' 

if table_skeleton[col]['primary_key']: 

statement.append(col + ' ' + typ + ' PRIMARY KEY') 

elif table_skeleton[col]['unique']: 

statement.append(col + ' ' + typ + ' UNIQUE') 

else: 

statement.append(col + ' ' + typ) 

if table_skeleton[col]['index']: 

index_statement += 'CREATE INDEX i_%s_%s'%(table_name,\ 

col) + ' ON %s(%s);\n'%(table_name, col) 

create_statement += ', '.join(statement) + ') ' 

 

self.__connection__.execute(create_statement) 

if index_statement: 

self.__connection__.executescript(index_statement) 

 

self.__skeleton__[table_name] = table_skeleton 

 

def add_column(self, table_name, col_name, col_dict, default='NULL'): 

""" 

Add a column named ``col_name`` to table ``table_name``, whose data 

types are described by ``col_dict``. The format for this is:: 

 

{'col1':{'primary_key':False, 'unique': True, 'index':True, 'sql':'REAL'}} 

 

INPUT: 

 

- ``col_dict`` -- a dictionary: 

 

- key -- column name 

 

- inner key -- one of the following: 

 

- ``primary_key`` -- boolean, whether column has been set 

as primary key 

- ``index`` -- boolean, whether column has been set as 

index 

- ``unique`` -- boolean, weather column has been set as 

unique 

- ``sql`` -- one of ``'TEXT'``, ``'BOOLEAN'``, 

``'INTEGER'``, ``'REAL'``, or other user defined type 

 

EXAMPLES:: 

 

sage: MonicPolys = SQLDatabase() 

sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}}) 

sage: for n in range(20): MonicPolys.add_row('simon', (n,)) 

sage: MonicPolys.add_column('simon', 'n_squared', {'sql':'INTEGER', 'index':False}, 0) 

sage: MonicPolys.show('simon') 

n_squared n 

---------------------------------------- 

0 0 

0 1 

0 2 

0 3 

0 4 

0 5 

0 6 

0 7 

0 8 

0 9 

0 10 

0 11 

0 12 

0 13 

0 14 

0 15 

0 16 

0 17 

0 18 

0 19 

""" 

if self.__read_only__: 

raise RuntimeError('Cannot add columns to a read only database.') 

# Check input: 

if col_name.find('sqlite') != -1: 

raise ValueError("Column names cannot contain 'sqlite'.") 

if col_name.upper() in sqlite_keywords: 

raise ValueError("Column names cannot be SQLite keywords.") 

if table_name not in self.__skeleton__: 

raise ValueError("Database has no table %s."%table_name) 

if col_name in self.__skeleton__[table_name]: 

raise ValueError("Table %s already has column %s."%(table_name,col_name)) 

 

# Update the skeleton: 

self.__skeleton__[table_name][col_name] = verify_column(col_dict) 

 

try: 

self._rebuild_table(table_name, col_name, default) 

except sqlite.Error as e: 

# delete added column from skeleton 

self.__skeleton__[table_name].pop(col_name) 

 

print('A sqlite error occurred: ', e.args[0]) 

 

def _rebuild_table(self, table_name, col_name=None, default=''): 

""" 

Rebuilds the table ``table_name`` adding column ``col_name`` if not 

``None``. If a new column is added, each rows' value is set to 

``default``. 

 

Used in the methods ``add_column``, ``drop_column``, ``make_unique``, 

``drop_unique``, ``make_primary_key``, and ``drop_primary_key`` to get 

around the limitations of SQLite's ``ALTER TABLE``. We have set up the 

creation of a temporary database in this method. Please feel free to 

improve on this by sending a patch or suggestion. 

 

EXAMPLES:: 

 

sage: MonicPolys = SQLDatabase() 

sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}}) 

sage: for n in range(20): MonicPolys.add_row('simon', (n,)) 

sage: MonicPolys.show('simon') 

n 

-------------------- 

0 

1 

2 

3 

4 

5 

6 

7 

8 

9 

10 

11 

12 

13 

14 

15 

16 

17 

18 

19 

sage: MonicPolys._rebuild_table('simon') 

sage: MonicPolys.show('simon') 

n 

-------------------- 

0 

1 

2 

3 

4 

5 

6 

7 

8 

9 

10 

11 

12 

13 

14 

15 

16 

17 

18 

19 

""" 

cols = [] 

cols_attr = [] 

table_skeleton = self.__skeleton__[table_name] 

# gather column names and attributes for new table 

for col in table_skeleton: 

cols.append(col) 

attr_str = col + ' ' + table_skeleton[col]['sql'] 

if table_skeleton[col]['primary_key']: 

attr_str += ' PRIMARY KEY' 

elif table_skeleton[col]['unique']: 

attr_str += ' UNIQUE' 

cols_attr.append(attr_str) 

 

original = list(cols) 

 

if col_name is not None: 

original[original.index(col_name)] = str(default) 

 

original = ', '.join(original) 

cols = ', '.join(cols) 

cols_attr = ', '.join(cols_attr) 

 

# Silly SQLite -- we have to make a temp table to hold info... 

self.__connection__.executescript(""" 

CREATE TEMPORARY TABLE spam(%s); 

INSERT INTO spam SELECT %s FROM %s; 

DROP TABLE %s; 

CREATE TABLE %s (%s); 

"""%(cols_attr, original, table_name, table_name, table_name, cols_attr)) 

# Update indices in new table 

index_statement = ''.join(['CREATE INDEX i_%s_%s ON '%(table_name, \ 

col) + '%s(%s);\n'%(table_name, col) for col in \ 

self.__skeleton__[table_name] if \ 

self.__skeleton__[table_name][col]['index'] and not \ 

self.__skeleton__[table_name][col]['primary_key']]) 

if index_statement: self.__connection__.executescript(index_statement) 

 

# Now we can plop our data into the *new* table: 

self.__connection__.executescript(""" 

INSERT INTO %s SELECT %s FROM spam; 

DROP TABLE spam; 

"""%(table_name, cols)) 

 

self.vacuum() 

 

def drop_column(self, table_name, col_name): 

""" 

Drop the column ``col_name`` from table ``table_name``. 

 

EXAMPLES:: 

 

sage: MonicPolys = SQLDatabase() 

sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}}) 

sage: for n in range(20): MonicPolys.add_row('simon', (n,)) 

sage: MonicPolys.add_column('simon', 'n_squared', {'sql':'INTEGER'}, 0) 

sage: MonicPolys.drop_column('simon', 'n_squared') 

sage: MonicPolys.show('simon') 

n 

-------------------- 

0 

1 

2 

3 

4 

5 

6 

7 

8 

9 

10 

11 

12 

13 

14 

15 

16 

17 

18 

19 

""" 

if self.__read_only__: 

raise RuntimeError('Cannot drop columns in a read only database.') 

# Check input: 

if table_name not in self.__skeleton__: 

raise ValueError("Database has no table %s."%table_name) 

if col_name not in self.__skeleton__[table_name]: 

raise ValueError("Table %s has no column %s."%(table_name,col_name)) 

 

# Update the skeleton: 

self.__skeleton__[table_name].pop(col_name) 

 

self._rebuild_table(table_name) 

 

def rename_table(self, table_name, new_name): 

""" 

Renames the table ``table_name`` to ``new_name``. 

 

EXAMPLES:: 

 

sage: D = SQLDatabase() 

sage: D.create_table('simon',{'col1':{'sql':'INTEGER'}}) 

sage: D.show('simon') 

col1 

-------------------- 

sage: D.rename_table('simon', 'lucy') 

sage: D.show('simon') 

Traceback (most recent call last): 

... 

RuntimeError: Failure to fetch data. 

sage: D.show('lucy') 

col1 

-------------------- 

 

""" 

if self.__read_only__: 

raise RuntimeError('Cannot rename tables in a read only database.') 

# Check input: 

if table_name not in self.__skeleton__: 

raise ValueError('Database has no table %s.'%table_name) 

if new_name in self.__skeleton__: 

raise ValueError('Database already has table %s.'%new_name) 

 

self.__connection__.execute('ALTER TABLE %s RENAME TO '%table_name \ 

+ new_name) 

 

# Update skeleton: 

self.__skeleton__[new_name] = self.__skeleton__.pop(table_name) 

 

def drop_table(self, table_name): 

""" 

Delete table ``table_name`` from database. 

 

INPUT: 

 

- ``table_name`` -- a string 

 

EXAMPLES:: 

 

sage: D = SQLDatabase() 

sage: D.create_table('simon',{'col1':{'sql':'INTEGER'}}) 

sage: D.show('simon') 

col1 

-------------------- 

sage: D.drop_table('simon') 

sage: D.get_skeleton() 

{} 

""" 

if self.__read_only__: 

raise RuntimeError('Cannot drop tables from a read only ' \ 

+ 'database.') 

if table_name not in self.__skeleton__: 

raise ValueError("Database has no table %s."%table_name) 

 

self.__connection__.execute('DROP TABLE ' + table_name) 

 

# Update Skeleton 

self.__skeleton__.pop(table_name) 

 

def drop_data_from_table(self, table_name): 

""" 

Removes all rows from ``table_name``. 

 

EXAMPLES:: 

 

sage: D = SQLDatabase() 

sage: D.create_table('simon',{'col1':{'sql':'INTEGER'}}) 

sage: D.add_row('simon',(9,)) 

sage: D.show('simon') 

col1 

-------------------- 

9 

sage: D.drop_data_from_table('simon') 

sage: D.show('simon') 

col1 

-------------------- 

""" 

if self.__read_only__: 

raise RuntimeError('Cannot remove data from a read only database.') 

if table_name not in self.__skeleton__: 

raise ValueError("Database has no table %s."%table_name) 

self.__connection__.execute('DELETE FROM ' + table_name) 

 

def make_index(self, col_name, table_name, unique=False): 

""" 

Set the column ``col_name`` in table ``table_name`` to be an index, 

that is, a column set up to do quick searches on. 

 

INPUT: 

 

- ``col_name`` -- a string 

- ``table_name`` -- a string 

- ``unique`` -- requires that there are no multiple entries in the 

column, makes searching faster 

 

EXAMPLES:: 

 

sage: MonicPolys = SQLDatabase() 

sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}, 'n2':{'sql':'INTEGER'}}) 

sage: MonicPolys.make_index('n2','simon') 

sage: MonicPolys.get_skeleton() 

{'simon': {'n': {'index': True, 

'primary_key': False, 

'sql': 'INTEGER', 

'unique': False}, 

'n2': {'index': True, 

'primary_key': False, 

'sql': 'INTEGER', 

'unique': False}}} 

""" 

if self.__read_only__: 

raise RuntimeError('Cannot modify a read only database.') 

if table_name not in self.__skeleton__: 

raise ValueError("Database has no table %s."%table_name) 

if col_name not in self.__skeleton__[table_name]: 

raise ValueError("Table %s has no column %s."%(table_name,col_name)) 

 

if unique: 

index_string = 'CREATE UNIQUE INDEX ' + col_name + ' ON ' \ 

+ table_name + ' (' + col_name + ')' 

else: 

index_string = 'CREATE INDEX ' + col_name + ' ON ' + table_name \ 

+ ' (' + col_name + ')' 

cur = self.__connection__.cursor() 

exe = cur.execute(index_string) 

 

# Update Skeleton 

self.__skeleton__[table_name][col_name]['index'] = True 

if unique: 

self.__skeleton[table_name][col_name]['unique'] = True 

 

def drop_index(self, table_name, index_name): 

""" 

Set the column ``index_name`` in table ``table_name`` to not be an 

index. See ``make_index()`` 

 

EXAMPLES:: 

 

sage: MonicPolys = SQLDatabase() 

sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}, 'n2':{'sql':'INTEGER'}}) 

sage: MonicPolys.drop_index('simon', 'n') 

sage: MonicPolys.get_skeleton() 

{'simon': {'n': {'index': False, 

'primary_key': False, 

'sql': 'INTEGER', 

'unique': False}, 

'n2': {'index': False, 

'primary_key': False, 

'sql': 'INTEGER', 

'unique': False}}} 

""" 

if self.__read_only__: 

raise RuntimeError('Cannot modify a read only database.') 

if table_name not in self.__skeleton__: 

raise ValueError("Database has no table %s."%table_name) 

if index_name not in self.__skeleton__[table_name]: 

raise ValueError("Table %s has no column %s."%(table,index_name)) 

if not self.__skeleton__[table_name][index_name]['index']: 

return # silently 

 

cur = self.__connection__.cursor() 

exe = cur.execute('DROP INDEX i_' + table_name + '_' + index_name) 

 

# Update Skeleton 

self.__skeleton__[table_name][index_name]['index'] = False 

 

def make_unique(self, table_name, col_name): 

""" 

Set the column ``col_name`` in table ``table_name`` to store unique 

values. 

 

NOTE: 

 

This function only adds the requirement for entries in ``col_name`` to 

be unique, it does not change the values. 

 

EXAMPLES:: 

 

sage: MonicPolys = SQLDatabase() 

sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}, 'n2':{'sql':'INTEGER'}}) 

sage: MonicPolys.make_unique('simon', 'n2') 

sage: MonicPolys.get_skeleton() 

{'simon': {'n': {'index': True, 

'primary_key': False, 

'sql': 'INTEGER', 

'unique': False}, 

'n2': {'index': False, 

'primary_key': False, 

'sql': 'INTEGER', 

'unique': True}}} 

 

""" 

if self.__read_only__: 

raise RuntimeError('Cannot modify a read only database') 

if table_name not in self.__skeleton__: 

raise ValueError("Database has no table %s."%table_name) 

if col_name not in self.__skeleton__[table_name]: 

raise ValueError("Table %s has no column %s."%(table_name, col_name)) 

 

# Update the skeleton: 

self.__skeleton__[table_name][col_name]['unique'] = True 

 

self._rebuild_table(table_name) 

 

def drop_unique(self, table_name, col_name): 

""" 

Set the column ``col_name`` in table ``table_name`` not store unique 

values. 

 

NOTE: 

 

This function only removes the requirement for entries in ``col_name`` 

to be unique, it does not delete it. 

 

EXAMPLES:: 

 

sage: MonicPolys = SQLDatabase() 

sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}, 'n2':{'sql':'INTEGER'}}) 

sage: MonicPolys.make_unique('simon', 'n2') 

sage: MonicPolys.drop_unique('simon', 'n2') 

sage: MonicPolys.get_skeleton() 

{'simon': {'n': {'index': True, 

'primary_key': False, 

'sql': 'INTEGER', 

'unique': False}, 

'n2': {'index': False, 

'primary_key': False, 

'sql': 'INTEGER', 

'unique': False}}} 

""" 

if self.__read_only__: 

raise RuntimeError('Cannot modify a read only database.') 

if table_name not in self.__skeleton__: 

raise ValueError("Database has no table %s."%table_name) 

if col_name not in self.__skeleton__[table_name]: 

raise ValueError("Table %s has no column %s."%(table_name, col_name)) 

if self.__skeleton__[table_name][col_name]['primary_key']: 

raise ValueError("Primary keys must be unique.") 

 

# Update the skeleton: 

self.__skeleton__[table_name][col_name]['unique'] = False 

 

self._rebuild_table(table_name) 

 

def make_primary_key(self, table_name, col_name): 

""" 

Set the column ``col_name`` in table ``table_name`` to be a primary key. 

 

A primary key is something like an index, but its main purpose is to 

link different tables together. This allows searches to be executed on 

multiple tables that represent maybe different data about the same 

objects. 

 

NOTE: 

 

Some SQL features, such as automatically incrementing primary key, 

require the full word ``'INTEGER'``, not just ``'INT'``. 

 

EXAMPLES:: 

 

sage: MonicPolys = SQLDatabase() 

sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}, 'n2':{'sql':'INTEGER'}}) 

sage: MonicPolys.make_primary_key('simon', 'n2') 

sage: MonicPolys.get_skeleton() 

{'simon': {'n': {'index': True, 

'primary_key': False, 

'sql': 'INTEGER', 

'unique': False}, 

'n2': {'index': False, 

'primary_key': True, 

'sql': 'INTEGER', 

'unique': True}}} 

""" 

if self.__read_only__: 

raise RuntimeError('Cannot modify a read only database.') 

if table_name not in self.__skeleton__: 

raise ValueError("Database has no table %s."%table_name) 

if col_name not in self.__skeleton__[table_name]: 

raise ValueError("Table %s has no column %s."%(table_name, col_name)) 

 

# Update the skeleton: 

self.__skeleton__[table_name][col_name]['primary_key'] = True 

self.__skeleton__[table_name][col_name]['unique'] = True 

 

self._rebuild_table(table_name) 

 

def drop_primary_key(self, table_name, col_name): 

""" 

Set the column ``col_name`` in table ``table_name`` not to be a primary 

key. 

 

A primary key is something like an index, but its main purpose is to 

link different tables together. This allows searches to be executed on 

multiple tables that represent maybe different data about the same 

objects. 

 

NOTE: 

 

This function only changes the column to be non-primary, it does not 

delete it. 

 

EXAMPLES:: 

 

sage: MonicPolys = SQLDatabase() 

sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}, 'n2':{'sql':'INTEGER'}}) 

sage: MonicPolys.make_primary_key('simon', 'n2') 

sage: MonicPolys.drop_primary_key('simon', 'n2') 

sage: MonicPolys.get_skeleton() 

{'simon': {'n': {'index': True, 

'primary_key': False, 

'sql': 'INTEGER', 

'unique': False}, 

'n2': {'index': False, 

'primary_key': False, 

'sql': 'INTEGER', 

'unique': True}}} 

""" 

if self.__read_only__: 

raise RuntimeError('Cannot modify a read only database.') 

if table_name not in self.__skeleton__: 

raise ValueError("Database has no table %s."%table_name) 

if col_name not in self.__skeleton__[table_name]: 

raise ValueError("Table %s has no column %s."%(table_name,col_name)) 

if not self.__skeleton__[table_name][col_name]['primary_key']: 

return # silently 

 

# Update the skeleton: 

self.__skeleton__[table_name][col_name]['primary_key'] = False 

 

self._rebuild_table(table_name) 

 

def add_row(self, table_name, values, entry_order=None): 

""" 

Add the row described by ``values`` to the table ``table_name``. Values 

should be a tuple, of same length and order as columns in given table. 

 

NOTE: 

 

If ``values`` is of length one, be sure to specify that it is a tuple of 

length one, by using a comma, e.g.:: 

 

sage: values = (6,) 

 

EXAMPLES:: 

 

sage: DB = SQLDatabase() 

sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}}) 

sage: DB.add_row('simon',(0,1)) 

sage: cur = DB.get_cursor() 

sage: (cur.execute('select * from simon')).fetchall() 

[(0, 1)] 

""" 

self.add_rows(table_name, [values], entry_order) 

 

def delete_rows(self, query): 

""" 

Uses a ``SQLQuery`` instance to modify (delete rows from) the 

database. 

 

``SQLQuery`` must have no join statements. (As of now, you can only 

delete from one table at a time -- ideas and patches are welcome). 

 

To remove all data that satisfies a ``SQLQuery``, send the query as an 

argument to ``delete_rows``. Be careful, test your query first. 

 

Recommended use: have some kind of row identification primary 

key column that you use as a parameter in the query. (See example 

below). 

 

INPUT: 

 

- ``query`` -- a ``SQLQuery`` (Delete the rows returned when query is 

run). 

 

EXAMPLES:: 

 

sage: DB = SQLDatabase() 

sage: DB.create_table('lucy',{'id':{'sql':'INTEGER', 'primary_key':True, 'index':True}, 'a1':{'sql':'bool'}, 'b2':{'sql':'int'}}) 

sage: DB.add_rows('lucy', [(0,1,1),(1,1,4),(2,0,7),(3,1,384), (4,1,978932)],['id','a1','b2']) 

sage: DB.show('lucy') 

a1 id b2 

------------------------------------------------------------ 

1 0 1 

1 1 4 

0 2 7 

1 3 384 

1 4 978932 

sage: Q = SQLQuery(DB, {'table_name':'lucy', 'display_cols':['id','a1','b2'], 'expression':['id','>=',3]}) 

sage: DB.delete_rows(Q) 

sage: DB.show('lucy') 

a1 id b2 

------------------------------------------------------------ 

1 0 1 

1 1 4 

0 2 7 

""" 

if self.__read_only__: 

raise RuntimeError('Cannot delete rows from a read only database.') 

# Check query is associated with this database 

if not isinstance(query, SQLQuery): 

raise TypeError('%s is not a valid SQLQuery'%query) 

if query.__database__ is not self: 

raise ValueError('%s is not associated to this database.'%query) 

if (query.__query_string__).find(' JOIN ') != -1: 

raise ValueError('%s is not a valid query. Can only '%query \ 

+ 'delete from one table at a time.') 

 

delete_statement = re.sub('SELECT .* FROM', 'DELETE FROM', \ 

query.__query_string__) 

 

try: 

cur = self.get_cursor() 

cur.execute(delete_statement, query.__param_tuple__) 

except Exception: 

raise RuntimeError('Failure to complete delete. Check your data.') 

 

def add_rows(self, table_name, rows, entry_order=None): 

""" 

INPUT: 

 

- ``rows`` -- a list of tuples that represent one row of data to add 

(types should match col types in order) 

- ``entry_order`` -- an ordered list or tuple overrides normal order 

with user defined order 

 

EXAMPLES:: 

 

sage: DB = SQLDatabase() 

sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}}) 

sage: DB.add_rows('simon',[(0,0),(1,1),(1,2)]) 

sage: DB.add_rows('simon',[(0,0),(4,0),(5,1)], ['b2','a1']) 

sage: cur = DB.get_cursor() 

sage: (cur.execute('select * from simon')).fetchall() 

[(0, 0), (1, 1), (1, 2), (0, 0), (0, 4), (1, 5)] 

""" 

if self.__read_only__: 

raise RuntimeError('Cannot add rows to read only database.') 

quest = '(' + ', '.join('?' for i in rows[0]) + ')' 

strows = [tuple((str(entry) for entry in row)) for row in rows] 

 

if entry_order is not None: 

self.__connection__.executemany('INSERT INTO ' + table_name \ 

+ str(tuple(entry_order)) + ' VALUES ' + quest, strows) 

else: 

self.__connection__.executemany('INSERT INTO ' + table_name \ 

+ ' VALUES ' + quest, strows) 

 

add_data = add_rows 

 

def vacuum(self): 

""" 

Cleans the extra hard disk space used up by a database that has 

recently shrunk. 

 

EXAMPLES:: 

 

sage: DB = SQLDatabase() 

sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}}) 

sage: DB.add_row('simon',(0,1)) 

sage: DB.add_data('simon',[(0,0),(1,1),(1,2)]) 

sage: DB.add_data('simon',[(0,0),(4,0),(5,1)], ['b2','a1']) 

sage: DB.drop_column('simon','b2') 

sage: DB.commit() 

sage: DB.vacuum() 

""" 

self.__connection__.execute('VACUUM') 

 

def commit(self): 

""" 

Commits changes to file. 

 

EXAMPLES:: 

 

sage: DB = SQLDatabase() 

sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}}) 

sage: DB.add_row('simon',(0,1)) 

sage: DB.add_data('simon',[(0,0),(1,1),(1,2)]) 

sage: DB.add_data('simon',[(0,0),(4,0),(5,1)], ['b2','a1']) 

sage: DB.drop_column('simon','b2') 

sage: DB.commit() 

sage: DB.vacuum() 

""" 

if self.__read_only__: 

raise RuntimeError("Cannot commit read only database.") 

try: 

self.__connection__.execute('COMMIT') 

except sqlite.OperationalError: 

# Not sure why this throws an exception - but without it, 

# the changes are not committed so it is necessary. 

pass