[SHARE CODE] PRAGMA query for sqlite

Phuket2

Was hoping to get some Guru advice here. The script I list below, I include into my file that has my PRAGMA class in it.

It appears a little wasteful. But I am going for clarity and maintain ability over the small hit on speed. After screwing around a lot with classes and dicts of dicts , lists etc... This seems good to me.

But if you are inclined to comment. Please give it to me between the eyes. If you think it's crap, please let me know. No sugar coating required. I really want to do it correctly.

# coding: utf-8

from collections import namedtuple
_prec_flds = ['cmd', 'attr', 'query', 
						'call', 'ui_type', 'msg']
						
_prec = namedtuple('_prec', _prec_flds )

# 63 + records in pragma_static_data (sample here)
# i find defining the static data as namedtuples. if i need
# to edit by hand of just review the data, its very clear
# also, if i want to add another field, i have a script i can
# just that will generate the pragma_static_data again
# with the new field.

pragma_static_data = [
	_prec(cmd='application_id', attr=None, 
			query=True, call=True, ui_type='number', msg=None),
			
	_prec(cmd='auto_vacuum', attr=None,
			query=True, call=True, ui_type='number', msg=None),
]

# creates a list of ordered dicts, containing the data
# from pragma_static_data
_params_dict = [ nt._asdict() for nt in pragma_static_data]

# dont want to use the named_tuples in the class
# mainly because of the difficultly updating flds
# in place.
del pragma_static_data

# make sure its working
print _params_dict

ccc

You can write:

_prec_flds = 'cmd attr query call ui_type msg'

namedtuple.__init__() will do a str.split() on the second parameter if it is a str. This helps a lot with readability.

You could write:

pragma_static_data = [_prec(cmd=cmd, attr=None,  query=True, call=True,
    ui_type='number', msg=None) for cmd in ('application_id', 'auto_vacuum')]

_params_dict is a list of dicts, not a dict ;-( Perhaps add an 's' on the end of the variable name to make that clear.

Phuket2

@ccc , I see your point about the creating the namedtuple's the way you did it. Also a nice way. But I just picked the first 2 records that just happen to have the fields other than cmd being the same. There are 63 records and the fields all differ, so that pattern you suggest I can not use in this case. Of course I can use you fields suggestion.

I agree with the naming of params_dict, was a last min tack on. Should be pragma also. Like pragma_dicts or pragma_dict_list

Tizzy

Hi guys I know this is a little bit off topic, but I need some help and at the very least you guys discuss sqlite in this thread. Maybe I'm too shy to start a new thread...

So I'm trying to use sqlite3 with a local database file to store values that have to do with whether it is being run for the first time etc. I want to initialize it with certain values that later, when users run the app will change.

Its my understanding that doing sqlite3.connect('path/to/file/blablabla.db") will create a db where I'd like. How do I get the relative path to the main scripts folder of my app in the Xcode template? I'd like to save the .db file along side my scripts and then access it from other instances of my app, and also be able to peek into it during development. Thank you all. :)

Phuket2

@Tizzy l sorry I can not help. I have never worked with Xcode. So I have no idea were your scripts are being saved. But I am assuming you would be using some relative path from your script director. But I am just guessing. But yes the connect() will make the database
Below is a bit of code I got from stackflow you might find useful later.

def isSQLite3(filename):
	'''
		try and determine if the filename is a valid sqlite3 database. if the filename does not exist, still returns False. 
		
		copied this code from stackflow
	'''

	if not os.path.isfile(filename):
		return False
	if os.path.getsize(filename) < 100: 
		# SQLite database file header is 100 bytes
		return False

	with open(filename, 'rb') as fd:
		header = fd.read(100)
	
	return header[:16] == 'SQLite format 3\x00'

Tizzy

that looks like it could be useful. However I'm having problems creating a db file where I specify the path. here's what I've tried.

import sqlite3
import os

dir = os.path.dirname(__file__)
filename = os.path.join(dir,'/sillllly.db')
conn = sqlite3.connect(filename)

if you don't specify a path the database is created simply by making the call to .connect, but it doesn't seem to with this relative path.

ccc

Do you get an error / exception or does it fail silently.

You might not have write access in an XCode created app. Can you open('junk.txt', 'w') in an XCode app?

A few things to try out:

import os
print('\n'.join(func(__file__) for func in (str, os.path.abspath, os.path.normcase, os.path.realpath)))
print(os.path.relpath(__file__, os.curdir))

Tizzy

it was just saying could not create database...but I understand why now it was a failure of my path string- for some reason filename = os.path.join(dir,'/sillllly.db') was just returning '/sillllly.db.' instead of


/Users/username/Library/Developer/CoreSimulator/Devices/randomalphanumericstrings/data/containers/Data/Application/more-alphan-numeric-mumbo-jumbo/Documents/sillllly.db

...Now I got it to work using

diry = os.path.dirname(__file__)
combinedViaString = diry+"/sillllly.db"

and plugging that into connect()

Now, the above works, and the database says it's been created, and i can even create a table, however I can't see the database file in the main directory where my script is actually located. I guess this "/documents" area is some sort of temporary directory during runtime since that's not the actual directory I see in the Xcode file structure? or is it inaccessible to me?

And that really is my goal - to create a database file which i can see and have full control over in the file structure.

dgelessus

@Tizzy os.path.join treats both arguments as full paths. If the second one has a slash at the beginning, it is considered an absolute path (relative to the root directory /), and the first path is effectively ignored. If you want to join two paths together, the second one must be a relative path. For example os.path.join("/usr/bin", "python") returns "/usr/bin/python", but os.path.join("/usr/bin", "/python") returns "/python".

Tizzy

@dgelessus said:

@Tizzy os.path.join treats both arguments as full paths. If the second one has a slash at the beginning, it is considered an absolute path (relative to the root directory /), and the first path is effectively ignored. If you want to join two paths together, the second one must be a relative path. For example os.path.join("/usr/bin", "python") returns "/usr/bin/python", but os.path.join("/usr/bin", "/python") returns "/python".

thank you I see what happened there then.

Tizzy

@ccc said:

You might not have write access in an XCode created app. Can you open('junk.txt', 'w') in an XCode app?

@ccc that open call did not throw any errors. I don't see any result for anything done, but no errors.

Phuket2

@Tizzy , did look though your dirs, to see if the db was created somewhere? It's really hard to get it to fail

Tizzy

The "temporary" directory during runtime I referred to above doesn't actually appear to be temporary because in subsequent runs, the table i created earlier is said to "already exist." However I can find NO trace of it. searching in xCode (the search tends to be pretty thorough there usually) and in finder, yields NOTHING.

Also worth noting, when run through the simulator all of those relative paths never once surfaced a "scripts" folder in the path, but a "Documents" folder. Scripts is the folder in which my scripts are in. But during runtime it doesn't know scripts exists.

FURTHER: just running the same thing outside of the simulator, the db file IS created and visible as it should be. So to conclude: iOS apps create a different directory structure at runtime, and I can't figure out how to manually access that outside of runtime, but if I pre-create the file will it get moved to the "runtime" "Documents" directory like all the other scripts so I can have a db file initialized before runtime but accessible during runtime using the relative directory stuff?

Does any of that make sense to you guys? I think this might work, initializing it outside of Xcode with the values i need, and then it should (i think) be put into the runtime directory along with the other files. I will report back with further findings.

Phuket2

@Tizzy , again I cannot help. But when searching for the database using the finder did you use wild cards, or use the filters for created today etc, also checking if it's visible. If the table reports it exisits , it must be there somewhere

Tizzy

I did find it at the absolute url specified in userr/Library/......./

But my takeaway is if you want to setup a sqlite database for use by an Xcode app, do so outside of Xcode if you want it to be in your scripts directory permanently.

Phuket2

Ok, I was just trying to test how big a empty database is... Now, I see an issue I was having before. Not sure why. But if you use sqlite3.connect() to create a database file and nothing else, it ends up as zero bytes. A valid sqlite3 db file is suppose to have a valid file header of a 100 bytes.
I did different ways, using with, committing then closing etc. but still made a zero byte file. Maybe this behaviour is expected, but not by me.
So my point being, if you make a db file as a placeholder. Good to check it's a valid sqlite3 db file

Tizzy

@Phuket2 said:

Ok, I was just trying to test how big a empty database is... Now, I see an issue I was having before. Not sure why. But if you use sqlite3.connect() to create a database file and nothing else, it ends up as zero bytes. A valid sqlite3 db file is suppose to have a valid file header of a 100 bytes.
I did different ways, using with, committing then closing etc. but still made a zero byte file. Maybe this behaviour is expected, but not by me.
So my point being, if you make a db file as a placeholder. Good to check it's a valid sqlite3 db file

I can confirm a newly created .db sqlite3 file is 0Kb. adding a table makes it 8Kb.

Phuket2

@Tizzy , but I am glad I tested it. This has being giving me grief. I didn't know why. Now I know why 😭😱