C# with SQLite - 데이터베이스 연동 3

C# 2022. 2. 19. 19:52 |
반응형

C# WinForm과 SQLite를 이용해 간단한 회원관리 프로그램을 만들어 보자.

 

DataGridView, Button등을 윈폼에 적당히 배치한다.

 

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
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
 
using System.Data.SQLite;
 
namespace Manager
{
    public partial class Form1 : Form
    {
        SQLiteConnection connection;
        SQLiteCommand command;
        DataTable dataTable;
 
        public Form1()
        {
            InitializeComponent();
 
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dataGridView1.ReadOnly = true;
            dataGridView1.AllowUserToAddRows = false;
 
            comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;
            comboBox1.Items.Add("아이디로");
            comboBox1.Items.Add("이름으로");
            comboBox1.Items.Add("생일로");
            comboBox1.SelectedIndex = 0;
 
            try
            {
                connection = new SQLiteConnection("Data Source=" + Application.StartupPath + "/member.db");
                connection.Open();
 
                command = new SQLiteCommand(connection);
                command.CommandText = "CREATE TABLE IF NOT EXISTS member (" +
                    "id TEXT UNIQUE NOT NULL, " +
                    "name TEXT NOT NULL, " +
                    "birthday TEXT NOT NULL)";
                command.ExecuteNonQuery();
 
                command.CommandText = "SELECT * FROM member";
                SQLiteDataReader reader = command.ExecuteReader();
                dataTable = new DataTable();
                dataTable.Load(reader);
                dataGridView1.DataSource = dataTable;
                reader.Close();
 
                dataGridView1.Columns[0].HeaderText = "아이디";
                dataGridView1.Columns[1].HeaderText = "이름";
                dataGridView1.Columns[2].HeaderText = "생일";
 
                connection.Close();
 
                label4.Text = "총 회원수: " + dataGridView1.Rows.Count;
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            try // 회원 정보 수정
            {
                foreach (DataGridViewRow row in dataGridView1.Rows) // 아이디(변경불가 상황 가정)를 바꾸려 시도하는 경우 체크
                {
                    if (row.Cells[0].Value.ToString() == textBox1.Text.Trim()) // 아이디 비교
                    {
                        if (MessageBox.Show(row.Cells[1].Value + "의 데이터를 수정 합니다. 수정하시겠습니까?",
                            "수정", MessageBoxButtons.YesNo) == DialogResult.Yes)
                        {
                            connection = new SQLiteConnection("Data Source=" + Application.StartupPath + "/member.db");
                            connection.Open();
 
                            command = new SQLiteCommand(connection);
                            command.CommandText = "UPDATE member set name=:name, birthday=:birthday WHERE id=:id";
                            command.Parameters.Add("name", DbType.String).Value = textBox2.Text.Trim();
                            command.Parameters.Add("birthday", DbType.String).Value = textBox3.Text.Trim();
                            command.Parameters.Add("id", DbType.String).Value = textBox1.Text.Trim();
                            command.ExecuteNonQuery();
 
                            connection.Close();
 
                            row.Cells[0].Value = textBox1.Text.Trim();
                            row.Cells[1].Value = textBox2.Text.Trim();
                            row.Cells[2].Value = textBox3.Text.Trim();
 
                            return;
                        }
                        else
                        {
                            //MessageBox.Show("바뀌지 않았습니다.");
                            return;
                        }
                    }
                }
                MessageBox.Show(textBox1.Text + "는 존재하지 않는 ID 입니다.");
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }
 
        private void dataGridView1_SelectionChanged(object sender, EventArgs e)
        {
            // 프로그램 시작 시 인덱스 에러 방지
            if (dataGridView1.SelectedRows.Count < 1)
                return;
 
            DataGridViewRow row = dataGridView1.SelectedRows[0];
            textBox1.Text = row.Cells[0].Value.ToString();
            textBox2.Text = row.Cells[1].Value.ToString();
            textBox3.Text = row.Cells[2].Value.ToString();
        }
 
        private void button2_Click(object sender, EventArgs e)
        {
            try // 회원 추가
            {
                connection = new SQLiteConnection("Data Source=" + Application.StartupPath + "/member.db");
                connection.Open();
 
                command = new SQLiteCommand(connection);
                command.CommandText = $"INSERT INTO member (id, name, birthday) VALUES (" +
                    $"'{textBox1.Text.Trim()}', '{textBox2.Text.Trim()}', '{textBox3.Text.Trim()}')";
                command.ExecuteNonQuery();
 
                connection.Close();
 
                dataTable.Rows.Add(textBox1.Text.Trim(), textBox2.Text.Trim(), textBox3.Text.Trim());
                label4.Text = "총 회원수: " + dataGridView1.Rows.Count;
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }
 
        private void button3_Click(object sender, EventArgs e)
        {
            try // 회원 삭제
            {
                if (MessageBox.Show($"{dataGridView1.SelectedRows[0].Cells[1].Value}의 데이터를 정말 삭제 하시겠습니까?",
                    "삭제", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    connection = new SQLiteConnection("Data Source=" + Application.StartupPath + "/member.db");
                    connection.Open();
 
                    command = new SQLiteCommand(connection);
                    command.CommandText = "DELETE from member WHERE id=:id";
                    command.Parameters.Add("id", DbType.String).Value = textBox1.Text.Trim();
                    command.ExecuteNonQuery();
 
                    connection.Close();
 
                    DataGridViewRow row = dataGridView1.SelectedRows[0];
                    dataGridView1.Rows.Remove(row);
 
                    label4.Text = "총 회원수: " + dataGridView1.Rows.Count;
                }
                else
                {
                    //MessageBox.Show("삭제하지 않았습니다");
                }
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }
 
        private void button4_Click(object sender, EventArgs e)
        {
            try // 검색
            {
                string filter;
                switch (comboBox1.SelectedIndex)
                {
                    case 0:
                        filter = "id";
                        break;
 
                    case 1:
                        filter = "name";
                        break;
 
                    case 2:
                        filter = "birthday";
                        break;
 
                    default:
                        filter = "id";
                        break;
                }
 
                connection = new SQLiteConnection("Data Source=" + Application.StartupPath + "/member.db");
                connection.Open();
                command = new SQLiteCommand(connection);
 
                if (textBox4.Text.Trim().Length == 0)
                {
                    command.CommandText = $"SELECT * FROM member";
                }
                else
                {
                    command.CommandText = $"SELECT * FROM member where {filter}='{textBox4.Text.Trim()}'";
                }
                SQLiteDataReader reader = command.ExecuteReader();
                dataTable = new DataTable();
                dataTable.Load(reader);
                dataGridView1.DataSource = dataTable;
                reader.Close();
 
                connection.Close();
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }
 
        private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            // 항목 번호 그리기
            String rowIndex = (e.RowIndex + 1).ToString();
 
            StringFormat centerFormat = new StringFormat()
            {
                Alignment = StringAlignment.Center,
                LineAlignment = StringAlignment.Center
            };
 
            Rectangle headerBound = new Rectangle(e.RowBounds.Left, e.RowBounds.Top,
                dataGridView1.RowHeadersWidth, e.RowBounds.Height);
            e.Graphics.DrawString(rowIndex, Font, SystemBrushes.ControlText, headerBound, centerFormat);
        }
    }
}
  

 

소스를 입력하고 빌드한다.

 

프로그램을 실행하면 빈 데이터베이스가 생성된다.

 

회원 추가, 수정, 삭제, 검색등을 해 본다.

 

반응형
저작자표시 비영리 변경금지

'C#' 카테고리의 다른 글

C# DataTable, DataSet, DataGridView, and XML 2  (0) 2022.02.20
C# DataTable, DataSet, DataGridView, and XML 1  (0) 2022.02.20
C# with SQLite - 데이터베이스 연동 2  (0) 2022.02.18
C# with SQLite - 데이터베이스 연동 1  (0) 2022.02.18
C# Google Cloud Vision API - 구글 클라우드 비전 API 3  (0) 2022.02.11
Posted by J-sean
:

C# with SQLite - 데이터베이스 연동 2

C# 2022. 2. 18. 15:32 |
반응형

C# WinForm의 DataGridView와 SQLite를 연동해 보자.

 

윈폼에 DataGridView를 적당히 배치한다.

 

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
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
 
using System.Data.SQLite;
 
namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
 
            try
            {
                SQLiteConnection connection = new SQLiteConnection("Data Source=" + Application.StartupPath + "/test.db");
                connection.Open();
 
                SQLiteCommand command = new SQLiteCommand(connection);
                command.CommandText = "CREATE TABLE IF NOT EXISTS test (" +
                    "id INTEGER PRIMARY KEY, " +
                    "name TEXT NOT NULL, " +
                    "birthday TEXT NOT NULL)";
                command.ExecuteNonQuery();
 
                command.CommandText = "INSERT OR IGNORE INTO test (id, name, birthday) VALUES (1, 'sean', '2020-01-20'), " +
                    "(2, 'david', '2021-11-03'), " +
                    "(3, 'john', '2022-05-17')";
                command.ExecuteNonQuery();
 
                command.CommandText = "SELECT * FROM test";
                SQLiteDataReader dataReader = command.ExecuteReader();
                DataTable dataTable = new DataTable();
                dataTable.Load(dataReader);
                dataGridView1.DataSource = dataTable;
                dataReader.Close();
 
                // 각 컬럼의 HeaderText를 변경하지 않으면 SQLite에 지정된 컬럼명 그대로 사용된다.
                dataGridView1.Columns[0].HeaderText = "아이디";
                dataGridView1.Columns[1].HeaderText = "이름";
                dataGridView1.Columns[2].HeaderText = "생일";
 
                connection.Close();
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }
 
        // DataGridView에 번호를 그려주는(DrawString) 함수.
        private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            //DataGridView dataGridView = sender as DataGridView;
            // 'sender as DataGridView' 는 dataGridView1과 동일.
 
            String rowIndex = (e.RowIndex + 1).ToString();
            //String rowIdx = (e.RowIndex + 1).ToString("D4"); // 10진수 4자리 표기(0001~9999). 16진수4자리는 'X4'.
 
            StringFormat centerFormat = new StringFormat()
            {
                Alignment = StringAlignment.Center,
                LineAlignment = StringAlignment.Center
            };
 
            Rectangle headerBound = new Rectangle(e.RowBounds.Left, e.RowBounds.Top,
                dataGridView1.RowHeadersWidth, e.RowBounds.Height);
            e.Graphics.DrawString(rowIndex, Font, SystemBrushes.ControlText, headerBound, centerFormat);
        }
    }
}
  

 

소스를 입력하고 빌드한다.

 

실행하면 데이터가 표시된다.

 

반응형
저작자표시 비영리 변경금지

'C#' 카테고리의 다른 글

C# DataTable, DataSet, DataGridView, and XML 1  (0) 2022.02.20
C# with SQLite - 데이터베이스 연동 3  (0) 2022.02.19
C# with SQLite - 데이터베이스 연동 1  (0) 2022.02.18
C# Google Cloud Vision API - 구글 클라우드 비전 API 3  (0) 2022.02.11
C# Google Cloud Vision API - 구글 클라우드 비전 API 2  (0) 2022.02.11
Posted by J-sean
:

C# with SQLite - 데이터베이스 연동 1

C# 2022. 2. 18. 14:29 |
반응형

C#에 SQLite 데이터베이스를 연동해 보자.

 

System.Data.SQLite를 설치한다.

 

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
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
using System.Data.SQLite;
 
namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                // 데이터베이스 생성 및 열기. 생성하려는 데이터베이스가 존재하면 열기만 한다.
                SQLiteConnection connection = new SQLiteConnection("Data Source=" + Environment.CurrentDirectory + "/test.db");
                connection.Open();
 
                SQLiteCommand command = new SQLiteCommand(connection);
                command.CommandText = "CREATE TABLE IF NOT EXISTS test (" +
                    "id INTEGER PRIMARY KEY, " +
                    "name TEXT NOT NULL, " +
                    "birthday TEXT NOT NULL)";
                // Execute the command and return the number of rows inserted/updated affected by it. 
                command.ExecuteNonQuery();
 
                // 이미 존재하는 데이터는 'OR IGNORE'에 의해 무시된다.
                command.CommandText = "INSERT OR IGNORE INTO test (id, name, birthday) VALUES (1, 'sean', '2020-01-20'), " +
                    "(2, 'david', '2021-11-03'), " +
                    "(3, 'john', '2022-05-17')";
                int rowCount = command.ExecuteNonQuery();
                Console.WriteLine("The number of rows inserted/updated affected by it: " + rowCount + "\n");
 
                command.CommandText = "SELECT * FROM test";
                SQLiteDataReader reader = command.ExecuteReader();
                // Reads the next row from the resultset. True if a new row was successfully loaded and is ready for processing
                while (reader.Read())
                {
                    Console.WriteLine($"ID: {reader.GetInt32(0)}, Name: {reader.GetString(1)}, Birthday: {reader.GetString(2)}");
                }
                // 다음 SQLiteCommand 사용을 위해 SQLiteDataReader 사용이 끝나면 종료해야 한다.
                reader.Close();
 
                // The date() function returns the date in this format: YYYY-MM-DD.
                // The julianday() function returns the Julian day - the number of days since noon in Greenwich
                // on November 24, 4714 B.C. (Proleptic Gregorian calendar).
                command.CommandText = "SELECT JULIANDAY(DATE('NOW')) - JULIANDAY(DATE(birthday)) FROM test WHERE id=2";
                int days = Convert.ToInt32(command.ExecuteScalar());
                Console.WriteLine("David's birthday was " + days + " days ago.");
 
                connection.Close();
            }
            catch (Exception exc)
            {
                Console.WriteLine(exc.Message);
            }
        }
    }
}
  

 

소스를 입력하고 빌드한다.

 

실행하면 데이터가 출려된다.

 

다시 한 번 실행하면 'OR IGNORE'에 의해 INSERT 명령어는 무시된다.

 

 

실행파일 폴더에 test.db가 생성되었다.

※ 참고

SQLite Download Page for Documentaion and Commend-Line Tools.

 

반응형
저작자표시 비영리 변경금지

'C#' 카테고리의 다른 글

C# with SQLite - 데이터베이스 연동 3  (0) 2022.02.19
C# with SQLite - 데이터베이스 연동 2  (0) 2022.02.18
C# Google Cloud Vision API - 구글 클라우드 비전 API 3  (0) 2022.02.11
C# Google Cloud Vision API - 구글 클라우드 비전 API 2  (0) 2022.02.11
C# Google Cloud Vision API - 구글 클라우드 비전 API 1  (0) 2022.02.11
Posted by J-sean
:

SQLite - C/C++

C, C++ 2021. 8. 27. 16:05 |
반응형

C/C++에서 SQLite를 사용해 보자.

 

C source code와 Precompiled Binaries for Windows를 다운로드 한다.

sqlite-amalgamation-XXX.zip과 sqlite-dll-win32-x86-XXX.zip를 다운로드하고 압축을 풀어 준다.

 

Visual Studio - Tools - Command Line - Developer Command Prompt를 실행 한다.

 

sqlite3.lib 파일을 생성 한다.

sqlite3.def, sqlite3.dll 파일이 있는 sqlite-dll-win32-x86-XXX 폴더에서 아래 명령어를 실행하면 sqlite3.lib 파일이 생성된다.

lib /def:sqlite3.def /machine:x86

 

sqlite-amalgamation-XXX 폴더에는 sqlite3.h 파일이 있다.

 

sqlite3.h, sqlite3.lib, sqlite3.dll 파일들을 프로젝트 폴더로 복사 한다.

 

 

SQLite 라이브러리 버전을 표시하는 간단한 프로그램을 작성하고 빌드해 보자.

 

SQLite 라이브러리 버전이 표시 된다.

 

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
#include <iostream>
#include "sqlite3.h"
 
#pragma comment(lib, "sqlite3.lib")
 
using namespace std;
 
int main()
{
    sqlite3* db;
    char* ErrMsg = 0;
    int rc;
 
    rc = sqlite3_open("test.db", &db);
    if (rc == SQLITE_OK) {
        cout << "Database opened successfully." << endl;
    }
    else {
        cout << "Can't open database: " << sqlite3_errmsg(db) << endl;
        return -1;
    }
        
    const char* sql1 = "CREATE TABLE COMPANY(" \
        "ID INT PRIMARY KEY NOT NULL, " \
        "NAME TEXT NOT NULL, " \
        "AGE INT NOT NULL, " \
        "ADDRESS CHAR(50), " \
        "SALARY REAL);";
 
    rc = sqlite3_exec(db, sql1, NULL, NULL, &ErrMsg);
    if (rc != SQLITE_OK) {
        cout << "SQL error: " << ErrMsg << endl;
        sqlite3_free(ErrMsg);
    }
    else {
        cout << "Table created successfully." << endl;
    }
    
    const char* sql2 = "INSERT INTO COMPANY VALUES(1, 'Paul', 32, 'California', 20000.00);" \
        "INSERT INTO COMPANY VALUES(2, 'Allen', 25, 'Texas', 15000.00);" \
        "INSERT INTO COMPANY VALUES(3, 'Teddy', 23, 'Norway', 20000.00);" \
        "INSERT INTO COMPANY VALUES(4, 'Mark', 25, 'Rich-Mond ', 65000.00);";
        
    rc = sqlite3_exec(db, sql2, NULL, NULL, &ErrMsg);
    if (rc != SQLITE_OK) {
        cout << "SQL error: " << ErrMsg << endl;
        sqlite3_free(ErrMsg);
    }
    else {
        cout << "Records created successfully." << endl;
    }
    
    sqlite3_close(db);
 
    return 0;
}
  

 

데이터베이스 생성, 테이블 생성, 레코드 삽입.

 

test.db 파일이 생성 된다.

 

 

 
#include <iostream>
#include "sqlite3.h"
 
#pragma comment(lib, "sqlite3.lib")
 
using namespace std;
 
/*
typedef int (*sqlite3_callback)(
    void*,    // Data provided in the 4th argument of sqlite3_exec()
    int,    // The number of columns in row
    char**,    // An array of strings representing fields in the row
    char**    // An array of strings representing column names
    );
*/
static int callback(void* data, int argc, char** argv, char** azColName) {
    int i;
    cout << (const char*)data << ":" << endl;
 
    for (i = 0; i < argc; i++) {
        cout << azColName[i] << " = " << (argv[i] ? argv[i] : "NULL") << endl;
    }
    cout << endl;
 
    return 0;
}
 
int main()
{
    sqlite3* db;
    char* ErrMsg = 0;
    int rc;
 
    rc = sqlite3_open("test.db", &db);
    if (rc == SQLITE_OK) {
        cout << "Database opened successfully." << endl;
    }
    else {
        cout << "Can't open database: " << sqlite3_errmsg(db) << endl;
        return -1;
    }
    
    const char* sql = "SELECT * FROM COMPANY;";
    const char* data = "Callback function called";
    
    rc = sqlite3_exec(db, sql, callback, (void*)data, &ErrMsg);
    if (rc != SQLITE_OK) {
        cout << "SQL error: " << ErrMsg << endl;
        sqlite3_free(ErrMsg);
    }
    else {
        cout << "Operation done successfully." << endl;
    }
    
    sqlite3_close(db);
 
    return 0;
}
  

 

데이터 읽기.

 

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
#include <iostream>
#include "sqlite3.h"
 
#pragma comment(lib, "sqlite3.lib")
 
using namespace std;
 
/*
typedef int (*sqlite3_callback)(
    void*,    // Data provided in the 4th argument of sqlite3_exec()
    int,    // The number of columns in row
    char**,    // An array of strings representing fields in the row
    char**    // An array of strings representing column names
    );
*/
static int callback(void* data, int argc, char** argv, char** azColName) {
    int i;
    cout << (const char*)data << ":" << endl;
 
    for (i = 0; i < argc; i++) {
        cout << azColName[i] << " = " << (argv[i] ? argv[i] : "NULL") << endl;
    }
    cout << endl;
 
    return 0;
}
 
int main()
{
    sqlite3* db;
    char* ErrMsg = 0;
    int rc;
 
    rc = sqlite3_open("test.db", &db);
    if (rc == SQLITE_OK) {
        cout << "Database opened successfully." << endl;
    }
    else {
        cout << "Can't open database: " << sqlite3_errmsg(db) << endl;
        return -1;
    }
    
    const char* sql = "UPDATE COMPANY SET SALARY = 25000.00 WHERE ID = 1; " \
        "DELETE FROM COMPANY WHERE ID = 2; " \
        "SELECT * FROM COMPANY;";
    const char* data = "Callback function called";
    
    rc = sqlite3_exec(db, sql, callback, (void*)data, &ErrMsg);
    if (rc != SQLITE_OK) {
        cout << "SQL error: " << ErrMsg << endl;
        sqlite3_free(ErrMsg);
    }
    else {
        cout << "Operation done successfully." << endl;
    }
    
    sqlite3_close(db);
 
    return 0;
}
  

 

데이터 수정 및 삭제.

 

반응형
저작자표시 비영리 변경금지

'C, C++' 카테고리의 다른 글

Qt6 설치 및 간단한 사용법  (0) 2021.09.25
MariaDB(MySQL) C API  (0) 2021.08.29
Linux(Ubuntu) Qt5 Image Display - 리눅스(우분투) Qt5 이미지 디스플레이  (0) 2021.02.13
Linux(Ubuntu) Qt5 GUI Programming - 리눅스(우분투) Qt5 GUI 프로그래밍  (0) 2021.02.12
Linux(Ubuntu) GTK+3(2) Image Display - 리눅스(우분투) GTK+3(2) 이미지 디스플레이  (0) 2021.02.12
Posted by J-sean
:

sqlite3 - DB API 2.0 interface for SQLite databases

Python 2019. 1. 21. 08:24 |
반응형

SQLite는 별도의 서버 없이 디스크 기반으로 SQL 쿼리를 지원하는 가벼운 C 라이브러리 데이터베이스 이다.


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
import sqlite3
 
dbpath = "fruit.db"
conn = sqlite3.connect(dbpath)
# Opens a connection to the SQLite database file database.
# By default returns a Connection object, unless a custom factory
# is given. You can use ":memory:" to open a database connection to 
# a database that resides in RAM instead of on disk.
cur = conn.cursor()
# The cursor method accepts a single optional parameter factory.
# If supplied, this must be a callable returning an instance of
# Cursor or its subclasses.
 
cur.executescript("""
drop table if exists items;
create table items(
    item_id integer primary key,
    name text unique,
    price integer
);
insert into items(name, price) values("Apple", 800);
insert into items(name, price) values("Orange", 700);
insert into items(name, price) values("Banana", 430);
""")
# This is a nonstandard convenience method for executing
# multiple SQL statements at once. It issues a COMMIT statement
# first, then executes the SQL script it gets as a parameter.
 
conn.commit()
# This method commits the current transaction. If you don’t call
# this method, anything you did since the last call to commit()
# is not visible from other database connections. If you wonder
# why you don’t see the data you’ve written to the database, please
# check you didn’t forget to call this method.
 
cur.execute("select * from items")
# This is a nonstandard shortcut that creates a cursor object by
# calling the cursor() method, calls the cursor’s execute() method
# with the parameters given, and returns the cursor.
item_list = cur.fetchall()
# Fetches all (remaining) rows of a query result, returning a list.
# Note that the cursor’s arraysize attribute can affect the performance
# of this operation. An empty list is returned when no rows are available.
for it in item_list:
    print(it)
 
print()
 
cur.execute("insert into items(name, price) values('Grape', 500)")
conn.commit()
 
cur.execute("select item_id, name, price from items")
item_list = cur.fetchall()
for it in item_list:
    print(it)
 
print()
 
cur.execute("insert into items(name, price) values(?, ?)", ("Strawberry", 800))
#conn.commit() 67라인에서 commit()을 호출 하므로 굳이 여기서 할 필요는 없다.
 
data = [("Mango", 250), ("Kiwi", 740), ("Peach", 650)]
cur.executemany("insert into items(name, price) values(?, ?)", data)
# Executes an SQL command against all parameter sequences or mappings found in the
# sequence seq_of_parameters.
conn.commit()
 
cur.execute("select item_id, name, price from items")
item_list = cur.fetchall()
for it in item_list:
    print(it)
 
print()
 
price_range = (600, 700)
cur.execute("select * from items where name = 'Kiwi' or (price >= ? and price <= ?)",
            price_range)
item_list = cur.fetchall()
for it in item_list:
    print("Name: ", it[1], ", Price: ", it[2])
 
for it in item_list:
    print("ID: %s, Name: %s, Price: %s" %it) # it 자체(튜플)를 전달해도 된다.
 
print("\nStrawberry를 Watermelon으로 변경, Orange 삭제")
 
cur.execute("update items set name = 'Watermelon', price = 1500 where name = 'Strawberry'")
cur.execute("delete from items where name = 'Orange'")
cur.execute("select * from items")
conn.commit()
 
item_list = cur.fetchall()
for it in item_list:
    print(it)
 
cur.close()
conn.close()
cs




fruit.db 파일이 생성 된다.



반응형
저작자표시 비영리 변경금지

'Python' 카테고리의 다른 글

Getting and installing the latest version of Python on Linux 리눅스에서 파이썬 설치 하기  (2) 2019.11.14
Visual Studio Python Environment 비주얼 스튜디오 파이썬 환경 추가 하기  (0) 2019.11.12
Pillow 차선 감지(Lane Detection)  (0) 2018.12.03
Pillow 이미지 서치(Image Search) 2  (0) 2018.12.02
Pillow 이미지 서치(Image Search) 1  (0) 2018.11.30
Posted by J-sean
:

티스토리툴바