python-gino.org/docs/en/master/tutorials/tutorial.html at master · python-gino/python-gino.org

History

607 lines (561 loc) · 58.1 KB

Raw

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

<!DOCTYPE html>

<head>

<title>GINO Basics - GINO 1.1.0rc1 documentation</title>

<script async

src="https://www.googletagmanager.com/gtag/js?id=UA-3759436-10"></script>

window.dataLayer = window.dataLayer || [];

function gtag () {

dataLayer.push(arguments);

}

gtag('js', new Date());

gtag('config', 'UA-3759436-10');

</script>

href="https://fonts.googleapis.com/css?family=Raleway:400,500,600,700&display=swap">

href="https://cdn.jsdelivr.net/npm/@mdi/font@latest/css/materialdesignicons.min.css">

href="../_static/css/materialize.min.css"

media="screen,projection"/>

type="text/css"/>

href="../_static/css/gino.css"/>

</head>

<body>

<nav>

<div class="img"

style="background-image: url(../_static/logo.svg); width: 103px; height: 40px;"></div>

</a>

<a href="../tutorials.html"

class="breadcrumb">Tutorials</a>

<a class="breadcrumb" style="color: #FFFFFF">GINO Basics</a>

</div>

</div>

HOME

</a>

CREDITS

</a>

<a href="https://github.com/python-gino/gino" target="_blank"

class="github-stars">

<div class="left"><img

src="data:image/svg+xml;base64,PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz4KPHN2ZyB3aWR0aD0iNDNweCIgaGVpZ2h0PSI0MnB4IiB2aWV3Qm94PSIwIDAgNDMgNDIiIHZlcnNpb249IjEuMSIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnIiB4bWxuczp4bGluaz0iaHR0cDovL3d3dy53My5vcmcvMTk5OS94bGluayI+CiAgICA8IS0tIEdlbmVyYXRvcjogU2tldGNoIDYxLjIgKDg5NjUzKSAtIGh0dHBzOi8vc2tldGNoLmNvbSAtLT4KICAgIDx0aXRsZT5GaWxsIDQ5PC90aXRsZT4KICAgIDxkZXNjPkNyZWF0ZWQgd2l0aCBTa2V0Y2guPC9kZXNjPgogICAgPGcgaWQ9IlYzLeacgOaWsOeov+S7tiIgc3Ryb2tlPSJub25lIiBzdHJva2Utd2lkdGg9IjEiIGZpbGw9Im5vbmUiIGZpbGwtcnVsZT0iZXZlbm9kZCI+CiAgICAgICAgPGcgaWQ9IkhPTUUtdjMiIHRyYW5zZm9ybT0idHJhbnNsYXRlKC0xNTA3LjAwMDAwMCwgLTQ5LjAwMDAwMCkiIGZpbGw9IiNGRkZGRkUiPgogICAgICAgICAgICA8ZyBpZD0iYmFubmVyLWJnIiB0cmFuc2Zvcm09InRyYW5zbGF0ZSgtMzc5LjAwMDAwMCwgLTE4Ny4wMDAwMDApIj4KICAgICAgICAgICAgICAgIDxnIGlkPSJoZWFkZXIiIHRyYW5zZm9ybT0idHJhbnNsYXRlKDU5Ny4wMDAwMDAsIDIxOC4wMDAwMDApIj4KICAgICAgICAgICAgICAgICAgICA8cGF0aCBkPSJNMTMxMC40OTgwMiwxOCBDMTI5OC42MjcxMiwxOCAxMjg5LDI3LjYzOTg4MzEgMTI4OSwzOS41MzIxMTIzIEMxMjg5LDQ5LjA0NTEwMjYgMTI5NS4xNTk4Myw1Ny4xMTQ2ODggMTMwMy43MDMzNCw1OS45NjE4NDM5IEMxMzA0Ljc3OTAzLDYwLjE2MDExMzggMTMwNS4xNzEwMyw1OS40OTUyNDg3IDEzMDUuMTcxMDMsNTguOTI0MjMxMyBDMTMwNS4xNzEwMyw1OC40MTI2OTUgMTMwNS4xNTI1NSw1Ny4wNTkxNzI0IDEzMDUuMTQxOTksNTUuMjYyODQ3IEMxMjk5LjE2MTY3LDU2LjU2MzQ5NzYgMTI5Ny44OTk4Nyw1Mi4zNzYwMzcxIDEyOTcuODk5ODcsNTIuMzc2MDM3MSBDMTI5Ni45MjE4NSw0OS44ODg0MTA2IDEyOTUuNTEyMjMsNDkuMjI2MTg5MSAxMjk1LjUxMjIzLDQ5LjIyNjE4OTEgQzEyOTMuNTYwMTUsNDcuODkxMTcxNyAxMjk1LjY2MDA2LDQ3LjkxNzYwNzcgMTI5NS42NjAwNiw0Ny45MTc2MDc3IEMxMjk3LjgxODA0LDQ4LjA2OTYxNDYgMTI5OC45NTMxMyw1MC4xMzY5MDg5IDEyOTguOTUzMTMsNTAuMTM2OTA4OSBDMTMwMC44NzA5LDUzLjQyNjg2NzYgMTMwMy45ODU3OSw1Mi40NzY0OTM4IDEzMDUuMjEwNjMsNTEuOTI1MzAzNSBDMTMwNS40MDU5Nyw1MC41MzQ3NzA1IDEzMDUuOTYxNjMsNDkuNTg1NzE4NiAxMzA2LjU3NTM3LDQ5LjA0Nzc0NjIgQzEzMDEuODAxNDEsNDguNTA0NDg2NiAxMjk2Ljc4MTk1LDQ2LjY1NjYxMTEgMTI5Ni43ODE5NSwzOC40MDU5MzkyIEMxMjk2Ljc4MTk1LDM2LjA1NTc3OTkgMTI5Ny42MjAwNiwzNC4xMzI1NjE3IDEyOTguOTk1MzcsMzIuNjI4MzU0IEMxMjk4Ljc3MzYzLDMyLjA4Mzc3MjYgMTI5OC4wMzU4MiwyOS44OTM1NTEgMTI5OS4yMDY1NCwyNi45MzAwNzY4IEMxMjk5LjIwNjU0LDI2LjkzMDA3NjggMTMwMS4wMTA4LDI2LjM1MTEyODYgMTMwNS4xMTgyNCwyOS4xMzc0ODE4IEMxMzA2LjgzMjc1LDI4LjY1ODk5MDQgMTMwOC42NzI2NCwyOC40MjEwNjY1IDEzMTAuNTAwNjYsMjguNDExODEzOSBDMTMxMi4zMjczNiwyOC40MjEwNjY1IDEzMTQuMTY1OTQsMjguNjU4OTkwNCAxMzE1Ljg4MzA4LDI5LjEzNzQ4MTggQzEzMTkuOTg3ODgsMjYuMzUxMTI4NiAxMzIxLjc4OTUsMjYuOTMwMDc2OCAxMzIxLjc4OTUsMjYuOTMwMDc2OCBDMTMyMi45NjI4NiwyOS44OTM1NTEgMTMyMi4yMjUwNSwzMi4wODM3NzI2IDEzMjIuMDA0NjMsMzIuNjI4MzU0IEMxMzIzLjM4MjU4LDM0LjEzMjU2MTcgMTMyNC4yMTQwOSwzNi4wNTU3Nzk5IDEzMjQuMjE0MDksMzguNDA1OTM5MiBDMTMyNC4yMTQwOSw0Ni42Nzc3NTk5IDEzMTkuMTg2NzIsNDguNDk3ODc3NiAxMzE0LjM5ODIzLDQ5LjAzMDU2MjggQzEzMTUuMTY5MDQsNDkuNjk1NDI3OSAxMzE1Ljg1NjY5LDUxLjAwOTI5NjUgMTMxNS44NTY2OSw1My4wMTcxMDk4IEMxMzE1Ljg1NjY5LDU1Ljg5NTk4ODkgMTMxNS44MzAyOSw1OC4yMTgzOTA1IDEzMTUuODMwMjksNTguOTI0MjMxMyBDMTMxNS44MzAyOSw1OS41MDA1MzU5IDEzMTYuMjE4MzMsNjAuMTcwNjg4MiAxMzE3LjMwODU0LDU5Ljk2MDUyMjEgQzEzMjUuODQ1NDUsNTcuMTA2NzU3MiAxMzMyLDQ5LjA0MjQ1OSAxMzMyLDM5LjUzMjExMjMgQzEzMzIsMjcuNjM5ODgzMSAxMzIyLjM3Mjg4LDE4IDEzMTAuNDk4MDIsMTgiIGlkPSJGaWxsLTQ5Ij48L3BhdGg+CiAgICAgICAgICAgICAgICA8L2c+CiAgICAgICAgICAgIDwvZz4KICAgICAgICA8L2c+CiAgICA8L2c+Cjwvc3ZnPg=="

class="github-logo">

GitHub

</div>

<div class="right"><img

src="data:image/svg+xml;base64,PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz4KPHN2ZyB3aWR0aD0iMjFweCIgaGVpZ2h0PSIxOXB4IiB2aWV3Qm94PSIwIDAgMjEgMTkiIHZlcnNpb249IjEuMSIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnIiB4bWxuczp4bGluaz0iaHR0cDovL3d3dy53My5vcmcvMTk5OS94bGluayI+CiAgICA8IS0tIEdlbmVyYXRvcjogU2tldGNoIDYxLjIgKDg5NjUzKSAtIGh0dHBzOi8vc2tldGNoLmNvbSAtLT4KICAgIDx0aXRsZT5QYXRoPC90aXRsZT4KICAgIDxkZXNjPkNyZWF0ZWQgd2l0aCBTa2V0Y2guPC9kZXNjPgogICAgPGcgaWQ9IlYzLeacgOaWsOeov+S7tiIgc3Ryb2tlPSJub25lIiBzdHJva2Utd2lkdGg9IjEiIGZpbGw9Im5vbmUiIGZpbGwtcnVsZT0iZXZlbm9kZCI+CiAgICAgICAgPGcgaWQ9IkhPTUUtdjMiIHRyYW5zZm9ybT0idHJhbnNsYXRlKC0xNTYxLjAwMDAwMCwgLTYwLjAwMDAwMCkiIGZpbGw9IiNGOEQyMzAiPgogICAgICAgICAgICA8ZyBpZD0iYmFubmVyLWJnIiB0cmFuc2Zvcm09InRyYW5zbGF0ZSgtMzc5LjAwMDAwMCwgLTE4Ny4wMDAwMDApIj4KICAgICAgICAgICAgICAgIDxnIGlkPSJoZWFkZXIiIHRyYW5zZm9ybT0idHJhbnNsYXRlKDU5Ny4wMDAwMDAsIDIxOC4wMDAwMDApIj4KICAgICAgICAgICAgICAgICAgICA8ZyBpZD0ibGFiZWwtY29weS0xMiIgdHJhbnNmb3JtPSJ0cmFuc2xhdGUoMTI4OS4wMDAwMDAsIDE4LjAwMDAwMCkiPgogICAgICAgICAgICAgICAgICAgICAgICA8cG9seWdvbiBpZD0iUGF0aCIgcG9pbnRzPSI2MS4wNTQzNTQzIDE3Ljc1NzM4MzYgNTQuMjE1MjQ2NiAxOC4zMTMzOTI5IDU5Ljg4OTM1MTcgMjIuOTczNzM4NyA1Ny43ODQ1MjI4IDI5Ljg0MDExODQgNjQuNDQyNTI3MSAyNi41NDM0MTg4IDcwLjU3NzcyMzkgMjkuODQwMTE4NCA2OS4yMzA0MzI1IDIyLjk3MzczODcgNzQuNzMyNTY5NiAxOC4zMTMzOTI5IDY3Ljg5MzQ2MiAxNy43NTczODM2IDY0LjQ0MjUyNzEgMTEuMzc1Ij48L3BvbHlnb24+CiAgICAgICAgICAgICAgICAgICAgPC9nPgogICAgICAgICAgICAgICAgPC9nPgogICAgICAgICAgICA8L2c+CiAgICAgICAgPC9nPgogICAgPC9nPgo8L3N2Zz4=">

</div>

</a>

</div>

</nav>

</div>

Tutorials

<li class="toctree-l1 current"><a class="reference internal" href="../tutorials.html">Tutorials</a><ul class="current">

<li class="toctree-l2"><a class="reference internal" href="announcement.html">官宣：Python 异步编程再添一利器</a></li>

<li class="toctree-l2 current"><a class="current reference internal" href="#">GINO Basics</a></li>

<li class="toctree-l2"><a class="reference internal" href="fastapi.html">Build a FastAPI Server</a></li>

</ul>

</li>

<li class="toctree-l1"><a class="reference internal" href="announcement.html">官宣：Python 异步编程再添一利器</a></li>

<li class="toctree-l1 current"><a class="current reference internal" href="#">GINO Basics</a></li>

<li class="toctree-l1"><a class="reference internal" href="fastapi.html">Build a FastAPI Server</a></li>

</ul>

How-to Guides

<ul>

<li class="toctree-l1"><a class="reference internal" href="../how-to.html">How-to Guides</a></li>

<li class="toctree-l1"><a class="reference internal" href="../how-to/alembic.html">Use Alembic</a></li>

<li class="toctree-l1"><a class="reference internal" href="../how-to/bakery.html">Bake Queries</a></li>

<li class="toctree-l1"><a class="reference internal" href="../how-to/contributing.html">Contributing</a></li>

<li class="toctree-l1"><a class="reference internal" href="../how-to/faq.html">Frequently Asked Questions</a></li>

<li class="toctree-l1"><a class="reference internal" href="../how-to/json-props.html">JSON Property</a></li>

<li class="toctree-l1"><a class="reference internal" href="../how-to/loaders.html">Loaders and Relationship</a></li>

<li class="toctree-l1"><a class="reference internal" href="../how-to/pool.html">Connection Pool</a></li>

<li class="toctree-l1"><a class="reference internal" href="../how-to/schema.html">Schema Declaration</a></li>

<li class="toctree-l1"><a class="reference internal" href="../how-to/transaction.html">Transaction</a></li>

</ul>

Explanation

<ul>

<li class="toctree-l1"><a class="reference internal" href="../explanation.html">Explanation</a></li>

<li class="toctree-l1"><a class="reference internal" href="../explanation/async.html">Asynchronous Programming 101</a></li>

<li class="toctree-l1"><a class="reference internal" href="../explanation/engine.html">Engine and Connection</a></li>

<li class="toctree-l1"><a class="reference internal" href="../explanation/sa20.html">SQLAlchemy 2.0</a></li>

<li class="toctree-l1"><a class="reference internal" href="../explanation/why.html">Why Asynchronous ORM?</a></li>

</ul>

Reference

<ul>

<li class="toctree-l1"><a class="reference internal" href="../reference.html">Reference</a></li>

<li class="toctree-l1"><a class="reference internal" href="../reference/api.html">API Reference</a></li>

<li class="toctree-l1"><a class="reference internal" href="../reference/extensions.html">Extensions</a></li>

<li class="toctree-l1"><a class="reference internal" href="../reference/history.html">History</a></li>

</ul>

</div>

</header>

<main>

<h1>GINO Basics<a class="headerlink" href="#gino-basics" title="Permalink to this headline">¶</a></h1>

This tutorial helps beginners to get started with the basic part of GINO.

Target audiences of this tutorial should have basic knowledge of:

<li>RDBMS, especially <a class="reference external" href="https://www.postgresql.org/">PostgreSQL</a></li>

<li><a class="reference external" href="https://realpython.com/async-io-python/">Asynchronous programming in Python</a></li>

</ul>

Knowledge of <a class="reference external" href="https://www.sqlalchemy.org/">SQLAlchemy</a> is not required.

<h2>Introduction<a class="headerlink" href="#introduction" title="Permalink to this headline">¶</a></h2>

Simply speaking, GINO helps you write and execute raw SQL in your asynchronous

application. Instead of interacting RDBMS directly with raw SQL, you can access

your data through friendly objective API.

You may not need GINO, or else to say asynchronous database connection, because

it adds quite some complexity and risk to your stack, and it won’t make your

code run faster, if not slower. Please read <a class="reference internal" href="../explanation/why.html">Why Asynchronous ORM?</a> for more

information.

</section>

<h2>Installation<a class="headerlink" href="#installation" title="Permalink to this headline">¶</a></h2>

To install GINO, run this command in your terminal:

<div class="highlight-console notranslate"><div class="highlight"><pre>$ pip install gino

</pre></div>

</div>

This is the preferred method to install GINO, as it will always install the

most recent stable release.

If you don’t have <a class="reference external" href="https://pip.pypa.io">pip</a> installed, this <a class="reference external" href="http://docs.python-guide.org/en/latest/starting/installation/">Python installation guide</a> can guide

you through the process.

Alternatively, if you are using <a class="reference external" href="https://python-poetry.org">Poetry</a> to manage your project dependencies,

you may want to run:

<div class="highlight-console notranslate"><div class="highlight"><pre>$ poetry add gino

</pre></div>

</div>

</section>

<h2>Declare Models<a class="headerlink" href="#declare-models" title="Permalink to this headline">¶</a></h2>

First of all, we’ll need a <a class="reference internal" href="../reference/api/gino.api.html#gino.api.Gino" title="gino.api.Gino"><code class="xref py py-class docutils literal notranslate">Gino</code></a> object, usually under the

name of <code class="docutils literal notranslate">db</code> as a global variable:

<div class="highlight-default notranslate"><div class="highlight"><pre>from gino import Gino

db = Gino()

</pre></div>

</div>

<code class="docutils literal notranslate">db</code> acts like a reference to the database, most database interactions will

go through it.

“Model” is a basic concept in GINO, it is a Python class inherited from

<a class="reference internal" href="../reference/api/gino.api.html#gino.api.Gino.Model" title="gino.api.Gino.Model"><code class="xref py py-attr docutils literal notranslate">db.Model</code></a>. Each <a class="reference internal" href="../reference/api/gino.declarative.html#gino.declarative.Model" title="gino.declarative.Model"><code class="xref py py-class docutils literal notranslate">Model</code></a>

subclass maps to one table in the database, while each object of the class

represents one row in the table. This must feel familiar if ORM is not a

strange word to you. Now let’s declare a model:

<div class="highlight-default notranslate"><div class="highlight"><pre>class User(db.Model):

__tablename__ = 'users'

id = db.Column(db.Integer(), primary_key=True)

nickname = db.Column(db.Unicode(), default='noname')

</pre></div>

</div>

By declaring this <code class="docutils literal notranslate">User</code> class, we are actually defining a database table

named <code class="docutils literal notranslate">users</code>, with two columns <code class="docutils literal notranslate">id</code> and <code class="docutils literal notranslate">nickname</code>. Note that the fixed

<code class="xref py py-attr docutils literal notranslate">__tablename__</code> property is required. GINO

suggests singular for model names, and plural for table names. Each

<a class="reference external" href="https://docs.sqlalchemy.org/en/14/core/metadata.html#sqlalchemy.schema.Column" title="(in SQLAlchemy v1.4)"><code class="xref py py-class docutils literal notranslate">db.Column</code></a> property defines one column for

the table, where its first parameter indicates the column type in database,

while the rest is for other column attributes or constraints. You can find a

mapping of database types to <code class="docutils literal notranslate">db</code> types <a class="reference external" href="http://docs.sqlalchemy.org/en/latest/core/type_basics.html">here</a> in the SQLAlchemy

documentation.

Note

<a class="reference external" href="https://www.sqlalchemy.org/">SQLAlchemy</a> is a powerful ORM library for non-asynchronous programming in

Python, on top of which GINO is built. SQLAlchemy supports many popular

RDBMS including PostgreSQL and MySQL through different dialect

implementation, so that the same Python code can be compiled into different

SQL depending on the dialect you choose. GINO inherited this support too,

but for now there is only one dialect for PostgreSQL through <a class="reference external" href="https://github.com/MagicStack/asyncpg">asyncpg</a>.

</div>

If you need constraints or indexes covering multiple columns these are also

defined using properties in model classes. The property names must be unique,

but are otherwise not used. Example:

<div class="highlight-default notranslate"><div class="highlight"><pre>class Booking(db.Model):

__tablename__ = 'bookings'

day = db.Column(db.Date)

booker = db.Column(db.String)

room = db.Column(db.String)

_pk = db.PrimaryKeyConstraint('day', 'booker', name='bookings_pkey')

_idx1 = db.Index('bookings_idx_day_room', 'day', 'room', unique=True)

_idx2 = db.Index('bookings_idx_booker_room', 'booker', 'room')

</pre></div>

</div>

It is also possible to define model constraints and indexes outside the model

class if that is preferred. For more details on constraints and indexes, see

<a class="reference external" href="http://docs.sqlalchemy.org/en/latest/core/constraints.html">here</a> in the

SQLAlchemy documentation.

Due to implementation limitations it is currently not allowed to specify

explicit constraints and indexes as direct attributes in classes that are meant

to be subclassed. The same is true for constraints and indexes specified

through the <code class="xref py py-attr docutils literal notranslate">__table_args__</code> attribute. In order

to e.g. define constraints in mixin classes,

<a class="reference internal" href="../reference/api/gino.declarative.html#gino.declarative.declared_attr" title="gino.declarative.declared_attr"><code class="xref py py-func docutils literal notranslate">declared_attr()</code></a> is required. Please feel free to read

more about it in its API documentation.

</section>

<h2>Get Connected<a class="headerlink" href="#get-connected" title="Permalink to this headline">¶</a></h2>

The declaration only defined the mapping, it does not create the actual table

in the database. To do that, we need to get connected first. Let’s create a

PostgreSQL database for this tutorial:

<div class="highlight-console notranslate"><div class="highlight"><pre>$ createdb gino

</pre></div>

</div>

Then we tell our <code class="docutils literal notranslate">db</code> object to connect to this database:

<div class="highlight-default notranslate"><div class="highlight"><pre>import asyncio

async def main():

await db.set_bind('postgresql://localhost/gino')

asyncio.get_event_loop().run_until_complete(main())

</pre></div>

</div>

If this runs successfully, then you are connected to the newly created database.

Here <code class="docutils literal notranslate">postgresql</code> indicates the database dialect to use (the default driver

is <code class="docutils literal notranslate">asyncpg</code>, you can explicitly specify that with <code class="docutils literal notranslate">postgresql+asyncpg://</code>,

or simply <code class="docutils literal notranslate">asyncpg://</code>), <code class="docutils literal notranslate">localhost</code> is where the server is, and <code class="docutils literal notranslate">gino</code>

is the name of the database. Check <a class="reference external" href="https://docs.sqlalchemy.org/en/latest/core/engines.html">here</a> for more

information about how to compose this database URL.

Note

Under the hood <a class="reference internal" href="../reference/api/gino.api.html#gino.api.Gino.set_bind" title="gino.api.Gino.set_bind"><code class="xref py py-meth docutils literal notranslate">set_bind()</code></a> calls

<a class="reference internal" href="../reference/api/gino.html#gino.create_engine" title="gino.create_engine"><code class="xref py py-func docutils literal notranslate">create_engine()</code></a> and bind the engine to this <code class="docutils literal notranslate">db</code> object. GINO

engine is similar to SQLAlchemy engine, but not identical. Because GINO

engine is asynchronous, while the other is not. Please refer to the API

reference of GINO for more information.

</div>

Now that we are connected, let’s create the table in database (in the same

<code class="docutils literal notranslate">main()</code> method):

<div class="highlight-default notranslate"><div class="highlight"><pre>await db.gino.create_all()

</pre></div>

</div>

Warning

It is <a class="reference internal" href="../reference/api/gino.schema.html#gino.schema.GinoSchemaVisitor.create_all" title="gino.schema.GinoSchemaVisitor.create_all"><code class="xref py py-meth docutils literal notranslate">db.gino.create_all</code></a>,

not <a class="reference external" href="https://docs.sqlalchemy.org/en/14/core/metadata.html#sqlalchemy.schema.MetaData.create_all" title="(in SQLAlchemy v1.4)"><code class="xref py py-meth docutils literal notranslate">db.create_all</code></a>, because

<code class="docutils literal notranslate">db</code> is inherited from SQLAlchemy <a class="reference external" href="https://docs.sqlalchemy.org/en/14/core/metadata.html#sqlalchemy.schema.MetaData" title="(in SQLAlchemy v1.4)"><code class="xref py py-class docutils literal notranslate">MetaData</code></a>,

and <a class="reference external" href="https://docs.sqlalchemy.org/en/14/core/metadata.html#sqlalchemy.schema.MetaData.create_all" title="(in SQLAlchemy v1.4)"><code class="xref py py-meth docutils literal notranslate">db.create_all</code></a> is from

SQLAlchemy using non-asynchronous methods, which doesn’t work with the

bound GINO engine.

In practice <a class="reference internal" href="../reference/api/gino.schema.html#gino.schema.GinoSchemaVisitor.create_all" title="gino.schema.GinoSchemaVisitor.create_all"><code class="xref py py-meth docutils literal notranslate">create_all()</code></a> is usually

not an ideal solution. To manage database schema, tool like <a class="reference external" href="https://bitbucket.org/zzzeek/alembic">Alembic</a> is

recommended, please see how to <a class="reference internal" href="../how-to/alembic.html">Use Alembic</a>.

</div>

If you want to explicitly disconnect from the database, you can do this:

<div class="highlight-default notranslate"><div class="highlight"><pre>await db.pop_bind().close()

</pre></div>

</div>

Let’s review the code we have so far together in one piece before moving on:

<div class="highlight-default notranslate"><div class="highlight"><pre>import asyncio

from gino import Gino

db = Gino()

class User(db.Model):

__tablename__ = 'users'

async def main():

await db.gino.create_all()

# further code goes here

await db.pop_bind().close()

</pre></div>

</div>

</section>

<h2>CRUD Operations<a class="headerlink" href="#crud-operations" title="Permalink to this headline">¶</a></h2>

In order to operate on the database, one of GINO’s core features is to Create,

Retrieve, Update or Delete model objects, also known as the CRUD operations.

<h3>Create<a class="headerlink" href="#create" title="Permalink to this headline">¶</a></h3>

Let’s start by creating a <code class="docutils literal notranslate">User</code>:

# This will cause GINO to execute this SQL with parameter 'fantix':

# INSERT INTO users (nickname) VALUES ($1) RETURNING users.id, users.nickname

</pre></div>

</div>

As mentioned previously, <code class="docutils literal notranslate">user</code> object represents the newly created row in

the database. You can get the value of each columns by the declared column

properties on the object:

<div class="highlight-default notranslate"><div class="highlight"><pre>print(f'ID: {user.id}') # 1

print(f'Nickname: {user.nickname}') # fantix

</pre></div>

</div>

It is also possible to create a model instance in-memory first, modify it, then

finally create it in the database:

<div class="highlight-default notranslate"><div class="highlight"><pre>user = User(nickname='fantix')

user.nickname += ' (founder)'

await user.create()

</pre></div>

</div>

</section>

<h3>Retrieve<a class="headerlink" href="#retrieve" title="Permalink to this headline">¶</a></h3>

To retrieve a model object from database by primary key, you can use the class

method <a class="reference internal" href="../reference/api/gino.crud.html#gino.crud.CRUDModel.get" title="gino.crud.CRUDModel.get"><code class="xref py py-meth docutils literal notranslate">get()</code></a> on the model class. Now let’s retrieve

the same row:

# SQL (parameter: 1):

# SELECT users.id, users.nickname FROM users WHERE users.id = $1

</pre></div>

</div>

Normal SQL queries are done through a class property

<a class="reference internal" href="../reference/api/gino.crud.html#gino.crud.CRUDModel.query" title="gino.crud.CRUDModel.query"><code class="xref py py-attr docutils literal notranslate">query</code></a>. For example, let’s retrieve all <code class="docutils literal notranslate">User</code>

objects from database as a list:

<div class="highlight-default notranslate"><div class="highlight"><pre>all_users = await db.all(User.query)

# SQL:

# SELECT users.id, users.nickname FROM users

</pre></div>

</div>

Alternatively, you can use the <code class="docutils literal notranslate">gino</code> extension on

<div class="highlight-default notranslate"><div class="highlight"><pre>all_users = await User.query.gino.all()

# SQL:

# SELECT users.id, users.nickname FROM users

</pre></div>

</div>

Note

<code class="docutils literal notranslate">User.query</code> is actually a SQLAlchemy query, with its own

non-asynchronous execution methods. GINO added this <code class="docutils literal notranslate">gino</code> extension on

all executable SQLAlchemy clause objects to conveniently execute them in

the asynchronous way, so that it is even not needed to import the <code class="docutils literal notranslate">db</code>

reference for execution.

</div>

Now let’s add some filters. For example, find all users with ID lower than 10:

<div class="highlight-default notranslate"><div class="highlight"><pre>founding_users = await User.query.where(User.id < 10).gino.all()

# SQL (parameter: 10):

# SELECT users.id, users.nickname FROM users WHERE users.id < $1

</pre></div>

</div>

Read more <a class="reference external" href="https://docs.sqlalchemy.org/en/latest/core/expression_api.html">here</a>

about writing queries, because the query object is exactly from SQLAlchemy core.

Warning

Once you get a model object, it is purely in memory and fully detached from

the database. That means, if the row is externally updated, the object

values remain unchanged. Likewise, changes made to the object won’t affect

the database values.

Also, GINO keeps no track of model objects, therefore getting the same row

twice returns two different object with identical values. Modifying one

does not magically affect the other one.

Different than traditional ORMs, the GINO model objects are more like

objective SQL results, rather than stateful ORM objects. In order to adapt

for asynchronous programming, GINO is designed to be that simple. That’s

also why GINO Is Not ORM.

</div>

Sometimes we want to get only one object, for example getting the user by name

when logging in. There’s a shortcut for this scenario:

<div class="highlight-default notranslate"><div class="highlight"><pre>user = await User.query.where(User.nickname == 'fantix').gino.first()

# SQL (parameter: 'fantix'):

# SELECT users.id, users.nickname FROM users WHERE users.nickname = $1

</pre></div>

</div>

If there is no user named “fantix” in database, <code class="docutils literal notranslate">user</code> will be <code class="docutils literal notranslate">None</code>.

And sometimes we may want to get a single value from database, getting the name

of user with ID 1 for example. Then we can use the

<a class="reference internal" href="../reference/api/gino.crud.html#gino.crud.CRUDModel.select" title="gino.crud.CRUDModel.select"><code class="xref py py-meth docutils literal notranslate">select()</code></a> class method:

<div class="highlight-default notranslate"><div class="highlight"><pre>name = await User.select('nickname').where(User.id == 1).gino.scalar()

# SQL (parameter: 1):

# SELECT users.nickname FROM users WHERE users.id = $1

print(name) # fantix

</pre></div>

</div>

Or get the count of all users:

<div class="highlight-default notranslate"><div class="highlight"><pre>population = await db.func.count(User.id).gino.scalar()

# SQL:

# SELECT count(users.id) AS count_1 FROM users

print(population) # 17 for example

</pre></div>

</div>

</section>

<h3>Update<a class="headerlink" href="#update" title="Permalink to this headline">¶</a></h3>

Then let’s try to make some modifications. In this example we’ll mixin some

retrieve operations we just tried.

<div class="highlight-default notranslate"><div class="highlight"><pre># create a new user

user = await User.create(nickname='fantix')

# get its name

name = await User.select('nickname').where(

User.id == user.id).gino.scalar()

assert name == user.nickname # they are both 'fantix' before the update

# modification here

await user.update(nickname='daisy').apply()

# SQL (parameters: 'daisy', 1):

# UPDATE users SET nickname=$1 WHERE users.id = $2 RETURNING users.nickname

print(user.nickname) # daisy

# get its name again

print(name) # daisy

</pre></div>

</div>

So <a class="reference internal" href="../reference/api/gino.crud.html#gino.crud.CRUDModel.update" title="gino.crud.CRUDModel.update"><code class="xref py py-meth docutils literal notranslate">update()</code></a> is the first GINO method we met so far

on model instance level. It accepts multiple keyword arguments, whose keys are

column names while values are the new value to update to. The following

Note

GINO explicitly split the in-memory update and SQL update into two methods:

<a class="reference internal" href="../reference/api/gino.crud.html#gino.crud.CRUDModel.update" title="gino.crud.CRUDModel.update"><code class="xref py py-meth docutils literal notranslate">update()</code></a> and

<a class="reference internal" href="../reference/api/gino.crud.html#gino.crud.UpdateRequest.apply" title="gino.crud.UpdateRequest.apply"><code class="xref py py-meth docutils literal notranslate">apply()</code></a>. <a class="reference internal" href="../reference/api/gino.crud.html#gino.crud.CRUDModel.update" title="gino.crud.CRUDModel.update"><code class="xref py py-meth docutils literal notranslate">update()</code></a>

will update the in-memory model object and return an

<a class="reference internal" href="../reference/api/gino.crud.html#gino.crud.UpdateRequest" title="gino.crud.UpdateRequest"><code class="xref py py-class docutils literal notranslate">UpdateRequest</code></a> object which contains all the

modifications. A following <a class="reference internal" href="../reference/api/gino.crud.html#gino.crud.UpdateRequest.apply" title="gino.crud.UpdateRequest.apply"><code class="xref py py-meth docutils literal notranslate">apply()</code></a> on

modifications to database by executing a compiled SQL.

</div>

Tip

<a class="reference internal" href="../reference/api/gino.crud.html#gino.crud.UpdateRequest" title="gino.crud.UpdateRequest"><code class="xref py py-class docutils literal notranslate">UpdateRequest</code></a> object has another method named

<a class="reference internal" href="../reference/api/gino.crud.html#gino.crud.UpdateRequest.update" title="gino.crud.UpdateRequest.update"><code class="xref py py-meth docutils literal notranslate">update()</code></a> which works the same as the one

on model object, just that it combines the new modifications together with

the ones already recorded in current <a class="reference internal" href="../reference/api/gino.crud.html#gino.crud.UpdateRequest" title="gino.crud.UpdateRequest"><code class="xref py py-class docutils literal notranslate">UpdateRequest</code></a>

object, and it returns the same <a class="reference internal" href="../reference/api/gino.crud.html#gino.crud.UpdateRequest" title="gino.crud.UpdateRequest"><code class="xref py py-class docutils literal notranslate">UpdateRequest</code></a> object.

That means, you can chain the updates and end up with one

batch.

</div>

<a class="reference internal" href="../reference/api/gino.crud.html#gino.crud.CRUDModel.update" title="gino.crud.CRUDModel.update"><code class="xref py py-meth docutils literal notranslate">update()</code></a> on model object affects only the row

represented by the object. If you want to do update with wider condition, you

can use the <a class="reference internal" href="../reference/api/gino.crud.html#gino.crud.CRUDModel.update" title="gino.crud.CRUDModel.update"><code class="xref py py-meth docutils literal notranslate">update()</code></a> on model class level, with a

bit difference:

<div class="highlight-default notranslate"><div class="highlight"><pre>await User.update.values(nickname='Founding Member ' + User.nickname).where(

User.id < 10).gino.status()

# SQL (parameter: 'Founding Member ', 10):

# UPDATE users SET nickname=($1 || users.nickname) WHERE users.id < $2

User.id == 1).gino.scalar()

print(name) # Founding Member fantix

</pre></div>

</div>

There is no <a class="reference internal" href="../reference/api/gino.crud.html#gino.crud.UpdateRequest" title="gino.crud.UpdateRequest"><code class="xref py py-class docutils literal notranslate">UpdateRequest</code></a> here, everything is again

SQLAlchemy clause, its

<a class="reference external" href="https://docs.sqlalchemy.org/en/latest/core/dml.html">documentation</a> here for

your reference.

</section>

<h3>Delete<a class="headerlink" href="#delete" title="Permalink to this headline">¶</a></h3>

At last. Deleting is similar to updating, but way simpler.

await user.delete()

# SQL (parameter: 1):

# DELETE FROM users WHERE users.id = $1

print(await User.get(user.id)) # None

</pre></div>

</div>

Hint

Remember the model object is in memory? In the last <a class="reference external" href="https://docs.python.org/3/library/functions.html#print" title="(in Python v3.10)"><code class="xref py py-func docutils literal notranslate">print()</code></a>

statement, even though the row is already deleted in database, the object

<code class="docutils literal notranslate">user</code> still exists with its values untouched.

</div>

Or mass deletion (never forget the where clause, unless you want to truncate

the whole table!!):

<div class="highlight-default notranslate"><div class="highlight"><pre>await User.delete.where(User.id > 10).gino.status()

# SQL (parameter: 10):

# DELETE FROM users WHERE users.id > $1

</pre></div>

</div>

With basic <a class="reference internal" href="../how-to/crud.html">CRUD</a>, you can already make some amazing stuff with

GINO. This tutorial ends here, please find out more in detail from the rest of

this documentation, and have fun hacking!

</section>

</div>

<img

alt="Creative Commons License"

style="border-width:0"

src="https://i.creativecommons.org/l/by-sa/4.0/80x15.png"

</a>

This work is licensed under a<a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/"> Creative Commons Attribution-ShareAlike 4.0 International License</a>.

</div>

<ul>

<li><a class="reference internal" href="#">GINO Basics</a><ul>

<li><a class="reference internal" href="#introduction">Introduction</a></li>

<li><a class="reference internal" href="#installation">Installation</a></li>

<li><a class="reference internal" href="#declare-models">Declare Models</a></li>

<li><a class="reference internal" href="#get-connected">Get Connected</a></li>

<li><a class="reference internal" href="#crud-operations">CRUD Operations</a><ul>

<li><a class="reference internal" href="#create">Create</a></li>

<li><a class="reference internal" href="#retrieve">Retrieve</a></li>

<li><a class="reference internal" href="#update">Update</a></li>

<li><a class="reference internal" href="#delete">Delete</a></li>

</ul>

</li>

</ul>

</li>

</ul>

<hr>

You're on the master branch. This documentation may include

functionality that is not released yet.

</div>

<a href="https://www.transifex.com/decentfox-studio/gino_1_0/"

target="_blank">Add your language here.</a>

</div>

<div>

</div>

</a>

</ul>

</div>

</main>

var sver = 'master';

var language = 'en';

var pagename = 'tutorials/tutorial';

</script>

</body>

</html>

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

FilesExpand file tree

tutorial.html

Latest commit

History

tutorial.html

File metadata and controls