Python list to sqlite
<pre><code>from bs4 import BeautifulSoup
import requests
import sqlite3
conn = sqlite3.connect('stadiumsDB.db')
c = conn.cursor()

c.executescript('''
DROP TABLE IF EXISTS Stadium;

CREATE TABLE Stadium (
    id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    Stadium   TEXT UNIQUE,
    Club   Text UNIQUE,
    Location  Text UNIQUE
)
''')
main_site = requests.get('https://en.wikipedia.org/wiki/List_of_Premier_League_stadiums').text
soup = BeautifulSoup(main_site)
column_names = [th.getText() for th in soup.findAll('th')[:9]]
print column_names
stadium_rows = soup.findAll('tr')[1:]
stadium_data = [[td.getText() for td in stadium_rows[i].findAll('td')]
            for i in range(len(stadium_rows))]
print stadium_data
</code></pre>

<p>I want to build a sqlite database. First row of the table will be the column name and i want them import from my column_names variable. Next rows i want them import from stadium_data variable. Any guidance please !!!!</p>

<p>For your table definition you can insert <code>stadium_data</code> best with <a href="https://docs.python.org/2.7/library/sqlite3.html#sqlite3.Cursor.executemany" rel="nofollow">executemany</a> like</p>

<pre><code>cn=column_names

stadium=cn.index("Stadium")
club=cn.index("Club")
location=cn.index("Location")
# this is for making sure that column order is correct

c.executemany("insert into stadium(stadium, club, location) values (?,?,?)",
              ((row[stadium], row[club],row[location]) for row in stadium_data))
</code></pre>

<p>This does not put <code>column_names</code> first in the table as this is doesnt seem like a good idea to me but you can do so with (insert this before the executemany)</p>

<pre><code>c.execute("insert into stadium(id,stadium, club, location) values (?,?,?,?)",
          (0,cn[stadium], cn[club],cn[location]))
</code></pre>

python sqlite