Friday, September 27, 2019

SQLite for VB.Net, C#, and Python

For the past few months, I have been learning how to incorporate SQLite file databases in software. SQLite has become a viable candidate for replacing Microsoft Access as backend databases in much of the software I write in my occupation. It does not have that silly 2 GB size limit Access has that has been the bane of my programming career. I am running up against applications that process data that will require way more data than can be contained in an Access database.

I got turned onto SQLite because other software that I either feed or consume the results of are going to be moving to SQLite (late I know, but that's government work). This lead to me having to research how to use SQLite and figure out how to do all the things I do with Access files with SQLite database. For example, I had code that queries Oracle databases and puts the results in Access Databases for processing the data. I had to figure out how to put that data into SQLite database. Also because the databases end up being huge, I had to split them up into smaller databases, process the data, and merge the results into a new database. I would have to be doing the same thing with SQLite. Also the application I have been spending the last several months (if not years) on pushes, pulls, and manipulate data using a variety of programming languages: VB.NET, VBA, SQL, and Python. I am using a variety of technologies together: ADO.Net,Task Parallel Library (TPL), the .Net Framework, DAO, and all that comes with these. I am also making calls to and consuming results from Microsoft Access (2013 or 2016), Microsoft Excel (2013 or 2016), and ArcGIS 10.5, running on either Windows 10 or Windows 2012 R2, Therefore it would be a massive undertaking to switch out the functions provided by Access and Excel and replace them with SQLite. So I wrote a smaller program as practice. This program is designed to practice using SQLite from installing it so I can use it to deploying a program to a server that uses it.

This article will cover what SQLite is and explain what I have learned about how to use it. My goal would be to document everything I know now that wish I knew a month ago. I am going to assume that the reader is familiar with everything I am writing about here but  the stuff about SQLite. I want to cover installing SQLite; and creating databases, tables, views.

First off, SQLite is an open-source and free file database platform that can be used to store data on a disk drive and has enough bells and whistles so that you can use it as relational database. An SQLite database has a small footprint on your system even it has a great many records. It's portable across different computers and operating systems and does not require any server side software for it to run. This is why SQLite is great when you don't need to support a lot of simultaneous users and when you don't need an enterprise solution. Use Oracle or Microsoft SQL Server, or a different option for those situation. The SQL you write to manipulate SQLite is really close to the SQL you use for Access, or Excel or Oracle or for any other platform. There are differences but they are relatively minor. I will cover some of those "gotchas" later. In my current situation, SQLite would suffice, but how do you get started?

Installing SQLite


The majority of the code will be written using Visual Studio 2017, so the question is: Are there ODBC drivers that I can use so my code can talk to SQLite databases? Yes. Yes there are, I thought that the easiest way was to use "Manage Nuget Packages" to install the needed parts in Visual Studio. The one's I suggest to install if you are going to use the .Net Framework are System.Data.SQLite  and System.Data.SQLite.Core, If you want to run SQLite from the command line or outside Visual Studio, go to https://www.sqlite.org/download.html and "pick your poison." For Windows, do what I did and  download and install sqlite_dll_win64-x64-3290000.zip. It's easy to install. You do not have to do anything special for Python because SQLite is pre-installed.  Doing these installations mean that you can either look at your databases and perform many tasks either from Python, Visual Studio, or from command-line. However, if you want a GUI interface like the Microsoft Access or PL/SQL Developer you are gonna need to install something a little extra. Fortunately, there are many options, and some of them are even free. I use the software someone suggested to me, SQLite Expert Personal 5 - 64bit.exe. There is a 32-bit version as well. You can either pay to get the full version, or get the free one. I don't need the full version because I use it to look at the structure of a given SQLite database to see if it works the way I expect. I can even test out SQL statements to see if they make sense, Download it from http://www.sqliteexpert.com/download.html.

General Stuff to Keep In Mind


One of the really great things to remember about SQLite is that that when you create a connection, the database will be created if it does not already exist! You can even create a database in memory and it will be deleted when the connection is closed. Like all database connections, no matter what kind of database you are using, one needs to remember to use to close that connection object.

It is good to use transactions in SQLite because like for other databases it makes it really easy to do many actions and be able to commit  those changes or roll them back in one line of code!  It is also good to keep in mind some differences in SQLite's SQL. For example SQLite chokes if you try to insert text that has an apostrophe ('). Also it does not have a separate datatype for Date/Time. If you are porting your data into SQLite to use it from another database such as MS Access one would need to make sure to massage any text that has quotes or dates in the data before it is inserted.

Following sections will highlight some code snippets.


Creating an SQLite Database.


  • VB.Net
  • C#
  • Python

1
2
3
4
5
6
Imports System.Data.SQLite

Dim cnn As SQLiteConnection = New SQLiteConnection("Data Source=" & dbfile & ";")
cnn.Open()

cnn.Close()


1
2
3
4
5
using System.Data.SQLite;
SQLiteConnection cnn = new SQLiteConnection(("Data Source=" 
                + (dbfile + ";")));
cnn.Open();
cnn.Close();


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
import sqlite3
from sqlite3 import Error
 
 
def create_connection(db_file):
    """ create a database connection to a SQLite database """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        print(sqlite3.version)
    except Error as e:
        print(e)
    finally:
        if conn:
            conn.close()
 
 
if __name__ == '__main__':
    create_connection(r"C:\sqlite\db\pythonsqlite.db")
dbfile is the name of an SQLite File - *.db or *.sqlite;"C:\sqlite\db\pythonsqlite.db" is an example an SQLite file. These example are only for creating a database in SQLite but one must remember to close the connection when you are done manipulating the database. Also all versions of the code work the same way: if the file does not already exist create, the file is created and otherwise a connection to it is created.

Create SQLite Database



  • VB.Net
  • C#
  • Python

  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
Imports System.Data.SQLite
 
Public Class Form1
 
    Dim configDb As String = "configDb.db"
    Dim connectionString As String = "Data Source={0};Version=3;"
 
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        connectionString = String.Format(connectionString, configDb)
        create_sqlite_database()
    End Sub
 
    Public Sub create_sqlite_database()
        If Not My.Computer.FileSystem.FileExists(configDb) Then
 
            Try
                ' Create the SQLite database
                SQLiteConnection.CreateFile(configDb)
 
                MessageBox.Show("Database Created...")
 
            Catch ex As Exception
                MessageBox.Show("Database Created Failed...")
            End Try
 
        End If
 
    End Sub
 
    Private Sub btn_create_table_Click(sender As Object, e As EventArgs) Handles btn_create_table.Click
        create_config_table()
    End Sub
 
    Public Sub create_config_table()
        Dim create_table As String = String.Empty
 
        ' create table sql statement
        create_table &= "CREATE TABLE IF NOT EXISTS ConfigSettings ("
        create_table &= "config_key   TEXT PRIMARY KEY NOT NULL,"
        create_table &= "config_value TEXT)"
 
        Try
            Using con As New SQLiteConnection(connectionString)
                con.Open()
                Using cmd As New SQLiteCommand(create_table, con)
                    cmd.ExecuteNonQuery()
                End Using
            End Using
            MessageBox.Show("Table created successfully")
        Catch ex As Exception
            MessageBox.Show("create table failed")
        End Try
 
    End Sub
 
    Private Sub btn_insert_data_Click(sender As Object, e As EventArgs) Handles btn_insert_data.Click
        insert_config_data()
    End Sub
 
    Public Sub insert_config_data()
        Dim dt As New DataTable
        Dim SQL As String = String.Empty
 
        ' create datatable
        dt.Columns.Add(New DataColumn("config_key"))
        dt.Columns.Add(New DataColumn("config_value"))
 
        ' add default setting records
        dt.Rows.Add({"database name", "some database name"})
        dt.Rows.Add({"connection string", "some connection string"})
        dt.Rows.Add({"start date", "some start date"})
 
        Try
 
            'insert the records into the database table
 
            Using con As New SQLiteConnection(connectionString)
                con.Open()
                Dim transaction As SQLiteTransaction = con.BeginTransaction()
                Using transaction
                    Using cmd As New SQLiteCommand(con)
                        cmd.Transaction = transaction
                        For Each row As DataRow In dt.Rows
                            ' create the SQL statement
                            SQL = ""
                            SQL &= "INSERT INTO ConfigSettings (config_key,config_value) VALUES "
                            SQL &= String.Format("('{0}','{1}')", row("config_key"), row("config_value"))
 
                            cmd.CommandText = SQL
                            cmd.ExecuteNonQuery()
 
                        Next
                    End Using
                    transaction.Commit()
                End Using
            End Using
            MessageBox.Show("Insert Default Setting Success")
 
        Catch ex As Exception
            MessageBox.Show("Insert Default Setting Failed")
        End Try
 
    End Sub
End Class


  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
using System.Data.SQLite;
public class Form1 {
    
    private string configDb = "configDb.db";
    
    private string connectionString = "Data Source={0};Version=3;";
    
    private void Form1_Load(object sender, EventArgs e) {
        connectionString = string.Format(connectionString, configDb);
        this.create_sqlite_database();
    }
    
    public void create_sqlite_database() {
        if (!My.Computer.FileSystem.FileExists(configDb)) {
            try {
                //  Create the SQLite database
                SQLiteConnection.CreateFile(configDb);
                MessageBox.Show("Database Created...");
            }
            catch (Exception ex) {
                MessageBox.Show("Database Created Failed...");
            }
            
        }
        
    }
    
    private void btn_create_table_Click(object sender, EventArgs e) {
        this.create_config_table();
    }
    
    public void create_config_table() {
        string create_table = String.Empty;
        //  create table sql statement
        "CREATE TABLE IF NOT EXISTS ConfigSettings (";
        "config_key   TEXT PRIMARY KEY NOT NULL,";
        "config_value TEXT)";
        try {
            Using;
            ((void)(con));
            new SQLiteConnection(connectionString);
            con.Open();
            Using;
            ((void)(cmd));
            new SQLiteCommand(create_table, con);
            cmd.ExecuteNonQuery();
        }
        
    }
    
    private Exception ex;
}
EndSubEndUsing;
MessageBox.Show("Insert Default Setting Success");
CatchMessageBox.Show("Insert Default Setting Failed");
Endtry {
}

Endclass Unknown {
}

    
    private void btn_insert_data_Click(object sender, EventArgs e) {
        insert_config_data();
    }
    
    public void insert_config_data() {
        DataTable dt = new DataTable();
        string SQL = String.Empty;
        //  create datatable
        dt.Columns.Add(new DataColumn("config_key"));
        dt.Columns.Add(new DataColumn("config_value"));
        //  add default setting records
        dt.Rows.Add({, "database name", "some database name");
        dt.Rows.Add({, "connection string", "some connection string");
        dt.Rows.Add({, "start date", "some start date");
        try {
            // insert the records into the database table
            Using;
            ((void)(con));
            new SQLiteConnection(connectionString);
            con.Open();
            SQLiteTransaction transaction = con.BeginTransaction();
            Using;
            transaction;
            Using;
            ((void)(cmd));
            new SQLiteCommand(con);
            cmd.Transaction = transaction;
            foreach (DataRow row in dt.Rows) {
                //  create the SQL statement
                SQL = "";
                "INSERT INTO ConfigSettings (config_key,config_value) VALUES ";
                string.Format("(\'{0}\',\'{1}\')", row["config_key"], row["config_value"]);
                cmd.CommandText = SQL;
                cmd.ExecuteNonQuery();
            }
            
        }
        
        transaction.Commit();
    }
    
    private Exception ex;


 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
import sqlite3
from sqlite3 import Error
 
 
def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)
 
    return conn
 
 
def create_table(conn, create_table_sql):
    """ create a table from the create_table_sql statement
    :param conn: Connection object
    :param create_table_sql: a CREATE TABLE statement
    :return:
    """
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)
 
 
def main():
    database = r"C:\sqlite\db\pythonsqlite.db"
 
    sql_create_projects_table = """ CREATE TABLE IF NOT EXISTS projects (
                                        id integer PRIMARY KEY,
                                        name text NOT NULL,
                                        begin_date text,
                                        end_date text
                                    ); """
 
    sql_create_tasks_table = """CREATE TABLE IF NOT EXISTS tasks (
                                    id integer PRIMARY KEY,
                                    name text NOT NULL,
                                    priority integer,
                                    status_id integer NOT NULL,
                                    project_id integer NOT NULL,
                                    begin_date text NOT NULL,
                                    end_date text NOT NULL,
                                    FOREIGN KEY (project_id) REFERENCES projects (id)
                                );"""
 
    # create a database connection
    conn = create_connection(database)
 
    # create tables
    if conn is not None:
        # create projects table
        create_table(conn, sql_create_projects_table)
 
        # create tasks table
        create_table(conn, sql_create_tasks_table)
    else:
        print("Error! cannot create the database connection.")
 
 
if __name__ == '__main__':
    main()
configDb.db is the name of an SQLite File - *.db or *.sqlite;" dbfile is an example configDb.db is the name of an Sqlite File - *.db or *.sqlite. Each version of the code, creates a table and adds it to the SQLite database. The snippets do not include closing the connection. Notice that VB.Net and C# use a Transaction object.

Generate an SQLite Database from an ADO DataSet



  • VB.Net
  • C#
  • Python

  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
Public Shared Function DataSet2SLQite(ByVal DS As DataSet, ByVal SQLiteDB As String) As Boolean

        Dim worked As Boolean = True
        Dim tbl As DataTable
        Dim sqlite_conn As SQLiteConnection = Nothing

        Try
            ' create a new database connection:
            sqlite_conn = New SQLiteConnection("Data Source=" & SQLiteDB & ";Version=3;")

            ' open the connection:
            sqlite_conn.Open()

            For Each tbl In DS.Tables

                Dim Sql, rows, values, myColumnName, myDataType, CreateColumnString As String
                rows = ""

                Dim create_table As String = String.Empty
                CreateColumnString = String.Empty
                Dim CreateTableString As String = "CREATE TABLE IF NOT EXISTS " & tbl.TableName & " ("
                For Each dc As DataColumn In tbl.Columns


                    myColumnName = "[" & dc.ColumnName.ToString & "]"


                    myDataType = dc.DataType.ToString

                    If myColumnName = "[DBHI]" Then
                        CreateColumnString += myColumnName & " FLOAT, "
                    ElseIf myDataType <> "System.Byte[]" And myColumnName <> "[UPDATE_MEM]" And myColumnName <> "[NOTES2]" Then
                        Select Case myDataType
                            Case Is = "System.Double"
                                myDataType = "FLOAT"
                            Case Is = "System.Decimal"
                                myDataType = "FLOAT"
                            Case Is = "System.String"
                                myDataType = "VARCHAR(255)"
                            Case Is = "System.DateTime"
                                myDataType = "VARCHAR(255)"
                            Case Is = "System.Int32"
                                myDataType = "INTEGER"
                            Case Is = "System.Int16"
                                myDataType = "INTEGER"
                            Case Is = "System.Byte[]"
                                myDataType = "long binary data"
                            Case Is = "System.DateTime"
                                myDataType = "DATE"
                            Case Else
                                myDataType = "VARCHAR(255)"
                        End Select

                        If myColumnName = "FVSKeyword" Then
                            CreateColumnString += "CAST(FVSKeyword As text)" & ", "
                        Else
                            CreateColumnString += myColumnName & " " & myDataType & ", "
                        End If

                    End If
                Next

                CreateColumnString = CreateColumnString + ")"
                CreateColumnString = Replace(CreateColumnString, ", )", ")")
                CreateColumnString = Replace(CreateColumnString, ", [[", ", [")
                CreateColumnString = Replace(CreateColumnString, "]]", "]")

                CreateTableString = CreateTableString & CreateColumnString
                create_table = CreateTableString
                Try
                    Dim transaction As SQLiteTransaction = sqlite_conn.BeginTransaction()
                    Using transaction
                        Try

                            Using cmd As New SQLiteCommand(create_table, sqlite_conn)
                                cmd.Transaction = transaction
                                cmd.ExecuteNonQuery()
                            End Using
                        Catch
                            MsgBox(Err.Number & " " & Err.Description)
                            MsgBox(create_table & " failed!")
                        End Try


                        Using cmd As New SQLiteCommand(sqlite_conn)
                            cmd.Transaction = transaction
                            For Each row As DataRow In tbl.Rows
                                ' create the SQL statement
                                values = ""
                                Dim CreateRowString As String = ""
                                rows = ""

                                For Each dc As DataColumn In tbl.Columns

                                    myDataType = dc.DataType.ToString

                                    Try

                                        Select Case myDataType

                                            Case Is = "System.Double"
                                                If Not IsDBNull(row(dc)) And Not Double.IsInfinity(row(dc)) Then
                                                    CreateRowString = CreateRowString & CDbl(row(dc)) & ", "
                                                Else
                                                    CreateRowString = CreateRowString & 0.0 & ", "
                                                End If
                                            Case Is = "System.Decimal"
                                                If Not IsDBNull(row(dc)) And Not Double.IsInfinity(row(dc)) Then
                                                    CreateRowString = CreateRowString & CDbl(row(dc)) & ", "
                                                Else
                                                    CreateRowString = CreateRowString & 0.0 & ", "
                                                End If
                                            Case Is = "System.Int16"
                                                If Not IsDBNull(row(dc)) And Not Double.IsInfinity(row(dc)) Then
                                                    CreateRowString = CreateRowString & CDbl(row(dc)) & ", "
                                                Else
                                                    CreateRowString = CreateRowString & 0.0 & ", "
                                                End If
                                            Case Is = "System.Int32"
                                                If Not IsDBNull(row(dc)) And Not Double.IsInfinity(row(dc)) Then
                                                    CreateRowString = CreateRowString & CDbl(row(dc)) & ", "
                                                Else
                                                    CreateRowString = CreateRowString & 0.0 & ", "
                                                End If
                                            Case Is = "System.Int64"
                                                If Not IsDBNull(row(dc)) And Not Double.IsInfinity(row(dc)) Then
                                                    CreateRowString = CreateRowString & CDbl(row(dc)) & ", "
                                                Else
                                                    CreateRowString = CreateRowString & 0.0 & ", "
                                                End If
                                            Case Is = "System.String"
                                                If Not IsDBNull(row(dc)) Then
                                                    CreateRowString = CreateRowString & "'" & row(dc) & "', "
                                                Else
                                                    CreateRowString = CreateRowString & "' '" & ", "
                                                End If
                                            Case Is = "System.DateTime"
                                                If Not IsDBNull(row(dc)) Then
                                                    CreateRowString = CreateRowString & "'" & row(dc) & "', "
                                                Else
                                                    CreateRowString = CreateRowString & "' '" & ", "
                                                End If
                                        End Select
                                    Catch
                                        If myDataType = "System.String" Then
                                            CreateRowString = CreateRowString & "' '" & ", "
                                        Else
                                            CreateRowString = CreateRowString & 0.0 & ", "
                                        End If
                                    End Try


                                    If InStr(CreateRowString, "Database") > 0 Then
                                        Dim test As String() = Split(CreateRowString, ",")
                                        test(2) = Trim(test(2)).ToString
                                        test(2) = test(2).Replace("'%StandID%'", "''%StandID%''")
                                        CreateRowString = test(0) & "," & test(1) & "," & Trim(test(2)) & ", "
                                    ElseIf InStr(CreateRowString.Substring(1, CreateRowString.Length - 2), "'") > 0 Then
                                        CreateRowString = CreateRowString.Substring(1, CreateRowString.Length - 2).Replace("'", "''")
                                    ElseIf InStr(CreateRowString.Substring(1, CreateRowString.Length - 2), Chr(39)) > 0 Then
                                        CreateRowString = CreateRowString.Substring(1, CreateRowString.Length - 2).Replace(Chr(39), " ft ")
                                    End If
                                    rows = rows & dc.ColumnName & ","


                                Next

                                rows = rows + ")"
                                rows = Replace(rows, ",)", ")")

                                values = CreateRowString + ")"
                                values = Replace(values, ", )", ")")


                                Sql = ""
                                Sql = "INSERT INTO [" & tbl.TableName & "] (" & rows.Substring(0, rows.Length - 1) & ") VALUES (" & values.Substring(0, values.Length - 1) & ");"

                                Try
                                    cmd.CommandText = Sql
                                    cmd.ExecuteNonQuery()
                                Catch
                                    MsgBox(Err.Number & " " & Err.Description)
                                    MsgBox(Sql & " failed!")
                                End Try

                            Next
                        End Using
                        transaction.Commit()

                    End Using
                Catch

                    MsgBox(Err.Number & " " & Err.Description)
                    'MsgBox(create_table & " failed!")
                    worked = False
                End Try
            Next

        Catch
            MsgBox(Err.Number & " " & Err.Description)
            'MsgBox(create_table & " failed!")
            worked = False
        Finally
            sqlite_conn.Close()
            sqlite_conn = Nothing

        End Try

        Return worked

    End Function


  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
 public static bool DataSet2SLQite(DataSet DS, string SQLiteDB) {
        bool worked = true;
        DataTable tbl;
        SQLiteConnection sqlite_conn = null;
        try {
            //  create a new database connection:
            sqlite_conn = new SQLiteConnection(("Data Source=" 
                            + (SQLiteDB + ";Version=3;")));
            //  open the connection:
            sqlite_conn.Open();
            foreach (tbl in DS.Tables) {
                string Sql;
                string rows;
                string values;
                string myColumnName;
                string myDataType;
                string CreateColumnString;
                rows = "";
                string create_table = String.Empty;
                CreateColumnString = String.Empty;
                string CreateTableString = ("CREATE TABLE IF NOT EXISTS " 
                            + (tbl.TableName + " ("));
                foreach (DataColumn dc in tbl.Columns) {
                    myColumnName = ("[" 
                                + (dc.ColumnName.ToString + "]"));
                    myDataType = dc.DataType.ToString;
                    if ((myColumnName == "[DBHI]")) {
                        CreateColumnString = (CreateColumnString 
                                    + (myColumnName + " FLOAT, "));
                    }
                    else if (((myDataType != "System.Byte[]") 
                                && ((myColumnName != "[UPDATE_MEM]") 
                                && (myColumnName != "[NOTES2]")))) {
                        switch (myDataType) {
                        }
                        "System.Double";
                        myDataType = "FLOAT";
                        "System.Decimal";
                        myDataType = "FLOAT";
                        "System.String";
                        myDataType = "VARCHAR(255)";
                        "System.DateTime";
                        myDataType = "VARCHAR(255)";
                        "System.Int32";
                        myDataType = "INTEGER";
                        "System.Int16";
                        myDataType = "INTEGER";
                        "System.Byte[]";
                        myDataType = "long binary data";
                        "System.DateTime";
                        myDataType = "DATE";
                    }
                    else {
                        myDataType = "VARCHAR(255)";
                    }
                    
                    if ((myColumnName == "FVSKeyword")) {
                        CreateColumnString = (CreateColumnString + ("CAST(FVSKeyword As text)" + ", "));
                    }
                    else {
                        CreateColumnString = (CreateColumnString 
                                    + (myColumnName + (" " 
                                    + (myDataType + ", "))));
                    }
                    
                    CreateColumnString = (CreateColumnString + ")");
                    CreateColumnString = CreateColumnString.Replace(", )", ")");
                    CreateColumnString = CreateColumnString.Replace(", [[", ", [");
                    CreateColumnString = CreateColumnString.Replace("]]", "]");
                    CreateTableString = (CreateTableString + CreateColumnString);
                    create_table = CreateTableString;
                    try {
                        SQLiteTransaction transaction = sqlite_conn.BeginTransaction();
                        Using;
                        transaction;
                        try {
                            Using;
                            ((void)(cmd));
                            new SQLiteCommand(create_table, sqlite_conn);
                            cmd.Transaction = transaction;
                            cmd.ExecuteNonQuery();
                        }
                        catch (System.Exception MsgBox) {
                            (Err.Number + (" " + Err.Description));
                            MsgBox((create_table + " failed!"));
                        }
                        
                        Using;
                        ((void)(cmd));
                        new SQLiteCommand(sqlite_conn);
                        cmd.Transaction = transaction;
                        foreach (DataRow row in tbl.Rows) {
                            //  create the SQL statement
                            values = "";
                            string CreateRowString = "";
                            rows = "";
                            foreach (DataColumn dc in tbl.Columns) {
                                myDataType = dc.DataType.ToString;
                                try {
                                    switch (myDataType) {
                                    }
                                    "System.Double";
                                    if ((!IsDBNull(row[dc]) 
                                                && !double.IsInfinity(row[dc]))) {
                                        CreateRowString = (CreateRowString 
                                                    + (double.Parse(row[dc]) + ", "));
                                    }
                                    else {
                                        CreateRowString = (CreateRowString + (0 + ", "));
                                    }
                                    
                                    "System.Decimal";
                                    if ((!IsDBNull(row[dc]) 
                                                && !double.IsInfinity(row[dc]))) {
                                        CreateRowString = (CreateRowString 
                                                    + (double.Parse(row[dc]) + ", "));
                                    }
                                    else {
                                        CreateRowString = (CreateRowString + (0 + ", "));
                                    }
                                    
                                    "System.Int16";
                                    if ((!IsDBNull(row[dc]) 
                                                && !double.IsInfinity(row[dc]))) {
                                        CreateRowString = (CreateRowString 
                                                    + (double.Parse(row[dc]) + ", "));
                                    }
                                    else {
                                        CreateRowString = (CreateRowString + (0 + ", "));
                                    }
                                    
                                    "System.Int32";
                                    if ((!IsDBNull(row[dc]) 
                                                && !double.IsInfinity(row[dc]))) {
                                        CreateRowString = (CreateRowString 
                                                    + (double.Parse(row[dc]) + ", "));
                                    }
                                    else {
                                        CreateRowString = (CreateRowString + (0 + ", "));
                                    }
                                    
                                    "System.Int64";
                                    if ((!IsDBNull(row[dc]) 
                                                && !double.IsInfinity(row[dc]))) {
                                        CreateRowString = (CreateRowString 
                                                    + (double.Parse(row[dc]) + ", "));
                                    }
                                    else {
                                        CreateRowString = (CreateRowString + (0 + ", "));
                                    }
                                    
                                    "System.String";
                                    if (!IsDBNull(row[dc])) {
                                        CreateRowString = (CreateRowString + ("\'" 
                                                    + (row[dc] + "\', ")));
                                    }
                                    else {
                                        CreateRowString = (CreateRowString + ("\' \'" + ", "));
                                    }
                                    
                                    "System.DateTime";
                                    if (!IsDBNull(row[dc])) {
                                        CreateRowString = (CreateRowString + ("\'" 
                                                    + (row[dc] + "\', ")));
                                    }
                                    else {
                                        CreateRowString = (CreateRowString + ("\' \'" + ", "));
                                    }
                                    
                                }
                                catch (myDataType If) {
                                    "System.String";
                                    CreateRowString = (CreateRowString + ("\' \'" + ", "));
                                    CreateRowString = (CreateRowString + (0 + ", "));
                                }
                                
                                try {
                                    if (((CreateRowString.IndexOf("Database") + 1) 
                                                > 0)) {
                                        string[] test = CreateRowString.Split(",");
                                        test[2] = test[2].Trim().ToString;
                                        test[2] = test[2].Replace("\'%StandID%\'", "\'\'%StandID%\'\'");
                                        CreateRowString = (test[0] + ("," 
                                                    + (test[1] + ("," 
                                                    + (test[2].Trim() + ", ")))));
                                    }
                                    else if (((CreateRowString.Substring(1, (CreateRowString.Length - 2)).IndexOf("\'") + 1) 
                                                > 0)) {
                                        CreateRowString = CreateRowString.Substring(1, (CreateRowString.Length - 2)).Replace("\'", "\'\'");
                                    }
                                    else if (((CreateRowString.Substring(1, (CreateRowString.Length - 2)).IndexOf('\'') + 1) 
                                                > 0)) {
                                        CreateRowString = CreateRowString.Substring(1, (CreateRowString.Length - 2)).Replace('\'', " ft ");
                                    }
                                    
                                    rows = (rows 
                                                + (dc.ColumnName + ","));
                                    rows = (rows + ")");
                                    rows = rows.Replace(",)", ")");
                                    values = (CreateRowString + ")");
                                    values = values.Replace(", )", ")");
                                    Sql = "";
                                    Sql = ("INSERT INTO [" 
                                                + (tbl.TableName + ("] (" 
                                                + (rows.Substring(0, (rows.Length - 1)) + (") VALUES (" 
                                                + (values.Substring(0, (values.Length - 1)) + ");"))))));
                                    try {
                                        cmd.CommandText = Sql;
                                        cmd.ExecuteNonQuery();
                                    }
                                    catch (System.Exception MsgBox) {
                                        (Err.Number + (" " + Err.Description));
                                        MsgBox((Sql + " failed!"));
                                    }
                                    
                                }
                                
                                transaction.Commit();
                                Using;
                                MsgBox((Err.Number + (" " + Err.Description)));
                                // MsgBox(create_table & " failed!")
                                worked = false;
                                try {
                                }
                                catch (System.Exception MsgBox) {
                                    (Err.Number + (" " + Err.Description));
                                    worked = false;
                                }
                                finally {
                                    sqlite_conn.Close();
                                    sqlite_conn = null;
                                }
                                
                                return worked;
                            }
                            
                        }
                        
                    }
                    
                }
                
            }
            
        }
        
    }


1
2
3
4
5
6
7
8
9
import dataset

db = dataset.connect('sqlite:///:memory:')

table = db['sometable']
table.insert(dict(name='John Doe', age=37))
table.insert(dict(name='Jane Doe', age=34, gender='female'))

john = table.find_one(name='John Doe')

These code snippets takes a disconnected ADO.net Dataset containing multiple tables and uses it to define tables - their data and structure - and reproduce them as data in a SQLite database.


Conclusion



SQLite is a great choice if you need a relational database with not many simultaneous users, quick access, and no server code: in other words perfect for the application I am trying to develop. I might in the future write more posts about this software I am developing but this posting is primarily about SQLite. Feel free to comment and ask questions.

Reference Links

https://www.sqlitetutorial.net/sqlite-python/creating-database/
https://www.sqlitetutorial.net/sqlite-python/create-tables/
https://www.sqlitetutorial.net/sqlite-python/creating-database/
https://dataset.readthedocs.io/en/latest/


 With these examples, one can literally do almost anything you want to do with SQLite. 
Posted by mmcelhaney at 8:15 PM
Labels: , , , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)