Issue: Syntax error while using db.execute_sql to get sqlite3 table schema
Author: gzxultra
State: closed
Created: 2018-05-03 07:48:53

Problem:

I got a syntax error when using db.execute_sql to get sqlite3 table schema,

Reproduce:

```python
In [135]: db = SqliteDatabase('test.db')
     ...:
     ...:
     ...: class ExampleTable(Model):
     ...:     class Meta:
     ...:         table_name = 'test_table'
     ...:         database = db
     ...:
     ...:     created_time = DateTimeField(default=datetime.datetime.now)
     ...:     update_time = DateTimeField(default=datetime.datetime.now)
     ...:

In [136]: ExampleTable
Out[136]: __main__.ExampleTable

In [137]: ExampleTable.create_table()

In [138]: db.execute_sql('.schema')
---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
~/.local/share/virtualenvs/pycsvsql-TYJ8UyNv/lib/python3.6/site-packages/peewee.py in execute_sql(self, sql, params, commit)
   2566             try:
-> 2567                 cursor.execute(sql, params or ())
   2568             except Exception:

OperationalError: near ".": syntax error

During handling of the above exception, another exception occurred:

OperationalError                          Traceback (most recent call last)
<ipython-input-138-e0357ff2f49e> in <module>()
----> 1 db.execute_sql('.schema')

~/.local/share/virtualenvs/pycsvsql-TYJ8UyNv/lib/python3.6/site-packages/peewee.py in execute_sql(self, sql, params, commit)
   2572             else:
   2573                 if commit and not self.in_transaction():
-> 2574                     self.commit()
   2575         return cursor
   2576

~/.local/share/virtualenvs/pycsvsql-TYJ8UyNv/lib/python3.6/site-packages/peewee.py in __exit__(self, exc_type, exc_value, traceback)
   2365             new_type = self.exceptions[exc_type.__name__]
   2366             exc_args = exc_value.args
-> 2367             reraise(new_type, new_type(*exc_args), traceback)
   2368
   2369 EXCEPTIONS = {

~/.local/share/virtualenvs/pycsvsql-TYJ8UyNv/lib/python3.6/site-packages/peewee.py in reraise(tp, value, tb)
    173     def reraise(tp, value, tb=None):
    174         if value.__traceback__ is not tb:
--> 175             raise value.with_traceback(tb)
    176         raise value
    177

~/.local/share/virtualenvs/pycsvsql-TYJ8UyNv/lib/python3.6/site-packages/peewee.py in execute_sql(self, sql, params, commit)
   2565             cursor = self.cursor(commit)
   2566             try:
-> 2567                 cursor.execute(sql, params or ())
   2568             except Exception:
   2569                 if self.autorollback and not self.in_transaction():

OperationalError: near ".": syntax error
```

I was expecting something like this,
```sql
sqlite> .schema --indent
CREATE TABLE IF NOT EXISTS "test_table"(
  "id" INTEGER NOT NULL PRIMARY KEY,
  "created_time" DATETIME NOT NULL,
  "update_time" DATETIME NOT NULL
);
```

Could you tell me how to fix it?

Those "."-commands are only implemented in the sqlite shell. They're not part of the sql interface.

Check out the code in SqliteDatabase like get_tables, get_columns, etc if
you want to introspect the database. There's also the reflection module in
playhouse.reflection. Check the docs.

On Thu, May 3, 2018, 2:48 AM 夏天的风铃_ <notifications@github.com> wrote:

> I got a syntax error when using db.execute_sql to get sqlite3 table schema,
>
> Reproduce:
>
> In [135]: db = SqliteDatabase('test.db')
>      ...:
>      ...:
>      ...: class ExampleTable(Model):
>      ...:     class Meta:
>      ...:         table_name = 'test_table'
>      ...:         database = db
>      ...:
>      ...:     created_time = DateTimeField(default=datetime.datetime.now)
>      ...:     update_time = DateTimeField(default=datetime.datetime.now)
>      ...:
>
> In [136]: ExampleTable
> Out[136]: __main__.ExampleTable
>
> In [137]: ExampleTable.create_table()
>
> In [138]: db.execute_sql('.schema')
> ---------------------------------------------------------------------------
> OperationalError                          Traceback (most recent call last)
> ~/.local/share/virtualenvs/pycsvsql-TYJ8UyNv/lib/python3.6/site-packages/peewee.py in execute_sql(self, sql, params, commit)
>    2566             try:
> -> 2567                 cursor.execute(sql, params or ())
>    2568             except Exception:
>
> OperationalError: near ".": syntax error
>
> During handling of the above exception, another exception occurred:
>
> OperationalError                          Traceback (most recent call last)
> <ipython-input-138-e0357ff2f49e> in <module>()
> ----> 1 db.execute_sql('.schema')
>
> ~/.local/share/virtualenvs/pycsvsql-TYJ8UyNv/lib/python3.6/site-packages/peewee.py in execute_sql(self, sql, params, commit)
>    2572             else:
>    2573                 if commit and not self.in_transaction():
> -> 2574                     self.commit()
>    2575         return cursor
>    2576
>
> ~/.local/share/virtualenvs/pycsvsql-TYJ8UyNv/lib/python3.6/site-packages/peewee.py in __exit__(self, exc_type, exc_value, traceback)
>    2365             new_type = self.exceptions[exc_type.__name__]
>    2366             exc_args = exc_value.args
> -> 2367             reraise(new_type, new_type(*exc_args), traceback)
>    2368
>    2369 EXCEPTIONS = {
>
> ~/.local/share/virtualenvs/pycsvsql-TYJ8UyNv/lib/python3.6/site-packages/peewee.py in reraise(tp, value, tb)
>     173     def reraise(tp, value, tb=None):
>     174         if value.__traceback__ is not tb:
> --> 175             raise value.with_traceback(tb)
>     176         raise value
>     177
>
> ~/.local/share/virtualenvs/pycsvsql-TYJ8UyNv/lib/python3.6/site-packages/peewee.py in execute_sql(self, sql, params, commit)
>    2565             cursor = self.cursor(commit)
>    2566             try:
> -> 2567                 cursor.execute(sql, params or ())
>    2568             except Exception:
>    2569                 if self.autorollback and not self.in_transaction():
>
> OperationalError: near ".": syntax error
>
> I was expecting something like,
>
> sqlite> .schema
> CREATE TABLE IF NOT EXISTS "test_table" ("id" INTEGER NOT NULL PRIMARY KEY, "created_time" DATETIME NOT NULL, "update_time" DATETIME NOT NULL);
>
> Can you tell me how to fix it?
>
> —
> You are receiving this because you are subscribed to this thread.
> Reply to this email directly, view it on GitHub
> <https://github.com/coleifer/peewee/issues/1596>, or mute the thread
> <https://github.com/notifications/unsubscribe-auth/AAHUprxEhAxcns6tLtO0MPv2tREa9WdWks5turZmgaJpZM4TwodY>
> .
>