1

I want to create a new database in spatialite and want to connect to the database and execute the sql queries.

conn = db.connect('myDB.sqlite')
# creating a Cursor
cur = conn.cursor()
sql = 'select * from table_name'
cur.execute(sql)

Here i am connecting to the existing db. But i want to create a new db and connect to the db to execute the queries. How to do that using python.

  • Did you have a look to SpatiaLite and Python? – ArMoraer Mar 9 '16 at 11:37
  • @ArMoraer I am inserting and fetching the data using python.But before that i want to create a database and do the execution. – user99 Mar 9 '16 at 12:20
  • in the final script, you can find these lines: # creating/connecting the test_db conn = db.connect('test_db.sqlite'). So it seems that db.connect() also creates the database. – ArMoraer Mar 9 '16 at 12:22
  • @ArMoraer I tried but it throws an error. So i want to create one database and connect to that to do the sql queries. – user99 Mar 9 '16 at 12:25
3

As it says in the help document gave by ArMoraer. The command create the file for you if it not exist and if you have rigths to write in your work directory.

A another explanation of your error may come from the querie that ask to display everything (*) in the 'table_name'. But I assume you know a bit of sql and python if you are asking this question. (No offense, just saying).

Anyway, after create the Database you need to initialize the database with the geometry table and co. So, this is my suggestion, first check where you are working and if you don't have rigths to write on that directory change it

# Check where is your working directory
import os
os.getcwd() # gives the path where you are working on
# change directory
os.chdir('path/with/rigths')

Then you can create your database

# importing pyspatialite
from pyspatialite import dbapi2 as db

# creating/connecting the test_db
conn = db.connect('test_db.sqlite')

# creating a Cursor
cur = conn.cursor()

# initializing Spatial MetaData
# using v.2.4.0 this will automatically create
# GEOMETRY_COLUMNS and SPATIAL_REF_SYS
sql = 'SELECT InitSpatialMetadata()'
cur.execute(sql)

# creating a POINT table
sql = 'CREATE TABLE test_pt ('
sql += 'id INTEGER NOT NULL PRIMARY KEY,'
sql += 'name TEXT NOT NULL)'
cur.execute(sql)

# Test the table
sql = 'select * from test_pt'
cur.execute(sql)

It should work, if not try to reinstall your pyspatialite libs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.