0

Is there a way in python to create an sqlite database file in memory, from a string? I have tried using sqlite3 and tempfile, but did not foubd something that helps

NI6
  • 2,477
  • 5
  • 17
  • 28

2 Answers2

1

I found an example of creating an in memory sqlite database here:

import sqlite3
con = sqlite3.connect(':memory:')

Some information about different ways to use :memory: can be found here, and Python specific documentation is here.

What exactly is in the string that you mentioned? If it is a buffer of SQLite commands, you can use con.execute(buffer). If not, you will need to get a cursor, create a table, and then insert the data manually. Here is an example from the Python API docs:

c = con.cursor()

# Create table
c.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
con.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
con.close()

If you wanted to write the in-memory database to a file, you can use SQLite's Online Backup API (Python wrapper here). If you wanted immediate access to the database as a file, however, but still wanted it in memory, I think you would need to use a RAM disk (I'm not sure if that's what you were asking, but I included it just in case).

Hope this helps.

Alex Bowe
  • 523
  • 3
  • 11
  • Yeah but this way the db is empty, and I need to write to that memory file the content I got in the string. – NI6 May 26 '16 at 09:02
  • You should be able to use `con.execute(buffer)` if the buffer has commands to create the table, etc... Otherwise you will need to create the table and add each data item from your string. There is an example at the top of the Python API docs. – Alex Bowe May 26 '16 at 09:18
  • If you have a previously created sqlite db but just want to load it into memory, maybe this will help: http://stackoverflow.com/questions/3850022/how-to-load-existing-db-file-to-memory-in-python-sqlite3 – Alex Bowe May 26 '16 at 09:31
0

first one needs to create table1 in the database

import sqlite3
con = sqlite3.connect("memory.db")
cur = con.cursor()
cur.execute("CREATE TABLE table1 (A TEXT, B REAL, C TEXT);")

Then insert your string to table1 in database assuming that line is the string and has three entries seperated by ";" 

cur.executemany('INSERT INTO table1 VALUES (?,?,?)',(line,))

#to finish
con.commit()
con.close()
cheng chen
  • 479
  • 1
  • 4
  • 6