Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #81475

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

Show key headers only | View raw

On Tue, Nov 25, 2014 at 5:44 AM, Dennis Lee Bieber
<wlfraed@ix.netcom.com> wrote:
> On Mon, 24 Nov 2014 10:05:11 +0200, "Frank Millman" <frank@chagford.com>
> declaimed the following:
>
>>I will explain why such a feature would have been useful for me.
>>
>>My accounting system handles multiple companies. I needed to figure out a
>>way to keep each one's data separate from the others.
>>
>>Originally I supported two databases - PostgreSQL and Sql Server. They both
>>have a concept called 'schemas', which handles my requirement elegantly -
>>each company's data is stored in its own schema.
>>
>
>         We must have a different impression of what a "schema" consists of. As
> I learned it, the "schema" basically came down to the statements defining
> the form of the data, the rules (triggers) on it, and the relationships
> between data items. http://en.wikipedia.org/wiki/Database_schema
>
>         By that definition, SQLite3 does support "schemas" (it's all the DDL
> statements used to create the database less the data itself -- which are
> DML). So does Firebird, MySQL, Access/JET

The sense here is one of qualified names. In DB2 and PostgreSQL, you
can create a database, and then within that, create schemas. You
choose a database when you connect, and then you can write code like
this:

select customer_name, invoice_date from accts.invoices join
crm.customers on (customer_id = crm.customers.id) where blah blah

The table names are qualified with their schema names. You can also
have a "default schema", which is the one used for unqualified table
names, which lets you pick one from a group of related data sets: for
example, Frank's accounting system would probably make use of that,
such that the code needn't care after startup. It'll just select a
default schema (or, in PostgreSQL, alter the search_path), and then
most of the code will just use unqualified names. Any data that's not
company-specific (maybe the application stores UI config data in the
database too) can be stored in a dedicated schema and referenced by
qualified name.

Technically, a table can be referred to as
database_name.schema_name.table_name (and then you can add
.column_name after that, but I have NEVER seen anyone use the full
four-parter!), but in PostgreSQL, the database_name, if present, must
be the one that you connected to. In MySQL, there these things that
are kinda databases and kinda schemas, are referred to by both names,
and in terms of data separation, primarily act like schemas. In
SQLite3, there are basically no schemas, AIUI, but you can use
databases to do something similar - with the originally-mentioned
limitation.

ChrisA

Back to comp.lang.python | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-22 18:11 -0800
  Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 13:22 +1100
    Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-22 19:54 -0800
      Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Michael Torrie <torriem@gmail.com> - 2014-11-22 21:58 -0700
      Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 16:41 +1100
        Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-22 22:08 -0800
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 17:32 +1100
            Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-22 22:46 -0800
            Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Christian Gollwitzer <auriocus@gmx.de> - 2014-11-23 10:55 +0100
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert "Frank Millman" <frank@chagford.com> - 2014-11-23 10:21 +0200
            Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-23 17:29 -0800
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 19:31 +1100
            Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Jussi Piitulainen <jpiitula@ling.helsinki.fi> - 2014-11-23 11:40 +0200
              Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 20:53 +1100
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2014-11-23 13:01 -0500
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert "Frank Millman" <frank@chagford.com> - 2014-11-24 10:05 +0200
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2014-11-24 13:44 -0500
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-25 10:17 +1100
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert "Frank Millman" <frank@chagford.com> - 2014-11-25 07:31 +0200
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-25 17:40 +1100
  Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2014-11-23 11:58 -0500
  Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-23 18:22 -0800
    Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-24 15:49 +1100

csiph-web