[PYTHON] Start SQLite in a programming language

I have almost no experience with SQL, I learned how to use SQLite in Node.js, C # </ span>, Python3, and HSP3. (For the time being, it works only with SQL statements)

Node.js is written in TypeScript somehow, so delete the type notation if you want to use each automatic. (Maybe just a summary Promise)

Execution environment

Library used

Node.js node-sqlite3 Installed below.

> npm i sqlite3

C# Microsoft.Data.Sqlite

> dotnet add package Microsoft.Data.Sqlite

It has a derived relationship with System.Data.SQLite, and its API functions are likely to match.

Python sqlite3 Since it is a standard library, no additional installation is required.

HSP sqlele Since it is a standard library, no additional installation is required.

Database initialization

Open the database, If you don't have a table, create a table (CREATE), Add an item (INSERT), Close the database.

Set automatic numbering (AUTO INCREMENT) in the "ID" column.

SQL

command


> sqlite3 sqlitest.db < sqlitest.sql
CREATE TABLE IF NOT EXISTS DMCARDS (
	ID INTEGER PRIMARY KEY AUTOINCREMENT,
	NAME TEXT,
	COLOR TEXT,
	COST INTEGER,
	POWER INTEGER
);

INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('La Ura Giga','light',1,2000)
INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Aqua guard','water',1,2000)
INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Bone slime','darkness',1,1000)
INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Blaze Claw','fire',1,1000)
INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Snipe Mosquito','Nature',1,2000)

Node.js

const sqlite=require("sqlite3");

var db=new sqlite.Database("../sqlitest.db",err=>{
	db.serialize(()=>{
		db.run(`CREATE TABLE IF NOT EXISTS DMCARDS (
			ID INTEGER PRIMARY KEY AUTOINCREMENT,
			NAME TEXT,COLOR TEXT,
			COST INTEGER,
			POWER INTEGER
		)`);

		db.run("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('La Ura Giga','light',1,2000)");
		db.run("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Aqua guard','water',1,2000)");
		db.run("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Bone slime','darkness',1,1000)");
		db.run("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Blaze Claw','fire',1,1000)");
		db.run("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Snipe Mosquito','Nature',1,2000)");

		db.close();
	});
});

C#

using System;
using Microsoft.Data.Sqlite;

class Program{
	static void Main(){
		using(var db=new SqliteConnection("Data Source=../sqlitest.db")){
			db.Open();
			var sql=db.CreateCommand();

			sql.CommandText=@"CREATE TABLE IF NOT EXISTS DMCARDS (
				ID INTEGER PRIMARY KEY AUTOINCREMENT,
				NAME TEXT,
				COLOR TEXT,
				COST INTEGER,
				POWER INTEGER
			)";
			sql.ExecuteNonQuery();

			sql.CommandText="INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('La Ura Giga','light',1,2000)";
			sql.ExecuteNonQuery();
			sql.CommandText="INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Aqua guard','water',1,2000)";
			sql.ExecuteNonQuery();
			sql.CommandText="INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Bone slime','darkness',1,1000)";
			sql.ExecuteNonQuery();
			sql.CommandText="INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Blaze Claw','fire',1,1000)";
			sql.ExecuteNonQuery();
			sql.CommandText="INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Snipe Mosquito','Nature',1,2000)";
			sql.ExecuteNonQuery();
		}
	}
}

Python

import sqlite3

with sqlite3.connect("../sqlitest.db") as db:
	db.row_factory=sqlite3.Row
	sql=db.cursor()

	sql.execute("""CREATE TABLE IF NOT EXISTS DMCARDS (
		ID INTEGER PRIMARY KEY AUTOINCREMENT,
		NAME TEXT,COLOR TEXT,
		COST INTEGER,
		POWER INTEGER
	)""")

	sql.execute("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('La Ura Giga','light',1,2000)")
	sql.execute("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Aqua guard','water',1,2000)")
	sql.execute("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Bone slime','darkness',1,1000)")
	sql.execute("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Blaze Claw','fire',1,1000)")
	sql.execute("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Snipe Mosquito','Nature',1,2000)")

HSP

#runtime "hsp3cl"
#include "sqlele.hsp"

sql_open "../sqlitest.db"

makeTable={"CREATE TABLE IF NOT EXISTS DMCARDS (
	ID INTEGER PRIMARY KEY AUTOINCREMENT,
	NAME TEXT,COLOR TEXT,
	COST INTEGER,
	POWER INTEGER
)"}
sql_q makeTable

sql_q "INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('La Ura Giga','light',1,2000)"
sql_q "INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Aqua guard','water',1,2000)"
sql_q "INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Bone slime','darkness',1,1000)"
sql_q "INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Blaze Claw','fire',1,1000)"
sql_q "INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Snipe Mosquito','Nature',1,2000)"

sql_close

View database

Open the database, SELECT all the tables, Close the database.

SQL

SELECT * FROM DMCARDS;

Node.js

const sqlite=require("sqlite3");

var db=new sqlite.Database("../sqlitest.db",err=>{
	db.serialize(()=>{
		console.log("ID,NAME,COLOR,COST,POWER");
		db.each("SELECT * FROM DMCARDS",(err,row)=>{
			console.log(`${row["ID"]},${row["NAME"]},${row["COLOR"]},${row["COST"]},${row["POWER"]}`);
		});

		db.close();
	});
});

C#

using System;
using Microsoft.Data.Sqlite;

class Program{
	static void Main(){
		using(var db=new SqliteConnection("Data Source=../sqlitest.db")){
			db.Open();
			var sql=db.CreateCommand();

			sql.CommandText="SELECT * FROM DMCARDS";
			using(var row=sql.ExecuteReader()){
				Console.WriteLine("ID,NAME,COLOR,COST,POWER");
				while(row.Read()){
					Console.WriteLine($"{row["ID"]},{row["NAME"]},{row["COLOR"]},{row["COST"]},{row["POWER"]}");
				}
			}
		}
	}
}

Python

import sqlite3

with sqlite3.connect("../sqlitest.db") as db:
	db.row_factory=sqlite3.Row
	sql=db.cursor()

	print("ID,NAME,COLOR,COST,POWER")
	for row in sql.execute("SELECT * FROM DMCARDS"):
		print(f'{row["ID"]},{row["NAME"]},{row["COLOR"]},{row["COST"]},{row["POWER"]}')

HSP

#runtime "hsp3cl"
#include "sqlele.hsp"

sql_open "../sqlitest.db"

mes "ID,NAME,COLOR,COST,POWER"
sdim rows
sql_q "SELECT * FROM DMCARDS",rows
repeat stat
	mes sql_v("ID",rows)+","+sql_v("NAME",rows)+","+sql_v("COLOR",rows)+","+sql_i("COST",rows)+","+sql_i("POWER",rows)
	sql_next rows
loop

sql_close

Change theta base

Open the database, Change the table (UPDATE), Close the database.

SQL

UPDATE DMCARDS SET COLOR='White' WHERE COLOR='light';
UPDATE DMCARDS SET COLOR='Blue' WHERE COLOR='water';
UPDATE DMCARDS SET COLOR='black' WHERE COLOR='darkness';
UPDATE DMCARDS SET COLOR='Red' WHERE COLOR='fire';
UPDATE DMCARDS SET COLOR='Green' WHERE COLOR='Nature';

Node.js

const sqlite=require("sqlite3");

var db=new sqlite.Database("../sqlitest.db",err=>{
	db.serialize(()=>{
		db.run("UPDATE DMCARDS SET COLOR='White' WHERE COLOR='light'");
		db.run("UPDATE DMCARDS SET COLOR='Blue' WHERE COLOR='water'");
		db.run("UPDATE DMCARDS SET COLOR='black' WHERE COLOR='darkness'");
		db.run("UPDATE DMCARDS SET COLOR='Red' WHERE COLOR='fire'");
		db.run("UPDATE DMCARDS SET COLOR='Green' WHERE COLOR='Nature'");

		db.close();
	});
});

C#

using System;
using Microsoft.Data.Sqlite;

class Program{
	static void Main(){
		using(var db=new SqliteConnection("Data Source=../sqlitest.db")){
			db.Open();
			var sql=db.CreateCommand();

			sql.CommandText="UPDATE DMCARDS SET COLOR='White' WHERE COLOR='light'";
			sql.ExecuteNonQuery();
			sql.CommandText="UPDATE DMCARDS SET COLOR='Blue' WHERE COLOR='water'";
			sql.ExecuteNonQuery();
			sql.CommandText="UPDATE DMCARDS SET COLOR='black' WHERE COLOR='darkness'";
			sql.ExecuteNonQuery();
			sql.CommandText="UPDATE DMCARDS SET COLOR='Red' WHERE COLOR='Red'";
			sql.ExecuteNonQuery();
			sql.CommandText="UPDATE DMCARDS SET COLOR='Green' WHERE COLOR='Nature'";
			sql.ExecuteNonQuery();
		}
	}
}

Python

import sqlite3

with sqlite3.connect("../sqlitest.db") as db:
	db.row_factory=sqlite3.Row
	sql=db.cursor()

	sql.execute("UPDATE DMCARDS SET COLOR='White' WHERE COLOR='light'")
	sql.execute("UPDATE DMCARDS SET COLOR='Blue' WHERE COLOR='water'")
	sql.execute("UPDATE DMCARDS SET COLOR='black' WHERE COLOR='darkness'")
	sql.execute("UPDATE DMCARDS SET COLOR='Red' WHERE COLOR='fire'")
	sql.execute("UPDATE DMCARDS SET COLOR='Green' WHERE COLOR='Nature'")

HSP

#runtime "hsp3cl"
#include "sqlele.hsp"

sql_open "../sqlitest.db"

sql_q "UPDATE DMCARDS SET COLOR='White' WHERE COLOR='light'"
sql_q "UPDATE DMCARDS SET COLOR='Blue' WHERE COLOR='water'"
sql_q "UPDATE DMCARDS SET COLOR='black' WHERE COLOR='darkness'"
sql_q "UPDATE DMCARDS SET COLOR='Red' WHERE COLOR='fire'"
sql_q "UPDATE DMCARDS SET COLOR='Green' WHERE COLOR='Nature'"

sql_close

Delete database

Open the database, If the table exists, DROP the table and Close the database.

SQL

DROP TABLE IF EXISTS DMCARDS;

Node.js

const sqlite=require("sqlite3");

var db=new sqlite.Database("../sqlitest.db",err=>{
	db.serialize(()=>{
		db.run("DROP TABLE IF EXISTS DMCARDS");

		db.close();
	});
});

C#

using System;
using Microsoft.Data.Sqlite;

class Program{
	static void Main(){
		using(var db=new SqliteConnection("Data Source=../sqlitest.db")){
			db.Open();
			var sql=db.CreateCommand();

			sql.CommandText="DROP TABLE IF EXISTS DMCARDS";
			sql.ExecuteNonQuery();
		}
	}
}

Python

import sqlite3

with sqlite3.connect("../sqlitest.db") as db:
	db.row_factory=sqlite3.Row
	sql=db.cursor()

	sql.execute("DROP TABLE IF EXISTS DMCARDS")

HSP

#runtime "hsp3cl"
#include "sqlele.hsp"

sql_open "../sqlitest.db"

sql_q "DROP TABLE IF EXISTS DMCARDS"

sql_close

Database operation summary

The database operations up to this point are summarized as a function. (Excluding SQL statements. Can I use functions in SQLite?)

SQL

--Delete database
DROP TABLE IF EXISTS DMCARDS;

--Database(table)Initialize
CREATE TABLE IF NOT EXISTS DMCARDS (
	ID INTEGER PRIMARY KEY AUTOINCREMENT,
	NAME TEXT,COLOR TEXT,
	COST INTEGER,
	POWER INTEGER
);
INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('La Ura Giga','light',1,2000);
INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Aqua guard','water',1,2000);
INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Bone slime','darkness',1,1000);
INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Blaze Claw','fire',1,1000);
INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Snipe Mosquito','Nature',1,2000);

--View database
SELECT * FROM DMCARDS;
SELECT '--------------------------------------';

--Change database
UPDATE DMCARDS SET COLOR='White' WHERE COLOR='light';
UPDATE DMCARDS SET COLOR='Blue' WHERE COLOR='water';
UPDATE DMCARDS SET COLOR='black' WHERE COLOR='darkness';
UPDATE DMCARDS SET COLOR='Red' WHERE COLOR='fire';
UPDATE DMCARDS SET COLOR='Green' WHERE COLOR='Nature';

--View database
SELECT * FROM DMCARDS;

Node.js

const sqlite=require("sqlite3");

//Database(table)Initialize
function makeDB():Promise<void>{
	return new Promise(resolve=>{
		var db=new sqlite.Database("../sqlitest.db",err=>{
			db.serialize(()=>{
				db.run(`CREATE TABLE IF NOT EXISTS DMCARDS (
					ID INTEGER PRIMARY KEY AUTOINCREMENT,
					NAME TEXT,COLOR TEXT,
					COST INTEGER,
					POWER INTEGER
				)`);

				db.run("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('La Ura Giga','light',1,2000)");
				db.run("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Aqua guard','water',1,2000)");
				db.run("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Bone slime','darkness',1,1000)");
				db.run("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Blaze Claw','fire',1,1000)");
				db.run("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Snipe Mosquito','Nature',1,2000)");

				db.close(resolve);
			});
		});
	});
}

//Update database
function updateDB():Promise<void>{
	return new Promise(resolve=>{
		var db=new sqlite.Database("../sqlitest.db",err=>{
			db.serialize(()=>{
				db.run("UPDATE DMCARDS SET COLOR='White' WHERE COLOR='light'");
				db.run("UPDATE DMCARDS SET COLOR='Blue' WHERE COLOR='water'");
				db.run("UPDATE DMCARDS SET COLOR='black' WHERE COLOR='darkness'");
				db.run("UPDATE DMCARDS SET COLOR='Red' WHERE COLOR='fire'");
				db.run("UPDATE DMCARDS SET COLOR='Green' WHERE COLOR='Nature'");

				db.close(resolve);
			});
		});
	});
}

//Delete database
function dropDB():Promise<void>{
	return new Promise(resolve=>{
		var db=new sqlite.Database("../sqlitest.db",err=>{
			db.serialize(()=>{
				db.run("DROP TABLE IF EXISTS DMCARDS");

				db.close(resolve);
			});
		});
	});
}

//Change database
function viewDB():Promise<void>{
	return new Promise(resolve=>{
		var db=new sqlite.Database("../sqlitest.db",err=>{
			db.serialize(()=>{
				console.log("ID,NAME,COLOR,COST,POWER");
				db.each("SELECT * FROM DMCARDS",(err,row)=>{
					console.log(`${row["ID"]},${row["NAME"]},${row["COLOR"]},${row["COST"]},${row["POWER"]}`);
				});

				db.close(resolve);
			});
		});
	});
}

(async function(){
	await dropDB();
	await makeDB();
	await viewDB();
	await updateDB();
	await viewDB();
})();

C#

using System;
using Microsoft.Data.Sqlite;

class Program{
	//Database(table)Initialize
	static void makeDB(){
		using(var db=new SqliteConnection("Data Source=../sqlitest.db")){
			db.Open();
			var sql=db.CreateCommand();

			sql.CommandText=@"CREATE TABLE IF NOT EXISTS DMCARDS (
				ID INTEGER PRIMARY KEY AUTOINCREMENT,
				NAME TEXT,
				COLOR TEXT,
				COST INTEGER,
				POWER INTEGER
			)";
			sql.ExecuteNonQuery();

			sql.CommandText="INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('La Ura Giga','light',1,2000)";
			sql.ExecuteNonQuery();
			sql.CommandText="INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Aqua guard','water',1,2000)";
			sql.ExecuteNonQuery();
			sql.CommandText="INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Bone slime','darkness',1,1000)";
			sql.ExecuteNonQuery();
			sql.CommandText="INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Blaze Claw','fire',1,1000)";
			sql.ExecuteNonQuery();
			sql.CommandText="INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Snipe Mosquito','Nature',1,2000)";
			sql.ExecuteNonQuery();
		}
	}

	//Change database
	static void updateDB(){
		using(var db=new SqliteConnection("Data Source=../sqlitest.db")){
			db.Open();
			var sql=db.CreateCommand();

			sql.CommandText="UPDATE DMCARDS SET COLOR='White' WHERE COLOR='light'";
			sql.ExecuteNonQuery();
			sql.CommandText="UPDATE DMCARDS SET COLOR='Blue' WHERE COLOR='water'";
			sql.ExecuteNonQuery();
			sql.CommandText="UPDATE DMCARDS SET COLOR='black' WHERE COLOR='darkness'";
			sql.ExecuteNonQuery();
			sql.CommandText="UPDATE DMCARDS SET COLOR='Red' WHERE COLOR='Red'";
			sql.ExecuteNonQuery();
			sql.CommandText="UPDATE DMCARDS SET COLOR='Green' WHERE COLOR='Nature'";
			sql.ExecuteNonQuery();
		}
	}

	//Delete database
	static void dropDB(){
		using(var db=new SqliteConnection("Data Source=../sqlitest.db")){
			db.Open();
			var sql=db.CreateCommand();

			sql.CommandText="DROP TABLE IF EXISTS DMCARDS";
			sql.ExecuteNonQuery();
		}
	}

	//View database
	static void viewDB(){
		using(var db=new SqliteConnection("Data Source=../sqlitest.db")){
			db.Open();
			var sql=db.CreateCommand();

			sql.CommandText="SELECT * FROM DMCARDS";
			using(var row=sql.ExecuteReader()){
				Console.WriteLine("ID,NAME,COLOR,COST,POWER");
				while(row.Read()){
					Console.WriteLine($"{row["ID"]},{row["NAME"]},{row["COLOR"]},{row["COST"]},{row["POWER"]}");
				}
			}
		}
	}

	static void Main(){
		dropDB();
		makeDB();
		viewDB();
		updateDB();
		viewDB();
	}
}

Python

import sqlite3

#Database(table)Initialize
def makeDB():
	with sqlite3.connect("../sqlitest.db") as db:
		db.row_factory=sqlite3.Row
		sql=db.cursor()

		sql.execute("""CREATE TABLE IF NOT EXISTS DMCARDS (
			ID INTEGER PRIMARY KEY AUTOINCREMENT,
			NAME TEXT,COLOR TEXT,
			COST INTEGER,
			POWER INTEGER
		)""")

		sql.execute("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('La Ura Giga','light',1,2000)")
		sql.execute("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Aqua guard','water',1,2000)")
		sql.execute("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Bone slime','darkness',1,1000)")
		sql.execute("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Blaze Claw','fire',1,1000)")
		sql.execute("INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Snipe Mosquito','Nature',1,2000)")

#Change database
def updateDB():
	with sqlite3.connect("../sqlitest.db") as db:
		db.row_factory=sqlite3.Row
		sql=db.cursor()

		sql.execute("UPDATE DMCARDS SET COLOR='White' WHERE COLOR='light'")
		sql.execute("UPDATE DMCARDS SET COLOR='Blue' WHERE COLOR='water'")
		sql.execute("UPDATE DMCARDS SET COLOR='black' WHERE COLOR='darkness'")
		sql.execute("UPDATE DMCARDS SET COLOR='Red' WHERE COLOR='fire'")
		sql.execute("UPDATE DMCARDS SET COLOR='Green' WHERE COLOR='Nature'")

#Delete database
def dropDB():
	with sqlite3.connect("../sqlitest.db") as db:
		db.row_factory=sqlite3.Row
		sql=db.cursor()

		sql.execute("DROP TABLE IF EXISTS DMCARDS")

#View database
def viewDB():
	with sqlite3.connect("../sqlitest.db") as db:
		db.row_factory=sqlite3.Row
		sql=db.cursor()

		print("ID,NAME,COLOR,COST,POWER")
		for row in sql.execute("SELECT * FROM DMCARDS"):
			print(f'{row["ID"]},{row["NAME"]},{row["COLOR"]},{row["COST"]},{row["POWER"]}')

if __name__=="__main__":
	dropDB()
	makeDB()
	viewDB()
	updateDB()
	viewDB()

HSP

#runtime "hsp3cl"
#include "sqlele.hsp"

#module Program
	;Database(table)Initialize
	#deffunc makeDB
		sql_open "../sqlitest.db"

		makeTable={"CREATE TABLE IF NOT EXISTS DMCARDS (
			ID INTEGER PRIMARY KEY AUTOINCREMENT,
			NAME TEXT,COLOR TEXT,
			COST INTEGER,
			POWER INTEGER
		)"}
		sql_q makeTable

		sql_q "INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('La Ura Giga','light',1,2000)"
		sql_q "INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Aqua guard','water',1,2000)"
		sql_q "INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Bone slime','darkness',1,1000)"
		sql_q "INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Blaze Claw','fire',1,1000)"
		sql_q "INSERT INTO DMCARDS (NAME,COLOR,COST,POWER) VALUES ('Snipe Mosquito','Nature',1,2000)"

		sql_close
	return

	;Change database
	#deffunc updateDB
		sql_open "../sqlitest.db"

		sql_q "UPDATE DMCARDS SET COLOR='White' WHERE COLOR='light'"
		sql_q "UPDATE DMCARDS SET COLOR='Blue' WHERE COLOR='water'"
		sql_q "UPDATE DMCARDS SET COLOR='black' WHERE COLOR='darkness'"
		sql_q "UPDATE DMCARDS SET COLOR='Red' WHERE COLOR='fire'"
		sql_q "UPDATE DMCARDS SET COLOR='Green' WHERE COLOR='Nature'"

		sql_close
	return

	;Delete database
	#deffunc dropDB
		sql_open "../sqlitest.db"

		sql_q "DROP TABLE IF EXISTS DMCARDS"

		sql_close
	return

	;View database
	#deffunc viewDB
		sql_open "../sqlitest.db"

		mes "ID,NAME,COLOR,COST,POWER"
		sdim rows
		sql_q "SELECT * FROM DMCARDS",rows
		repeat stat
			mes sql_v("ID",rows)+","+sql_v("NAME",rows)+","+sql_v("COLOR",rows)+","+sql_i("COST",rows)+","+sql_i("POWER",rows)
			sql_next rows
		loop

		sql_close
	return

	#deffunc main
		dropDB
		makeDB
		viewDB
		updateDB
		viewDB
	return
#global

main

Impressions or supplements

That's the result of trying SQLite.

I feel that INSERT and UPDATE look better when poured in a loop, but this time I'm just honest because I mainly check the operation. It doesn't make sense to open and close the database for each function in the final summary.

Impressions about the language are similar to the use of the library. Perhaps. In particular, the SELECT statement has a return value, so the difference in library specifications is often noticeable.

SQL I'm not used to writing in a completely different way from ordinary procedural languages ... There is an if statement, there is a for statement, and there is a function, which is an ordinary language, but it is difficult to require a different way of thinking in SQL.

The SELECT statement in SQL is a print statement, and it feels like a foreach statement, so I'm not sure. Perhaps the image is that the map is processed by applying a filter.

Node.js Asynchronous is the biggest enemy. Must be handled outside of db.serialize (). Also note that db.close does not seem to be synchronized even if you plunge into db.serialize ().

Perhaps db.each (), which seems to be dedicated to SELECT statements, is useful. I saw that it was inconvenient because I couldn't break somewhere. (In that case, you should use db.all ())

C# Redundant anyway. It is difficult to always separate the input and execution of SQL statements. Another way to give a value to SQL is called a parameter, On the contrary, it seemed to be redundant, so I stopped using it this time.

Python I thought it was pretty concise and easy to write. At first I was confused about processing the return value of the SELECT statement, but ... (If you do it normally, tuples will be returned I didn't have to use db.commit () for some reason.

HSP Probably the simplest. Probably the drawback is that you can't open multiple databases at the same time because it's the only non-object-oriented language.

The SELECT statement feels like turning the iterator on its own with the received value. (VBA, VBS Dir () may be close)

After that, if you use "#cmpopt init 1", it will be moss, so be careful.

Recommended Posts

Start SQLite in a programming language
Programming language in "Hello World"
A programming language that young people will need in the future
The most sought after programming language in 2020
Draw graphs in the programming language Julia
Try using a stochastic programming language (Pyro)
Try a functional programming pipe in Python
Learn dynamic programming in Python (A ~ E)
Programming in python
Sqlite in python
Prepare a programming language environment for data analysis
Create an executable file in a scripting language
Start Django in a virtual environment with Pipenv
Tools that fit in your hand (programming language)
Set up a UDP server in C language
I tried using Pythonect, a dataflow programming language.
Create a web server in Go language (net/http) (2)
Try to make a Python module in C language
Japan may be Galapagos in terms of programming language
A programming language that protects the people from NHK
dict in dict Makes a dict a dict
I participated in competitive programming in a week after I started programming
Create a web server in Go language (net / http) (1)
Python programming in Excel
Start a Django project
OperationalError in SQLAlchemy + SQLite3
A note for embedding the scripting language in a bash script
Note 2 for embedding the scripting language in a bash script
How to temporarily implement a progress bar in a scripting language
A liberal arts engineer tried knocking 100 language processes in Python 02
Let's make a number guessing game in your own language!
A liberal arts engineer tried knocking 100 language processes in Python 01
A liberal arts engineer tried knocking 100 language processes in Python 00
Explanation of CSV and implementation example in each programming language
Machine language embedding in C language
Take a screenshot in Python
Heapsort made in C language
Create a function in Python
Create a dictionary in Python
Try programming with a shell!
Collaborate in a remote environment
[Gimp] Start scripting in Python
Make a bookmarklet in Python
Hello World in GO language
[2019 latest version] Programming language comparison
Draw a heart in Python
Try to select a language
[Python] Dynamic programming TDPC A
Start in 5 minutes GIMP Python-Fu
Created a package to support AWS Lambda development in Go language
Created gomi, a trash can tool for rm in Go language
Read the config file in Go language! Introducing a simple sample
Programming with Python / JavaScript / VBScript inline scripting in Automation Anywhere A 2019
Think seriously about what language to use in programming education and programming education.