1

I have a big postgrSQL database. I would like to somehow store the full database in a python object, which form/structure would reflect the one of the database. Namely I imagine something like * An object database, with an attribute .tables which a kind of list of object "table", and a table object has an attribute "list_of_keys" (list of the column names) and an attribute "rows", which reflects all the rows of the corresponding table in the database.

Now, the main point i need is: i want to be able to perform a search in the database object, with exactely the same SQL synthax that i would use in the corresponding SQL database. Thus something like database.execute("SELECT * FROM .....") where, i repeat, "database" is a purely python object (which was filled with data coming from an SQL database, but which is now independent of it).

My aim is: i want to be able to apply the same algorithm either on a SQL-Database, or on a python-object, such as described above, without changing my code. So, i imagine, let "database" be either a usual database-connector/cursor (like with psycopg, for example), or a python object as i described, and the same piece of code database.execute("SELECT BLABLABLA") would work in both cases.

Is there any known module which allows that ?

thanks.

Improve this question
5
  • 1
    Did you look at SQLAlchemy? Commented Dec 11, 2013 at 8:28
  • i have heard about it, but i was not sure, it does what i want. Does it ? Commented Dec 11, 2013 at 8:33
  • Well, you could define a database/table model, and interact with with it dynamically with python. I have not heard of a tool that automatically reflects the scheme of your DB, at all times. Commented Dec 11, 2013 at 8:38
  • I do not want to reflect it a "all time". I just want to copy all datafrom the SQL Database at the beginning in the python object, and then work only with it (make a lot of SELECT queries). I've read again SQLchemy documentation, and i think sqlalchemy.orm.query.Query.from_statement is maybe what i need. Commented Dec 11, 2013 at 8:45
  • Use some ORM. For python good ORM is SQLAlchemy or SQLObject. But now I prefer pure SQL. ORMs have some lacks: problems with perfomance, bad portability to other languages, sql-like code. Commented Dec 11, 2013 at 8:48

1 Answer 1

Reset to default
2

It might get a bit complicated, but take a look at SQLite's in-memory storage:

import sqlite3
cnx = sqlite3.connect(':memory:')
cnx.execute('CREATE TABLE ...')

There are some differences in the SQL syntax, but the basic stuff works fine. This might also take a good amount of RAM, depending on your data.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

thanks for the hint. I will take a look a t it, though i'm afraid, SQLite is maybe not adapted to the size of my data (several tables with 10/20 collums and millions of rows)
@nicoroy then you probably won't be able to store your data as python object, and your question becomes unclear, you have to use some serialization methods for your object and why not a db then?
I already store my data in a self-made python dictionary, and it works (i mean, with respect to the memory size). But i need the feature "performing query with SQL sythax on the object", which i do not have, of course, with my self-made dictionary.
I'm wondering whether my prejudice about sqlite is founded. I always think of "lite" as "light", i.e. for "small" databases. I may be simply completely wrong !

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.