python sqlite 'NoneType' object is not iterable

Question

I am using the sqlite3 in python.

I've created a table in the database. The table has some NULL values

import sqlite3
engine = sqlite3.connect('Practice')
BST = pd.DataFrame({'N':[1,3,5,7,2,6,4,8,10,12,14,9,13,11,15],'P':[2,2,6,6,4,4,15,9,9,13,13,11,11,15,'NULL']})
BST.to_sql(name = "BST", con=engine, if_exists = 'replace', index = False)

Then I want to create a view of this table

pd.read_sql_query("CREATE VIEW V1 as select P from BST",con=engine)

But it throws an error TypeError: 'NoneType' object is not iterable.

I wonder how should I create a view of a table in sqlite when the table contains NULL value?

i think you can't read sql and create a view in the same time — PRMoureu
– PRMoureu, Commented Jul 3, 2017 at 19:21
@PRMoureu thanks! So df.to_sql() is a pandas function so as to either create a real table in the database or create a view (which occupies less memory than create an actual table?) in the database that can be refer to later, am I right? pd.read_sql_query() is really just read the sql query and fetch the rows, columns in the created table (or view) in the database? — KevinKim
– KevinKim, Commented Jul 3, 2017 at 19:36
no i made a mistake, you must use engine.execute to create that view — PRMoureu
– PRMoureu, Commented Jul 3, 2017 at 19:57
@PRMoureu But actually I used your 1st version and it works pretty well. I even wrote a 2nd query that use the view V1.... — KevinKim
– KevinKim, Commented Jul 3, 2017 at 23:47
The real question is why is it working, because we were not creating a view, but a table named 'CREATE VIEW V1 as select P from BST', i will take a look later — PRMoureu
– PRMoureu, Commented Jul 4, 2017 at 10:39

PRMoureu · Accepted Answer · 2017-07-03 19:57:31Z

2

The code should be like this :

import sqlite3
import pandas as pd
engine = sqlite3.connect('Practice')
BST = pd.DataFrame({'N':[1,3,5,7,2,6,4,8,10,12,14,9,13,11,15],'P':[2,2,6,6,4,4,15,9,9,13,13,11,11,15,'NULL']})
BST.to_sql(name = "BST", con=engine, if_exists = 'replace', index = False) 

engine.execute("CREATE VIEW V1 as select P from BST")

print(pd.read_sql_query('SELECT * from V1', con=engine))

edited Jul 3, 2017 at 19:57

answered Jul 3, 2017 at 19:25

PRMoureu

13.4k6 gold badges46 silver badges52 bronze badges

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

Collectives™ on Stack Overflow

python sqlite 'NoneType' object is not iterable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related