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 creates the file for you if it not exist and if you have rights to write in your work directory.

Another explanation of your error may come from the queries 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 offence, just saying).

Anyway, after creating 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 rights 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.

  • 1
    pyspatialite is deprecated and not working in python 3.x. – BBG_GIS Aug 20 at 10:00

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.